November 15, 2013 at 6:09 am
I have a team of Dev. that created a database with tables that each table has only one PK called ID as integer, in the child tables they join to tables with that PK from the parent to the child based on that key.
Example
State
PK ID int
Code CHAR(2)
Name VARCHAR(50)
City
PK ID int
Name VARCHAR(50)
StateID int
ZIP VARCHAR(10)
ect....
StoreLocation
PK ID int
Name VARCHAR(30)
CityID int
Book
PK ID Int
Name VARCHAR(30
BookLocation
PK ID int
StoreLocationID int
Count int
They did put unique second keys on the logical primary key of the table, so that the database will have only one StateCode for example.
My question is has anyone worked with a database like this, where ever table has only one PK column called ID?
I have asked that they change the name of ID to the table name id, example State table the ID would be called StateID to match the table where it will be used in.
I know SQL would like to join the table on a single integer ascending one from each table but what other problem would I see?
Any other pit falls for support?
Thanks
November 15, 2013 at 6:24 am
mstanl (11/15/2013)
I have a team of Dev. that created a database with tables that each table has only one PK called ID as integer, in the child tables they join to tables with that PK from the parent to the child based on that key.<snip>
They did put unique second keys on the logical primary key of the table, so that the database will have only one StateCode for example.
My question is has anyone worked with a database like this, where ever table has only one PK column called ID?
I have asked that they change the name of ID to the table name id, example State table the ID would be called StateID to match the table where it will be used in.
I know SQL would like to join the table on a single integer ascending one from each table but what other problem would I see?
Any other pit falls for support?
Thanks
For the naming convention, where everything is "ID", well it's against best practice, for sure.
PK should always be tablename + ID, is what i've been told, and seen in my career.
the key in the foreign table must always contain the full name of the key from the referenced table.
so if you needed, two stateID in an address table for example it would be columns HomeStateId and BusinessStateID, which join to State.StateID
.
that is very beneficial when you are creating joins, obviously.
can you make the developers follow that best practice?
Lowell
November 15, 2013 at 9:07 am
I've done things as Lowell has suggested. Some ORMS or tools might make an "ID" in every table, but this does become confusing. I'd have
StateID in State table
CustomerID in Customer table
etc.
November 15, 2013 at 9:33 am
i always felt that if the ID column was in a Table called 'State', then its self explanatory that its the 'StateID'.
so it was unecessary to call the column 'StateID'.
It also makes it easier to see what is the Primary Key and what is the Foreign Key, in your query, where 'ID' is the Primary Key and [tablename]ID is the Foreign Key.
e.g
where t1.ID = t2.StateID
as opposed to:
where t1.StateID = t2.StateID
November 15, 2013 at 10:00 am
davidandrews13 (11/15/2013)
i always felt that if the ID column was in a Table called 'State', then its self explanatory that its the 'StateID'.so it was unecessary to call the column 'StateID'.
It also makes it easier to see what is the Primary Key and what is the Foreign Key, in your query, where 'ID' is the Primary Key and [tablename]ID is the Foreign Key.
e.g
where t1.ID = t2.StateID
as opposed to:
where t1.StateID = t2.StateID
I don't like this for three reasons.
1) Ambiguity. ID by itself is meaningless. This is like having a column named Date. It gives no indication what it means. I realize that with ID it is kind of a clue but you have to stop and think about what it means.
2) (one of my biggest annoyances with some systems) is that a column name should NOT change its name based on usage. What I mean by that is we should not change the name of a piece of information just because it is in another table. This leads itself to things like StatePK in the States table being changed to StateFK as a foreign key.
3) The same named column meaning different things in different tables. For somebody new to your system they would look at your tables and very possibly write something like this:
where t1.ID = t2.ID
Notice how strange that becomes. It looks perfectly normal except we have no idea what ID means and why can't you join these two tables on columns with the exact same name? Because we have to add context to the names in order for this to work.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 15, 2013 at 10:23 am
Thank you all,
And Thank you Sean Lange for the link,
Mike Stanley
November 15, 2013 at 10:45 am
Functionally, it's no big deal. A little bit of a pain having to maintain two unique indexes, one for the PK and one for AK, but I've worked with such systems in the past. They function fine. The naming standard of just an ID column is also not the end of the world, but it removes clarity and that's something you must strive for when writing code. It's just going to make things difficult down the line, but it won't be a showstopper.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 15, 2013 at 2:03 pm
As Grant said, the naming issue is not the end of the world, but it can create lack of clarity which can result in confusion; but unlike Grant, I believe that such confusion can be a show stopper unless everyone involved in writing, debugging, modifying, and maintaining the code is thoroughly aware of teh issue and on their gueard against confusion and misunderstanding.
As Grant says, the problem with working with two keys is that you need two indexes on the main table, which is an overhead in both storage and execution time. However, usually that overhead is swamped by the storage and execution time savings resulting from use of the surrogate as primary key in auxiliary tables and as the target of foreign keys (there's no excuse for using a surrogate when the savigs don't match the costs, but that rarely happens). In my experience people have tended to get this right once they've understood the concepts of forign keys and auxiliary tables, and been able to avoid using surrogates for primary keys where doing so costs more than it saves.
It's bad practise in the table that ties a surrogate key to the real primary key to call the surrogate the primary key and mark the primary key with a unique constraint (and not null constraints for its components). Call the real primary key the primary key, and mark the surrogate not null and unique. You can still use the surrogate to save space in the all the usual ways: refer to it, not to the primary key, in foreign key constraints; use it, not the real primary key, as primary key in auxiliary tables that are split off from the main table because their data is rarely accessed or because only a few rows in the main table have data in this table; and of course if you are an anti-null nut you can also still use it in a similar way to arrange to have no nullable columns in your base tables without wasting space by repeating the real primary key in lots of tables. The reason having these things the right way round in the table where the surrogate is defined is quite simple: the primary key is the key (there may be several keys, but only one is primary) that is designated for an interactive user (not a piece of application code, but a real live user) to identify a row in the table; a user will only look at auxiliary tables through joins, so he gets there going via a row chosen in the main table using the real primary key and that row tells him what the surrogate key is. It's not a catastrophe to call the surrogate the primary key in the main table, but it is in conflict with the definition of a primary key for a table that is meaningful to a human being.
Some people go one step further and insist on having a separate table that holds nothing but real primary key and surrogate, and then the main table and all the auxiliary tables use the surrogate as primary key; this costs an extra join on most queries, and extra storage space because the extra table both introduces an extra copy of each surrogate and replaces one index on the original maintain table with two on the extra table, so I've never been able to see the point of doing it. I suspect that it is this extra step that has led to some people railing against the use of surrogate keys, since their other uses are all benign (except in very small databases where auxiliary tables are sometimes examined directly by the mark 1 human eyeball, and in cases where there are no auxiliary tables, very few foreign keys, and the real primary key is a single fairly small column).
Tom
November 15, 2013 at 2:16 pm
Thank you very much for the reply Tom,
And yes Grant this is the same person that talked to you in Dallas SQL Saturday.
Mike
November 15, 2013 at 2:25 pm
mstanl (11/15/2013)
Thank you very much for the reply Tom,And yes Grant this is the same person that talked to you in Dallas SQL Saturday.
Mike
HA!
I'd say I recognized your question, but I actually deal with this one a lot. It's really a common pattern (for good or for ill as you've seen).
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply