GregDataReno
New member
- Local time
- Tomorrow, 04:19
- Joined
- Jul 4, 2016
- Messages
- 25
mike60 - 40 years of data modeling experience has proven (to me) that autonumbered primary keys are not necessary where an existing natural key will do the job.Hey
Why oh why give such wrong advice.
Every table should have an Autonumber PK
Who told you to avoid using Autonumbers?
In nearly every instance of the excessive use of autonumbering where a natural key already exists and would do the job perfectly well, I see either users who don't understand their business, or developers who've failed to elicit from users their true business case, or both - liberal use of autonumber primary keys is not a substitute for a well-understood business need.
So, why my assertion about natural keys in place of autonumbering primary keys...?
1. Natural keys are self documenting which greatly simplifies the design of the front end and especially the creation of reports.
2. Natural keys are familiar to users and this simplifies communication amongst users and amongst users and developers when exploring the business need.
3. Natural keys remove the need to (always remember to) create a unique index to ensure relational integrity exists in stored data.
Here's my example model to support my argument:
If a particular 'race' is called, for example, "Iditarod Trail Sled Dog Race" then that name will serve perfectly well as the primary key - there is only one such race.
Race: RaceName (PK), <other non-key attributes>
Now we need a table to hold the occasions when this race is run - ie the "Race Event"; this 'event' table inherits the Race primary key to which I'd add the date when the race us run to make a composite primary key - each running of the race is now unique:
RaceEvent: RaceName (PK, FK), DateHeld (PK), <other non-key attributes>
We could then create a table that shows all entrants in an instance of the race:
Competition: RaceName (PK, FK), DateHeld (PK, FK), TeamID (PK, FK), <non key attributes, like PlaceAchieved, TimeStarted, TimeEnded etc.
...no autonumber primary key required yet.
However, there are occasions when autonumber keys are necessary -- when no natural key exists. For instance, consider a database storing riverwater quality -- sites along a transect where samples are taken may not exhibit any special characteristic unique to each site - they're just sites 1, 2, 3 etc. We don't need to know anything specific to each site, but we do want to be able to assign the correct set of results to each site so we don't end up with duplicates, or missing data etc. I'd probably use autonumber primary key for each site in this scenario to ensure referential integrity and functional dependence between all site-related ata. (I note - from experience - that a site's spatial coordinates - latitude/longitude for example - are not suitable as primary keys because they're not reliably discreet numbers and not immutable).
--
I'm very interested in any evidence you have to support your claim that "Every table should have an Autonumber PK".
Cheers
Greg
Last edited: