Well, there's a whole school of thought that says you should do just that. However, I feel that having a surrogate key and a unique index has some significant advantages over natural keys.
1. First of all, a surrogate (autonumber) field will never need to change. Any real-world key has the possibility of needing to change, which then requires a mechanism to update them. The Cascade Updates is supposed to handle this, but I've had cases where I needed to change the actual data type of the primary key. That's a real mess.
2. If you have a multi-field natural key and you make that the primary key, then ALL of the fields in the key have to be added to any related table. This means in addition to a multi-field primary key in one table, you may have many multi-field foreign keys.
3. Lastly, most natural keys are text data (even if they're numbers). Text fields are more "costly" to link data. Joining tables on a single numeric field is much faster, so an autonumber field is much more efficient.
So the thought with surrogates is that you're splitting the functions of the primary key in two. One field for joining tables, and another (or multiple fields) for maintaining real-world uniqueness.
4 comments:
Opulently I assent to but I contemplate the collection should prepare more info then it has.
Nice dispatch and this mail helped me alot in my college assignement. Thank you on your information.
"Then I create a Unique Index on the Natural Key"
Why do we make an autonumber ID and then do this too?
Why not just make the natural key into the primary key?
Your articles are the best for learning database design by the way... =) Thanks for the resource
Well, there's a whole school of thought that says you should do just that. However, I feel that having a surrogate key and a unique index has some significant advantages over natural keys.
1. First of all, a surrogate (autonumber) field will never need to change. Any real-world key has the possibility of needing to change, which then requires a mechanism to update them. The Cascade Updates is supposed to handle this, but I've had cases where I needed to change the actual data type of the primary key. That's a real mess.
2. If you have a multi-field natural key and you make that the primary key, then ALL of the fields in the key have to be added to any related table. This means in addition to a multi-field primary key in one table, you may have many multi-field foreign keys.
3. Lastly, most natural keys are text data (even if they're numbers). Text fields are more "costly" to link data. Joining tables on a single numeric field is much faster, so an autonumber field is much more efficient.
So the thought with surrogates is that you're splitting the functions of the primary key in two. One field for joining tables, and another (or multiple fields) for maintaining real-world uniqueness.
Post a Comment