| Author |
Topic |
|
MikeB
Constraint Violating Yak Guru
387 Posts |
 Posted - 2004-02-23 : 18:23:35
|
| I have a situation where I want a CompanyListing and a CompanyContact table in my database. Problem is the primary keys. For the contacts, I could use a combination of the company, first, surname of the record as a primary key, or I could use the identity field. The CompanyListing I could use the name or again the identity field.Argument one says "All primary keys should have meaning". And in the case of the identity, contact 195 for company 162 doesn't mean anything. Argument two says "If possible, use integers not strings, for primary keys" to speed up query time etc....So, there is apparently a conflict, any thoughts from the gurus?Mike B |
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2004-02-23 : 19:12:19
|
| Must... resist... rant....Use what your business model dictates..DavidM"SQL-3 is an abomination.." |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-02-24 : 03:40:44
|
| company, first, surnameThis almost certainly would not be a good PK.Companies have people with the same names.People are replaced and you should not update the PK.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2004-02-24 : 07:20:39
|
| I don't have as much experience as these other guys around here but in my years in the business I have never heared that a PK should have a meaning other than for it to be unique. I have always used identity-fields and I can come up with no good reason whatsoever why I shouldn't keep using it (maybe some of the guys here can though). Great when you write queries, great for indexing, always unique to the table... I was seriously annoied when I worked in my old firm, they always had combined PKs sometimes up to 5 fields and I was going mad writing the joins. My choice would definetly be identity...--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-02-24 : 07:28:50
|
| I would say unique and non-updatableThe terms are natural and artificial key.Do a search.Waits for the Celko tirade.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
MikeB
Constraint Violating Yak Guru
387 Posts |
Posted - 2004-02-24 : 08:13:40
|
quote:
Originally posted by byrmol
Must... resist... rant....Use what your business model dictates..DavidM"SQL-3 is an abomination.."
Actually, your rant would have been appreciated. Sometimes a rant can be alot like constructive critisism. Your comment "Use what your business model dictates" unfortunately doesn't tell a newbie like myself very much because I could logically use either and both would solve my problem. My question is, what is the safest approach, the most efficient approach, and what would others do?Thanks for your reponse.Mike B |
|
|
|
MikeB
Constraint Violating Yak Guru
387 Posts |
Posted - 2004-02-24 : 08:16:03
|
quote:
Originally posted by nr
company, first, surnameThis almost certainly would not be a good PK.Companies have people with the same names.People are replaced and you should not update the PK.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy.
Point well taken and understood.Thanks |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2004-02-24 : 08:29:56
|
| >> Use what your business model dictatesThis actually means work from your past experiences.I have a suspicion that a lot of people who are adamant one way or the other have design poor systems in the past - nothing to do with the theory just made a mistake and don'r realise it was their problem.>> doesn't tell a newbie like myself very muchThe point is that you have to look at the system and make decisions. If you haven't done it before then you will be trusting to luck and just hope that the design isn't too bad to run the system. No one is going to give you guidelines that you can follow to build any system (and you should be suspicious of anyone that tries) - that's why system design isn't simple - otherwise it would be left to trainees.The best you will get is "This could be a mistake in these circumstances".==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2004-02-24 : 08:41:34
|
| if your business model dictates that it would be advantagous to assign a unique "ID" number to each contact, and to always reference that contact by that ID, then by all means use an identity and feel no shame about it. However -- to be honest, i think a lot of times a better way to store this type of info (contact info) is:Company, ContactTypeas your primary key. where contact type is "Billing", or "Primary", or whatever -- you would have a distinct list of contact types in a table. This keeps your data organized as well, and when contacts change, you do not need to change your PK columns, just the data. But your business model may not make sense with this approach.- Jeff |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-02-24 : 09:21:40
|
quote:
Originally posted by MikeB
Argument one says "All primary keys should have meaning". And in the case of the identity, contact 195 for company 162 doesn't mean anything. Argument two says "If possible, use integers not strings, for primary keys" to speed up query time etc....
Just curious...where did yo get thos arguments?Also, in doing a logical data model, I don't belive the concept of identity exists...You would be best served by collecting all of the data elements. Separatley at first. Make sure to include things that are already recorded elctronically, and those that are not.Capture the data about the data as well (Metadata). Things like the type of data, size, frequency of change, additions, ect.Anything that floats the business.Then take them and organize them in to like groupings (Entities)Then establish relationships between these entitiesFor example, you're worrying about contacts for a company...First I'd worry about the people...how do you identify them today?Then I'd figure out how I'd identify a company...Then you have a business need that establishes a relationship between a company and a person...that relationship is the fact that you need a contact for that company...the business need establishes the fact that you need a relational entity to store that fact...It's not natural data in a sense...it's more a business rule...So...take a step back...collect all of the elements and rules...and build a logical model..Physical implementation is another story...oh, and MOO..but it works for me.Brett8-) |
|
|
|
MikeB
Constraint Violating Yak Guru
387 Posts |
Posted - 2004-02-24 : 09:56:56
|
quote:
Just curious...where did yo get thos arguments?
I didn't save the http address to the posts sorry, but I have seem them repeated several times in the Natural vs Surrogate key debates.quote:
However -- to be honest, i think a lot of times a better way to store this type of info (contact info) is:Company, ContactType
Well the type of contact is a great idea except that the contacts are site supervisors for general contractors. The contact type would not work here because on company has many site supers.quote:
First I'd worry about the people...how do you identify them today?
By name, but this would be a mistake for a primary key.quote:
Then I'd figure out how I'd identify a company...
Again by name, except what if in the case of where I work. I have on office located in one place, and an office/plant located in another. They are both the same company.quote:
Then you have a business need that establishes a relationship between a company and a person...that relationship is the fact that you need a contact for that company...the business need establishes the fact that you need a relational entity to store that fact...It's not natural data in a sense...it's more a business rule...So...take a step back...collect all of the elements and rules...and build a logical model..
Did that, but now being a newbie, I read articles that make me second guess my decisions. After all, I want a quality product.I decided to use surogate keys because I really don't know anything about the people / companies that will populate these tables other then name, address, phone, fax, email, first, last, extension, etc...Then I read articles that contain quotes such as:quote:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=24776By Page47"Plus, I'll never believe you can't find a natural key. If two rows are truely identical, then either you have poorly designed your database or you don't have two things to store ... you only have one."
Anyway, I find the forums give me alot of insight, so please, don't get annoyed with me and stop posting your thoughts, rants, etc...Mike B |
|
|
|
MuadDBA
628 Posts |
Posted - 2004-02-24 : 11:11:46
|
| First, you're right....your primary key should be meaningful where possible, and it should uniquely identify your data.Using an identity column as your primary key doesn't prevent you from inserting dulpicate data, it just gives you a unique identifier for each row. I could insert John Smith from Boeing 25,000 times without having a primary key violation if my primary key is simply an identity column.Also correct is the fact that your key fields should not be ones which get updated often....so using names is typically out, because contact names tend to change frequently enough to present a problem.Do the contacts have an employee id that could help you identify them uniquely by employer? Then you could use the employee id and company name as the primary key...If not, sometimes it comes down to the fact that you have to do what you have to do in order to get the job done with what you have. |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-02-24 : 11:19:47
|
quote:
Originally posted by MikeB
[quote]Then I read articles that contain quotes such as:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=24776By Page47"Plus, I'll never believe you can't find a natural key. If two rows are truely identical, then either you have poorly designed your database or you don't have two things to store ... you only have one."Anyway, I find the forums give me alot of insight, so please, don't get annoyed with me and stop posting your thoughts, rants, etc...Mike B
That's a GREAT Thread...see what I miss for not logging on on a saturday...probably skiingAnd if you read the article (and felt the friction) I don't think you EVER have to worry about the people here stopping their posts...As for the annoyed part...well you can read....just make sure to laugh it off...cause..IT JUST DOESN'T MATTER"Even if God himself pointed his finger down at sqlteamIt just doesn't matterEven if every man, woman and child, held hands and prayed for usIt just doesn't matter..." ** Meatballs referenceMOOOh, and go google some data modeling references...http://www.infogoal.com/dmc/dmcdmd.htmAnd remeber Logical then physical...do You have a data modeling tool like ERWin?You ucan use Visio 2000 PRO alsoBrett8-) |
|
|
|
MikeB
Constraint Violating Yak Guru
387 Posts |
Posted - 2004-02-24 : 11:56:18
|
quote:
MOO
What does this mean....or "does it matter" ? :)Mike B |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-02-24 : 12:18:08
|
| My Own OpinionMOOBrett8-) |
|
|
|
|
Primary Keys?