If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
PK - To AutoNumber or Not To AutoNumber - That is the Question! :-
Seriously... if I have, for example, employee numbers in an Employees table,
should I use the employee number (will never change) or should I add an autonumber PK? -- Thanks! Dee |
#2
|
|||
|
|||
PK - To AutoNumber or Not To AutoNumber - That is the Question! :-
Dee:
If your employee numbers are unique, there's no reason to add another autonumer PK. Just make the employee numer your primary key for the table and you'll be fine. "dee" wrote: Seriously... if I have, for example, employee numbers in an Employees table, should I use the employee number (will never change) or should I add an autonumber PK? -- Thanks! Dee |
#3
|
|||
|
|||
PK - To AutoNumber or Not To AutoNumber - That is the Question! :-
The only thing an Access Autonumber is designed to do is provide a unique
record/row identifier. If you already have one, why bother with another?! Regards Jeff Boyce Microsoft Office/Access MVP "dee" wrote in message ... Seriously... if I have, for example, employee numbers in an Employees table, should I use the employee number (will never change) or should I add an autonumber PK? -- Thanks! Dee |
#4
|
|||
|
|||
PK - To AutoNumber or Not To AutoNumber - That is the Question! :-
On Thu, 2 Aug 2007 14:36:01 -0700, dee wrote:
Seriously... if I have, for example, employee numbers in an Employees table, should I use the employee number (will never change) or should I add an autonumber PK? A primary key candidate should meet three criteria: it should be unique within the table; it should (preferably) be stable, not something that will get edited after entry; and (ideally) it should be reasonably small (8 bytes is the size of an Autonumber so that's a benchmark). If your employee number meets those criteria it's a good "natural key" and you should by all means use it. John W. Vinson [MVP] |
#5
|
|||
|
|||
PK - To AutoNumber or Not To AutoNumber - That is the Question! :-
I am in a situation where they changed the format of the EmployeeID number.
Perhaps updating thousands of records in related tables for a hundred employees is simpler than I imagine by using Cascade Update Related Fields, but I am not anxious to attempt the project. I am left either with doing the update (and verifying that it has gone as planned) or using the old number as the PK (incrementing it by 1 for new employee records) and adding another field for the new ID number. "dee" wrote in message ... Seriously... if I have, for example, employee numbers in an Employees table, should I use the employee number (will never change) or should I add an autonumber PK? -- Thanks! Dee |
#6
|
|||
|
|||
PK - To AutoNumber or Not To AutoNumber - That is the Question
I think BruceM has a good point. Too many times I have heard "This will
never change". You can never bank on anything staying the same. I had a similar instance where there was a code convention for a data item that was set up to satisfy or ERP system. One a littler over a year, they changed ERP systems twice. The coding system had to change both times. -- Dave Hargis, Microsoft Access MVP "BruceM" wrote: I am in a situation where they changed the format of the EmployeeID number. Perhaps updating thousands of records in related tables for a hundred employees is simpler than I imagine by using Cascade Update Related Fields, but I am not anxious to attempt the project. I am left either with doing the update (and verifying that it has gone as planned) or using the old number as the PK (incrementing it by 1 for new employee records) and adding another field for the new ID number. "dee" wrote in message ... Seriously... if I have, for example, employee numbers in an Employees table, should I use the employee number (will never change) or should I add an autonumber PK? -- Thanks! Dee |
#7
|
|||
|
|||
PK - To AutoNumber or Not To AutoNumber - That is the Question
I once had the 'pleasure' of trying to fix a problem where a company was
bought by another and, guess what, the employee numbers were all changed! Since they didn't have Referential Integrity enabled (actually couldn't as there were some orphaned records) couldn't use Cascade Update. I'm a firm believer in autonumbers for PKs. If it doesn't have a meaning, you won't be tempted to change it. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Klatuu" wrote: I think BruceM has a good point. Too many times I have heard "This will never change". You can never bank on anything staying the same. I had a similar instance where there was a code convention for a data item that was set up to satisfy or ERP system. One a littler over a year, they changed ERP systems twice. The coding system had to change both times. -- Dave Hargis, Microsoft Access MVP "BruceM" wrote: I am in a situation where they changed the format of the EmployeeID number. Perhaps updating thousands of records in related tables for a hundred employees is simpler than I imagine by using Cascade Update Related Fields, but I am not anxious to attempt the project. I am left either with doing the update (and verifying that it has gone as planned) or using the old number as the PK (incrementing it by 1 for new employee records) and adding another field for the new ID number. "dee" wrote in message ... Seriously... if I have, for example, employee numbers in an Employees table, should I use the employee number (will never change) or should I add an autonumber PK? -- Thanks! Dee |
#8
|
|||
|
|||
PK - To AutoNumber or Not To AutoNumber - That is the Question
I tend to use Autonumber PKs for most of my tables, much to the dismay of
the "natural key" purists (heh, heh, heh!). And to build on your suggestion, Jerry, the OP can simply add another field that is the "CurrentCompanyEmployeeIdentifier", and THAT can get changed for any reason! Regards Jeff Boyce Microsoft Office/Access MVP "Jerry Whittle" wrote in message ... I once had the 'pleasure' of trying to fix a problem where a company was bought by another and, guess what, the employee numbers were all changed! Since they didn't have Referential Integrity enabled (actually couldn't as there were some orphaned records) couldn't use Cascade Update. I'm a firm believer in autonumbers for PKs. If it doesn't have a meaning, you won't be tempted to change it. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Klatuu" wrote: I think BruceM has a good point. Too many times I have heard "This will never change". You can never bank on anything staying the same. I had a similar instance where there was a code convention for a data item that was set up to satisfy or ERP system. One a littler over a year, they changed ERP systems twice. The coding system had to change both times. -- Dave Hargis, Microsoft Access MVP "BruceM" wrote: I am in a situation where they changed the format of the EmployeeID number. Perhaps updating thousands of records in related tables for a hundred employees is simpler than I imagine by using Cascade Update Related Fields, but I am not anxious to attempt the project. I am left either with doing the update (and verifying that it has gone as planned) or using the old number as the PK (incrementing it by 1 for new employee records) and adding another field for the new ID number. "dee" wrote in message ... Seriously... if I have, for example, employee numbers in an Employees table, should I use the employee number (will never change) or should I add an autonumber PK? -- Thanks! Dee |
#9
|
|||
|
|||
PK - To AutoNumber or Not To AutoNumber - That is the Question
On Aug 3, 9:56 pm, Jerry Whittle
wrote: I once had the 'pleasure' of trying to fix a problem where a company was bought by another and, guess what, the employee numbers were all changed! Since they didn't have Referential Integrity enabled (actually couldn't as there were some orphaned records) couldn't use Cascade Update. I'm a firm believer in autonumbers for PKs. If it doesn't have a meaning, you won't be tempted to change it. Now consider the scenario where two companies use auto-increment values for their personnel data then those companies merge... Jamie. -- |
#10
|
|||
|
|||
PK - To AutoNumber or Not To AutoNumber - That is the Question
On Aug 4, 12:12 am, "Jeff Boyce" wrote:
I tend to use Autonumber PKs for most of my tables, much to the dismay of the "natural key" purists (heh, heh, heh!). My view: the primary (no pun) consideration is the logical: put a unique (lowercase) constraint on natural employee number because failure to do so will result in loss of data integrity, as described by Jerry Whittle in this thread. Physical considerations are secondary in my opinion e.g. does my SQL DBMS grant special powers to the PRIMARY KEY (uppercase) and if so what are they? Does my SQL DBMS physically repeat the key values for a foreign key and, if so, can I trust it to cascade the values if they change? Is the performance of my SQL DBMS so poor that I feel I must restrict myself to a single eight byte key column? Will my application be successful, hence will be ported to another platform with different physical considerations? Jamie. -- |
Thread Tools | |
Display Modes | |
|
|