| Author |
Topic |
|
crugerenator
Posting Yak Master
126 Posts |
 Posted - 2010-05-21 : 16:37:18
|
| I'm rather... junior... and just started working on a project that involves a database where every primary key is named the same. So every table's primary key is just 'id'. I've never seen this before and it's extremely annoying to me since I'm not used to it and cannot easily spot pk <-> fk relationships since the fk can be named anything. For example, you can join 'asdf' from table 1 to 'id' from table2.Is this bad design? What's the point of using the same name for every table's primary key? |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-05-21 : 16:48:07
|
| Its common to have all PK's as "ID"We never do it because we think it increases the risk of incorrect joins on IDs that will "join" but are not logical.We have unique names for EVERY column in EVERY table in our application.There is no "right" way, but I do think there are ways that reduce likelihood of bugs, and decrease re-familiarisation-time during maintenance. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-05-21 : 17:36:09
|
I've seen it before, and while it is annoying sometimes it can also be beneficial...you never have to guess which column is PK.  I've dealt with enough systems that didn't even have primary keys (much less foreign keys) that anything is welcome.The INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS view will list the PK-FK relationships by constraint name, which you can join to INFORMATION_SCHEMA.TABLE_CONSTRAINTS and INFORMATION_SCHEMA.KEY_COLUMN_USAGE to get the details. |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2010-05-21 : 20:46:21
|
| I've seen this in various systems. It is not just related to SQL Server, as I've seen it in Oracle systems, Cache systems, Pick systems, etc...The real problem I have seen is that someone new to the system will try to join the PK ID from the Tasks table to the PK ID in the Persons table, which of course is not correct.Ideally, every attribute in a database/schema should be unique to that domain. |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2010-05-25 : 08:48:27
|
| Its loony, and amateurish.That's my vote.________________________________________________If it is not practically useful, then it is practically useless.________________________________________________ |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2010-05-25 : 09:51:02
|
| Like many other "short cuts", people make the mistake of thinking it is easier, but they end up paying for it in confusion and bad code.CODO ERGO SUM |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2010-05-25 : 10:35:58
|
| Right. Like gratuitous table aliases..... :)________________________________________________If it is not practically useful, then it is practically useless.________________________________________________ |
|
|
|
agrajtech11
Starting Member
2 Posts |
Posted - 2010-05-26 : 07:56:44
|
| Yes i think this is really a bad kind of making database. You should rename all keys and primary keys because it will make confusion in your mind while working. It may possible that it will not generate error but will surely make you in trouble.(Link Removed) |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2010-05-27 : 04:18:06
|
quote:
Originally posted by blindman
Its loony, and amateurish.That's my vote.________________________________________________If it is not practically useful, then it is practically useless.________________________________________________
I concur.. Just had a developer give me a system like that, so I created the tables how I wanted and put views over the tables how he wanted (he's leaving and didn't have time to rewrite his code [the joys!!]) |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2010-06-01 : 16:32:40
|
| At his farewell luncheon, tell him blindman thinks he is loony and amateurish.________________________________________________If it is not practically useful, then it is practically useless.________________________________________________ |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2010-06-02 : 04:02:00
|
| Don't worry, I will.. |
|
|
|
|
Every table's PK has the same name???
Tara Kizer