Results 1 to 2 of 2

Thread: Character or Numeric as PK

  1. #1
    KCneedsHelp Guest

    Character or Numeric as PK

    Hello,
    I am from the school of thought that you should in every case have your primary keys as numeric values only. However, where I currently work there is a project leader who is a recent FoxPro convert (I know, they are tough ones to crack). I made the suggestion recently that the keys in the table should be numeric and with him being the project leader and me just a lowely developer he said get lost. I made the point that later joining your tables together in a PK/FK relationship where the keys where character would be slower then with numeric keys. He didn't listen and now we are approaching production with a database that is really just a bunch of text file. He said that with SQL 7 it doesn't matter if the pk is numeric or character. I disagree. But I need solid documentation to take to him and to the managers to convince them. If anyone out there could advise me on this. And if anyone could give me or tell me where I could find documentation on why even in SQL 7 there is a need to use numeric keys that would be a great help and you would be making one more shop in this world a little bit more technically sound :-) Thank you in advance for your help.

    kc

  2. #2
    Dale Shaw Guest

    Character or Numeric as PK (reply)

    Hi

    I would imagine that there is no performance difference between alpha and numeric PKs OF SIMILAR SIZE. With the hardware we have today (and SQL's super duper query engine) I don't think it matters.

    Why does he want an alpha key? Does they PK value have some external meaning? The standard rules for database design say that each table's PK should have no relationship to any other fields in the table (or the real world).

    I always use numeric PKs and there PKs are almost never exposed to the user. For example, my client may decide that a customer records should look like 'BE867SHAW-S', where each part of the customer record has a meaning to them. In this case I would use this as an 'alternate key' and create a numeric key that was used internally to track all records. They would do lookups based on their 'unique account number' and all joins etc would be done using the smaller, more elegant numeric PK.

    This also has the benefit that the user can 'rename' the customer record at any time and it has no effect on the internal pointers between tables etc.

    So, I prefer numeric PKs ... but ... if the man paying the bills said 'Customer ID is a char(6)!' then I wouldn't fight too hard.


    Dale

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •