| Author |
Topic |
|
bjornh
Yak Posting Veteran
87 Posts |
 Posted - 2003-10-18 : 05:07:08
|
I've read allmost all the articles on sqlteam about chosing the right PK. but still don't have a good anwser if to chose a nummeric ID column, or a column that is allready in the tabel, so i'll give a concrete small example, and then you can tell me what to chose  table:customersname, address, department, etc.table: DepartmentsName, address so this is the situation, but is it better to add an id column to departments, and change department @ customers in departmentID? or....thanks |
|
|
byrmol
Shed Building SQL Farmer
1591 Posts |
Posted - 2003-10-18 : 06:36:27
|
| It is very hard to answer without all the rules associated with each entity.But a good starting point is to answer 2 questions.. 1) What is the simpliest combination of columns that provide identity ( uniqueness)?2) Will the value of the PK change and at what frequency.DavidM"SQL-3 is an abomination.." |
 |
|
|
bjornh
Yak Posting Veteran
87 Posts |
Posted - 2003-10-18 : 07:13:37
|
Mmm yes these do help some thanks, you helped me creating a beter db  |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-10-18 : 13:40:14
|
| Do department names change?If they do are they considered new departments?How are departments added?If your department names can change and you still want to keep the references then you need an artificial key.If you adda department via manual entry and want to allow for mistakes corrected later then again you need an artificial key.For this sort of thing I would add an ID for flexibility.==========================================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. |
|
|
|
bjornh
Yak Posting Veteran
87 Posts |
Posted - 2003-10-19 : 15:41:02
|
| myeah, i had the same thought, so i can send change dep. where id=4 and not where id='previous dep. name' because if someone else changes it just before you, problems start... thx |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-10-19 : 15:52:16
|
| If you use the name as the PK then you can't change it at all. You would have to add a new departmentthen change the links to all the records that use it throughout the system.With a number the department name becomes a description for presentation which can be changed at will.==========================================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. |
|
|
|
bjornh
Yak Posting Veteran
87 Posts |
Posted - 2003-10-19 : 15:59:35
|
| you can set the relation to cascate update or something? (have never used it, but i saw this option and it made me thinking, so that's why i asked it initialy) |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-10-19 : 16:06:58
|
| Yep - but a PK should be row defining so not updateable.It's a matter of viewpoint but I would consider any system that allows a PK to be updated as incorrect.Think about what it will do to triggers which need to match records in the table and the inserted and deleted tables.==========================================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. |
|
|
|
bjornh
Yak Posting Veteran
87 Posts |
Posted - 2003-10-19 : 17:01:20
|
| my thought exactly, well, allmost |
|
|
|
|
A concrete anwser about PK column