July 18, 2007 at 7:14 am
I've seen two methods of naming primary keys:
1) All primary keys are named ID.
This has the following advantages:
- it's easy to find out which column is a PK and which isn't;
- it's less typing;
- join conditions read more like English:
... WHERE File.DirectoryID = Directory.ID
... WHERE BlogEntryTag.TagID = Tag.ID AND BlogEntryTag.BlogEntryID = BlogEntry.ID
2) All primary keys are named <tablename>ID.
This has the advantage that the primary key column and the referring foreign key column will have the same name. Apparently, some people think this makes it easier to write tools and scripts:
... WHERE File.DirectoryID = Directory.DirectoryID
... WHERE BlogEntryTag.TagID = Tag.TagID AND BlogEntryTag.BlogEntryID = BlogEntry.BlogEntryID
Personally, I prefer the first method, but I've seen very few places that implement it and a lot of places that implement the second method (or a more or less ugly abomination of it).
Which one do you prefer and why?
July 18, 2007 at 7:45 am
I do prefer the second one its easy to join columns when the column names are same and also easy to remember them.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
July 18, 2007 at 2:01 pm
Even with the standard of ALWAYS using TableName.FieldName, ensuring that field names are unique within a database provides greater sanity when talking about the names. How many conversations have you had when someone said 'ID' and you had no clue which of the 5 tables under discussion were being referenced? At least if the PKs for each table are uniquely named, there's no ambiguity. For that matter, why not use PK instead of ID? Does anyone do that?
July 19, 2007 at 10:47 pm
From here it looks as though Method 1 and Method 2 are the same. Both say, "All primary keys are named ID." Am I missing something?
July 19, 2007 at 11:00 pm
it was <tablename>ID , but the browser ate the tablename in angle brackets
July 20, 2007 at 12:29 am
I agree that method two is better as it keeps the field names identifiable, especially if you use table aliases in queries. As to ID vs. PK I use <TableName>ID for the primary keys. I always use the same field name in any other table that has an FK to the ID field with the simple exception that if you have two FKs to the same field I name the fields using a descriptive name and suffix them with FK to signify that they reference a field of a differt name, e.g.
Normally
StageID references StageID
but the following two references from the same table would be allowed
StageFromFK references StageID
StageToFK references StageID
July 20, 2007 at 5:35 am
We use method two. Since it is possible for one tables ID field to be used in more than one other table, method two makes it easier for us to see potential joins whether or not we have foreign key relationships in the tables.
As far as Primary Key verses Identity, well that's a sticky wicket. @=) Sometimes it makes sense for us to combo up our Identity field with a Primary Key Index. Other times, our application is actually searching on other fields to pull stuff up in the client.
For instance, if you have an application attached to Adventure works, you might have a Parts screen and a Vendor screen. It might make sense for the Parts screen to pull up by PartID, so you set that as an Identity & PK. But on the Vendor screen, chances are you're going to want to pull up Vendors by name or region. So why would you set your VendorID as the PK? It makes more sense in this instance to use VendorID as your unique column (for joining) but to use VendorName as your indexed column. And if your Vendor names aren't unique enough, use the VendorID/VendorName as a combo PK index.
Of course, there is truly no "right" way to do it, but that's just how I would approach it.
July 20, 2007 at 6:12 am
Almost every tool available for setting up a database schema will default to matching columns with the same name. So, naming the columns <TableName>ID and having this name as the foreign key field name as well will tend to make query building tools work better.
However, consistency is really the key here. You could name all of your primary key fields "Bob" and as long as you did it everywhere, it would be easy to follow. Both plans you have suggested will work great - go with whatever makes you comfortable.
Oh - if you are trying to save a few keystrokes and possibly make thing a bit more confusing - make the extra keystrokes.
July 20, 2007 at 8:46 am
Method 2.
I've been given Method1 and it's a pain.
July 20, 2007 at 10:33 pm
Your best bet is method 2. I understand the [TableName].[ID] naming convention from the object perspective. When you move that to a database will end up using an alias for the key so that Customer.ID migrates to the Order table as CustomerID and the Orders.ID column migrates to the OrderItem table as OrderID. Method 2 custs to the chase and gets to what the column really is -- a handle to a record.
--Paul Hunter
July 24, 2007 at 6:52 am
Method 2: prefixing the tablename to "ID" when using arbitrary unique identifiers is best practice. The reason is that when the Primary Key is migrated as a foreign key, it can retain the same name in the child table.
When using "ID" only and a table is a child of two or more parents, they must be role-named anyway.
July 24, 2007 at 8:40 am
If you follow no other rule in naming conventions, it should be that columns with the exact same information have the exact same name, and only columns with the same information should have the same name.
Method 1 breaks that rule both ways: it creates many columns called ID which cannot be joined together, but which should be joined to columns with different names.
July 24, 2007 at 2:36 pm
Method 2 is my preferred way but with this slight addendum. It works better when the tables themselves adhere to a good naming convention. I prefer to give tables a plural form of the entity name and the primary key the singular form. For example:
Parts( PartID,...)
However, all this applies only for artificial keys. When using a natural key as the primary key, the column name should reflect the attribute and should not be changed to adhere to some Naming Convention For Primary Keys. So if you have a table FinalAssemblies and the primary key is SerialNumber, then don't rename it to FinalAssemblyID or SerialNumberPK or any other such nonsense.
Oh, I have sometimes seen the PK postfix used instead of ID. What can I say? It sucks.
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
July 24, 2007 at 3:30 pm
We follow this naming convention, and actually enforce it.
Table name: T_entitynameExample: T_ORDER_ITEM
Identity Column Name: entityname_IDExample: ORDER_ITEM_ID
Primary Key Name (name of the PK constraint, not the column) PK_tablenameExample: PK_T_ORDER_ITEM
July 24, 2007 at 3:32 pm
Michael,
Do you really like typing all these underscores? Couldn't you get away with camel case? To me, tOrderItem, OrderItemID, PK_tOrderItem look much more readable.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply