Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
heze
Posting Yak Master
192 Posts |
 Posted - 2008-05-23 : 11:01:43
|
| Hi,I am designing a database for containing the info for the employees in the institution. My dilemma:-Should I use an sql autonumber primary key?or-should I merge lastName+FirstName+middleName into one field and use this string as the primary key?thank you |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-05-23 : 11:10:34
|
Autonumber Primary Key, also known as IDENTITY, is your choice.Else you may difficulties distinguishing between "Anne Cho" and "Ann Echo".
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
heze
Posting Yak Master
192 Posts |
Posted - 2008-05-23 : 11:27:49
|
| Thanks Peso, I have a question then,In this page (www.sqlteam.com) you have user name as either a primary key or a unique index because we can not go back and change it, and it is more descriptive than a number. In the case of "Anne Cho" and "Ann Echo" it would be inprobable that they both have the same middle name, and if they had, sorry to question you but this is one of the reasons for which I have not being able to decide, a natural key is allways more descriptive and makes your queries easier if you have multiple tables with primary key-foreign key relationships, for example, if I want to find Ann Cho in a related table I would just go to the related table (with foreign key) and typeselect * from relatedTable where mergedNamePrimaryKeyAsForeignKey like '%cho%'whereas if I go for the autonumber I would have to create an inner query adding another step, I would first have to go to the master table (with primary key) queryselect * from mastertableContainingPrimaryKey where mergedNamePrimaryKey like '%cho%'to get the surrogate keyand then :select * from relatedTable where surrogateKeyAsForeignKeylike '%cho%'what is your opinion of this?thank you |
|
|
|
heze
Posting Yak Master
192 Posts |
Posted - 2008-05-23 : 11:30:24
|
| sorry the last query looks like this:select * from relatedTable where surrogateKeyAsForeignKey =(select surrogateKeyAsPrimaryKey from mastertableContainingPrimaryKey where mergedNamePrimaryKey like '%cho%') |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-05-23 : 13:29:42
|
| There is no guarantee that you will not have two or more employees with exactly the same name. Having something that is unique most of the time is not good enough.Another reason that names are a bad choice for a primary key is that they change when people get married, divorced, etc.Another reason it is a bad choice for a primary key is that you would be wasting a lot of space in related tables with a foreign key relationship. Not to mention being forced to update all related tables when the name changes.CODO ERGO SUM |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-05-23 : 13:38:04
|
quote:
Originally posted by Michael Valentine JonesHaving something that is unique most of the time is not good enough.
Its the 99/99 rule. You spend 99% of your time maintaining systems that are accurate 99% of the time...e4 d5 xd5 Nf6 |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2008-05-23 : 13:49:37
|
| Most companies have some unique employee id. There is no good reason to use that as a primary key, but it should probably be a unique constraint. I wouldn’t use that as a primary key because there is no guarantee that the business will not decide to change it.Don’t make the mistake of using Social Security number as a primary key. If they decide to open an office in another country, employees will not have them.A surrogate integer identity primary key is probably the best choice.CODO ERGO SUM |
|
|
|
|
|
|
|
|
Last Name-First Name Primary Key?