Mike Smart
Registered User.
- Local time
- Yesterday, 18:07
- Joined
- Feb 14, 2007
- Messages
- 53
Greetings
We've been discussing this subject in the VBA coding forum and I've moved it here as it seems more appropriate. It was sparked by the publication of a set of design standards for Access applications on my web site www.learnaccessvba.com in which I advocate meaningless primary keys.
This subject has been discussed in the past but generally from a computer-science perspective rather than a real-world business needs perspective. There's no simple Right/Wrong answer to it but a set of advantages and disadvantages to each approach. The discussions so far have yielded the following specific advantages and disadvantages and the purpose of this thread is to discover more. Here's the presently identified pros and cons of each approach:
Arguments for Meaningless keys
========================
1/ A primary key should never change and a key that contains meaningful data will always be subject to potential change. While most RDBMS support cascading updates a major change in the primary key can result in the need for extensive system re-design.
2/ A primary key may never be Null so it will be impossible to add a record if the field chosen for the primary key is not known at the time of data entry. In this respect it is less flexible than an alternate key (uniquely constrained index).
3/ Because the primary key is meaningless it is possible to choose a data type for it that will optimise indexed lookups (eg the Autonumber data type in a Jet database).
4/ Business needs may dictate that meaninful data that was unique when the system was designed must change its status to non-unique at a later date. This can result in the need for extensive system re-design if meaningful keys have been implemented.
Arguments for Meaningful keys
=======================
1/ Reporting is easier as a join is avoided when the only information needed from another table is the value of the foreign key.
2/ The table will be more compact because it will avoid the need for a meaningless primary key field and its index.
3/ It isn't possible to create a composite alternate key (only a composite primary key). For this reason the task of maintaining a unique constraint composed of two attributes becomes more difficult and involves the maintanance of three indexes rather than one.
I would like carefully considered and logical argument for any new advantages/disadvantages. The advantages/disadvantages should be within the scope of real-world business applications.
I really appreciate any feedback provided and the findings will be written into the next edition of the quality standard which will be published on my web site.
Thanks in advance!
We've been discussing this subject in the VBA coding forum and I've moved it here as it seems more appropriate. It was sparked by the publication of a set of design standards for Access applications on my web site www.learnaccessvba.com in which I advocate meaningless primary keys.
This subject has been discussed in the past but generally from a computer-science perspective rather than a real-world business needs perspective. There's no simple Right/Wrong answer to it but a set of advantages and disadvantages to each approach. The discussions so far have yielded the following specific advantages and disadvantages and the purpose of this thread is to discover more. Here's the presently identified pros and cons of each approach:
Arguments for Meaningless keys
========================
1/ A primary key should never change and a key that contains meaningful data will always be subject to potential change. While most RDBMS support cascading updates a major change in the primary key can result in the need for extensive system re-design.
2/ A primary key may never be Null so it will be impossible to add a record if the field chosen for the primary key is not known at the time of data entry. In this respect it is less flexible than an alternate key (uniquely constrained index).
3/ Because the primary key is meaningless it is possible to choose a data type for it that will optimise indexed lookups (eg the Autonumber data type in a Jet database).
4/ Business needs may dictate that meaninful data that was unique when the system was designed must change its status to non-unique at a later date. This can result in the need for extensive system re-design if meaningful keys have been implemented.
Arguments for Meaningful keys
=======================
1/ Reporting is easier as a join is avoided when the only information needed from another table is the value of the foreign key.
2/ The table will be more compact because it will avoid the need for a meaningless primary key field and its index.
3/ It isn't possible to create a composite alternate key (only a composite primary key). For this reason the task of maintaining a unique constraint composed of two attributes becomes more difficult and involves the maintanance of three indexes rather than one.
I would like carefully considered and logical argument for any new advantages/disadvantages. The advantages/disadvantages should be within the scope of real-world business applications.
I really appreciate any feedback provided and the findings will be written into the next edition of the quality standard which will be published on my web site.
Thanks in advance!
Last edited: