| Author |
Topic |
|
X002548
Not Just a Number
15586 Posts |
 Posted - 2010-10-07 : 15:48:39
|
| It's time again in this corner of corp america to shuffle the deck...which I am all for....Transitioning a database to another project team...tyhe manager goes..."my dba mentioned to me that every table, and most tables, don't start with an identity column.That is standard practice in the industry. We might have to change your data model."More power to you....Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxWant to help yourself?http://msdn.microsoft.com/en-us/library/ms130214.aspx |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-10-07 : 16:12:20
|
Ask him/her/it if this is standard industry practice:CREATE TABLE US_States(StateID int NOT NULL IDENTITY(1,1),State char(2) NOT NULL,StateName varchar(30) NOT NULL); Seriously, I'd love to hear the justification for that one. Don't put primary keys or unique constraints on it, let them ponder that one. |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-10-07 : 16:15:46
|
| Sometimes, timing is a wonderful thing:http://twitter.com/PaulRandal/status/26681257419 |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2010-10-07 : 16:17:09
|
| Here's an example of the system I am referring to:CREATE TABLE Vehicle (VehicleId int) --not an identity column and not a sequencer, is the PKForeign keyed everywhere in the schema with on cascade updates and update triggers for where cascade updates weren't used or couldn't be used. Plus lots of indexes reference it. Database size is about 100GB.The app allows customers to change the VehicleId via an update! Imagine doing that with the above information. Crap! My recommendation: 1. don't allow updates to pk data OR 2. use an identity column and have VehicleId be metadata, reference the identity in the child tables.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2010-10-07 : 16:20:12
|
I'm a fan of Surrogate Keys myself, but to say that the data model must be changed because it is supposedly an industry standard is sophomoric.quote:
Originally posted by robvolk
Ask him/her/it if this is standard industry practice:CREATE TABLE US_States(StateID int NOT NULL IDENTITY(1,1),State char(2) NOT NULL,StateName varchar(30) NOT NULL); Seriously, I'd love to hear the justification for that one. Don't put primary keys or unique constraints on it, let them ponder that one.
Actually, that is EXACTLY how I would create that table, though I'd also add unique constraints to State and StateName. I've learned that consistency has value in and of itself, so every table gets a surrogate key.________________________________________________If it is not practically useful, then it is practically useless.________________________________________________ |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2010-10-07 : 16:23:58
|
| I always use identity columns as the primary key for the simple reason that I have never had reason to regret that, but have regretted the use of natural keys on many occasions. I also make the natural keys unique constraints of course.CODO ERGO SUM |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-10-07 : 16:27:13
|
quote:
Actually, that is EXACTLY how I would create that table
OK, please answer the following:1. What purpose does StateID perform?2. How will you join this to other tables? What foreign key would you define, if any? |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2010-10-07 : 16:33:00
|
quote:
Originally posted by robvolk
Ask him/her/it if this is standard industry practice:CREATE TABLE US_States(StateID int NOT NULL IDENTITY(1,1),State char(2) NOT NULL,StateName varchar(30) NOT NULL); Seriously, I'd love to hear the justification for that one. Don't put primary keys or unique constraints on it, let them ponder that one.
It may seem trivial and why not use the state code as a primary key, but there is nothing that says the state code is set in stone for all time.Canada changed a number of the province codes a few years ago, so it does happen.With a surrogate key design, update one row in a single table. With a natural key design, change it every place it occurs.That table should have StateID as the PK and unique constraints on the other two columns.CODO ERGO SUM |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2010-10-07 : 16:38:45
|
quote:
Originally posted by robvolk
Ask him/her/it if this is standard industry practice:CREATE TABLE US_States(StateID int NOT NULL IDENTITY(1,1),State char(2) NOT NULL,StateName varchar(30) NOT NULL); Seriously, I'd love to hear the justification for that one. Don't put primary keys or unique constraints on it, let them ponder that one.
I use that one all the timeBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxWant to help yourself?http://msdn.microsoft.com/en-us/library/ms130214.aspx |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2010-10-07 : 16:41:02
|
quote:
Originally posted by Michael Valentine Jones
I always use identity columns as the primary key for the simple reason that I have never had reason to regret that, but have regretted the use of natural keys on many occasions. I also make the natural keys unique constraints of course.CODO ERGO SUM
Another on of my pointsAnd tyo Tara...Yes...I never update a key...because it's a different thing and I would want to keep historyIf you want to make a tie between "old" and "new" then you can create a xref tableBrett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxWant to help yourself?http://msdn.microsoft.com/en-us/library/ms130214.aspx |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2010-10-07 : 16:56:29
|
quote:
Originally posted by X002548
quote:
Originally posted by Michael Valentine JonesCanada changed a number of the province codes a few years ago
No, they established a new thing. The old thing did not go away, it just ceased to exist at a point in time.Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxWant to help yourself?http://msdn.microsoft.com/en-us/library/ms130214.aspx
This is one of the codes that changed.http://www.statoids.com/uca.html"The name of Newfoundland changed to "Newfoundland and Labrador" ("Terre-Neuve-et-Labrador" in French) on 2001-12-06. This is reflected in Change Notice 8 to FIPS PUB 10-4, dated 2002-06-28, and in ISO 3166-2 Newsletter I-2 (2002-05-21). Subsequently the province's postal designator was changed from NF to NL."Quebec changed from PQ to QC about 25 years ago.CODO ERGO SUM |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-10-08 : 05:11:21
|
quote:
Originally posted by X002548
"most tables don't start with an identity column. That is standard practice in the industry.
Shit! We have Create Date as the first column and THEN Identity.I have moved all the Identity columns to be the first column in the table, but now Users are complaining that they liked the Create Date as the first column of their reports. We are going to have to change all our SELECT * queries .... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-10-08 : 05:13:53
|
| I have a number of tables where we used Natural Key being certain that it couldn't change.We have several where that was a bad move (and I'm seriously thinking of taking the hit and changing them, and all their FK sibblings, to Identity.And we have others where the Natural key is supposedly fine, has/will never change, but the users mis-type the natural key and then want to change it when the realise (by which time there are loads of child records and we have no process built [and thus fully QA'd] that allows for an update of that PK).Bummer.ID on mundane tables, like State, is starting to look more attractive even to a die-hard-natural-key'er like me |
|
|
|
Transact Charlie
Master Smack Fu Yak Hacker
3451 Posts |
Posted - 2010-10-08 : 05:18:42
|
I'm not sure if you are making a joke or not........quote:
We are going to have to change all our SELECT * queries ....
If you aren't. Well I hoped you learned something......  No excuse for SELECT *Unless you were joking, in which case: good point, well made.....Charlie===============================================================Msg 3903, Level 16, State 1, Line 1736The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2010-10-08 : 06:20:33
|
| I would always use a surrogate key for the arguments already stated.The bonus of using this approach is if you then need to build cubes off the data, makes life much easier to already have surrogate keys rather than relying on natural key which are likely to have inconsistencies (it happens too often). |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-10-08 : 07:13:35
|
| I'll take "good point, well made" thanks. We don't use "SELECT *" - no star developers here! |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2010-10-08 : 09:30:06
|
quote:
Originally posted by Kristen
quote:
Originally posted by X002548
"most tables don't start with an identity column. That is standard practice in the industry.
Shit! We have Create Date as the first column and THEN Identity.I have moved all the Identity columns to be the first column in the table, but now Users are complaining that they liked the Create Date as the first column of their reports. We are going to have to change all our SELECT * queries ....
Nobody worries about hotspots anymore?Brett8-)Hint: Want your questions answered fast? Follow the direction in this linkhttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxWant to help yourself?http://msdn.microsoft.com/en-us/library/ms130214.aspx |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-10-08 : 12:54:56
|
quote:
Originally posted by X002548
quote:
Originally posted by Kristen...but the users mis-type the natural key
Sounds like a simple sproc to me...or a trigger..I would still not delete them
Not sure its simple, is it?Change main record's PK from XXX to YYY requires:First insert YYY record - work around any other columns in the record that have UNIQUE constraints.Then change all Child Records to have YYY instead of XXXThen DELETE the original XXX record (and re-instate any Unique Columns in the YYY record)For us (you too no doubt) there are loads of child records on some tables. There might also be some usage that is not covered by foreign keys (we have some tables that have a generic "Foreign Key Column" plus a "Which table" column [which is no doubt a bad thing, but its convenient for us] so opportunity for us to screw up because FKeys don't catch it).Opportunity for everyone to screw up if someone forgot to create an FKey where there should be one.In short, I hate having PK-edit-routines because I never trust them to update all instances, and even if they are good now they might be bust in the future - its a very low-rent feature.But that is what we do ... its just that after years of doing this I am now very tempted to move to all-surrogate-keys camp in the near future. |
|
|
|
Next Page
|
Lot's of upheaval