I would like to get opinions on table design itself. Wether its best to use a unique id generated by the user ( ie. username serialnumber) etc.. or to use a system generated number? I am currious as to the pros and cons of both systems. Also the benefit of keeping an ID as far as space and speed. Instead of using the text fields listed below. If anyone has any input or has run into this argument before I would appreciate your opinions. As always thanks so much.
Unique ID
- Thread starter FloBob
- Start date
- Local time
- Today, 00:41
- Joined
- Feb 28, 2001
- Messages
- 30,566
Generally you want to use automatic IDs (such as Autoincrement fields) for unique record IDs. But there are always exceptions.
For example, if your record ID is supposed to coincide with some other data from an external source, you might not want to use the automatic ID. You would want something that relates to that other data.
The rule of thumb that I uses is,
1. Does the manually entered ID make sense in that context and
2. Does it take up significantly more room than an autonumber would use?
If the answers to the two questions are NO and YES, respectively, use an autonumber. If the answers are YES and NO, respectively, use your manually entered key. If you get YES and YES or NO and NO, it's your call.
For example, if your record ID is supposed to coincide with some other data from an external source, you might not want to use the automatic ID. You would want something that relates to that other data.
The rule of thumb that I uses is,
1. Does the manually entered ID make sense in that context and
2. Does it take up significantly more room than an autonumber would use?
If the answers to the two questions are NO and YES, respectively, use an autonumber. If the answers are YES and NO, respectively, use your manually entered key. If you get YES and YES or NO and NO, it's your call.
Do you have any documentation at all? I'm not questioning what your saying but I was curious if I could get some black and white on what to use. Also I am after a general concensus on how people view this perticular issue. Anyone Else? Thanks again.
First, let me say that from first hand (and in a certain number of cases painful) experience, I came to limit to very exceptional cases the use of built and/or 'meaningful' primary keys (as serial numbers, etc) in my DBs. Should I have a natural and useful 'meaningful' candidate for the 'primary key' status, I would rather make it a unique index and have a surrogate autonumber PK in my table.
The worst (but not only) complications I came in when doing otherwise in the first DBs I designed were related to: management of duplicated built/'menaningful' PKs in multi-users environment (requires extra work but not the worst still), and conflict tracking and resolution in replica sets with built PK (I had to surrender to that one).
They are a number of other reasons however, that range from performance considerations to questionning the actual usefulness of built or 'meaningful' PKs (especially when considering the supplementary workload involved in their generation and management) and reliability over autonumbers.
Following are a few references discussing that topic that may help to make yourself an idea about the pros and cons:
The fundamentals (to my opinion)
Intelligent Versus Surrogate Keys
Why use synthetic keys?
(in the later article, note that the discussion about reverse indexes is not really relevant when using autonumbers since in these cases Access would deal with concurrent PK generation problems. However it has to do with the possible use...fulness of random autonumbers like with replication sets)
Here is an assortment of articles in case you would be interested in getting into further considerations (more extensive use of surrogate keys, insight into datawarehouse DBs designing, extended referential integrity etc.)
The case for the surrogate key
Surrogate keys - 1
Surrogate keys - 2
Cross and self referencing relationships
And for the sake of the debate
(and further immersion into RDB theory)
Why primary keys are superfluous
Hope you'll enjoy the reading
The worst (but not only) complications I came in when doing otherwise in the first DBs I designed were related to: management of duplicated built/'menaningful' PKs in multi-users environment (requires extra work but not the worst still), and conflict tracking and resolution in replica sets with built PK (I had to surrender to that one).
They are a number of other reasons however, that range from performance considerations to questionning the actual usefulness of built or 'meaningful' PKs (especially when considering the supplementary workload involved in their generation and management) and reliability over autonumbers.
Following are a few references discussing that topic that may help to make yourself an idea about the pros and cons:
The fundamentals (to my opinion)
Intelligent Versus Surrogate Keys
Why use synthetic keys?
(in the later article, note that the discussion about reverse indexes is not really relevant when using autonumbers since in these cases Access would deal with concurrent PK generation problems. However it has to do with the possible use...fulness of random autonumbers like with replication sets)
Here is an assortment of articles in case you would be interested in getting into further considerations (more extensive use of surrogate keys, insight into datawarehouse DBs designing, extended referential integrity etc.)
The case for the surrogate key
Surrogate keys - 1
Surrogate keys - 2
Cross and self referencing relationships
And for the sake of the debate
(and further immersion into RDB theory)Why primary keys are superfluous
Hope you'll enjoy the reading
Last edited:
You still are welcome to contribute, shep
Currency , checks and credit cards accepted
More seriously I believe it would be nice if there were a thread that could be reffered to when expanations are requested on this common and fundamental topic.
So other (in kind) contributions are most welcome.
Thank you so much for that information. One more question, Where dealing with sql server does the playing field or the rules change as you switch platforms. I understand most of this is on Relational theory regardless of platform but is there a preferred method used on SQL? Thanks so much for this well thought out and thurough response.
R
Rich
Guest
Your cheque's in the post Alex, made it out in Ffrancs, hope that's ok
Cosmos75
Registered User.
- Local time
- Today, 00:41
- Joined
- Apr 22, 2002
- Messages
- 1,280
Redundant?
This may be a bit redundant, but this link may be helpful.
http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=30370
This may be a bit redundant, but this link may be helpful.
http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=30370
My personal opinion has little weight in this discussion since I am by no mean a serious/regular/experienced DB designer.
But I would defer to Pat's judgment at anytime and I believe the above thread is quite an interesting reference for this dicussion since Pat's position seems (as far as I understand) different from what is reflected in most of the articles I pointed to!
Pat, it would be nice if you could expand a little on this difference of views from your experience.
Maybe also an interesting topic for your book ? (well, you probably thought about it yet..)
But I would defer to Pat's judgment at anytime and I believe the above thread is quite an interesting reference for this dicussion since Pat's position seems (as far as I understand) different from what is reflected in most of the articles I pointed to!
Pat, it would be nice if you could expand a little on this difference of views from your experience.
Maybe also an interesting topic for your book ? (well, you probably thought about it yet..)
Last edited:
Thanks Pat. Cristal-clear and cogent as usually.
I only realize now that the articles I pointed to do recommend such extensive and probably exagerrated use of surrogate keys. I have so far only used them in top-level tables and have used multiple-fileds PKs in child tables without even wondering about that. Seems so natural...
I only realize now that the articles I pointed to do recommend such extensive and probably exagerrated use of surrogate keys. I have so far only used them in top-level tables and have used multiple-fileds PKs in child tables without even wondering about that. Seems so natural...
Similar threads
