Why is it that I find examples illustrate the first field in a table to be a unique number; an id?
What's the benefit of having a id like this and when are the times you shouldn't or don't really need to have it?
first field always a unique number.
Moderator: General Moderators
Re: first field always a unique number.
Only reason is to be unique identifier for all records. No more no less... e.g You could take social security number as ids...but then will be interesting for non-USA citizen.JellyFish wrote:Why is it that I find examples illustrate the first field in a table to be a unique number; an id?
What's the benefit of having a id like this and when are the times you shouldn't or don't really need to have it?
A couple of comments:
Relational database engines are all based on relational database theory that is based on mathematical set theory and was developed and published by E. F. Codd and Chris Date in the 1960's. That theory is like algebra, each theorem is based on underlying theorems. One of the most basic theorems is that every row in a relation (table) must be uniquely identified. You may ask, won't it work if I don't make sure that every row is unique? And the answer is: sometimes. In other words, the best practice is to always insure that every row will be unique, then you'll never get bit on the a** after you've put in a lot of work on a database that some day fails.
Social Security numbers are notoriously bad for use as unique identifiers, for several reasons:
1. Not everybody has one (immigrants, non-U.S. residents, babies).
2. Some people have more than one (not supposed to, but it is actually pretty common).
3. They are not unique (again, supposed to be, but mistakes have been made several times in history and there are still some duplicate SSN's out there).
[Afterthought: There is no reason that it must be the first column of a table, it's just that most experienced database designers know they are going to include a unique ID, so they put it in first so it's obvious.]
Relational database engines are all based on relational database theory that is based on mathematical set theory and was developed and published by E. F. Codd and Chris Date in the 1960's. That theory is like algebra, each theorem is based on underlying theorems. One of the most basic theorems is that every row in a relation (table) must be uniquely identified. You may ask, won't it work if I don't make sure that every row is unique? And the answer is: sometimes. In other words, the best practice is to always insure that every row will be unique, then you'll never get bit on the a** after you've put in a lot of work on a database that some day fails.
Social Security numbers are notoriously bad for use as unique identifiers, for several reasons:
1. Not everybody has one (immigrants, non-U.S. residents, babies).
2. Some people have more than one (not supposed to, but it is actually pretty common).
3. They are not unique (again, supposed to be, but mistakes have been made several times in history and there are still some duplicate SSN's out there).
[Afterthought: There is no reason that it must be the first column of a table, it's just that most experienced database designers know they are going to include a unique ID, so they put it in first so it's obvious.]
Last edited by califdon on Wed Dec 26, 2007 7:15 pm, edited 1 time in total.
- Kieran Huggins
- DevNet Master
- Posts: 3635
- Joined: Wed Dec 06, 2006 4:14 pm
- Location: Toronto, Canada
- Contact: