- You are here:
- Home »
- Database Design
- » Naming Primary Keys
Naming Primary Keys
Poorly Named Fields Lead to Mistakes
There are lots of things that irritate me about poorly named database fields, but one of the worst is naming the primary key field “ID”.
Naming primary keys, or any other database field, is an afterthought for many programmers, but not taking the time to use a consistent naming convention leads to confusion and errors.
Let’s take 3 tables – Product, Invoice and InvoiceItem. And let’s assume we’re using artificial (surrogate) keys (my preference).
Product
ID
ProductName
Invoice
ID
InvoiceNumber
InvoiceItem
ID
InvoiceID
ItemID
ItemName
It’s pretty easy to guess in this example that Invoice.ID = InvoiceItem.InvoiceID. But you still need to go into the table design to verify the relationship actually exists.
But what about that ItemID field? It should relate to the Product.ID field, but the name is misleading. Now we have no choice but to go into the table design to find out which field links back to the Product table.
Also consider your queries:
SELECT Invoice.ID, InvoiceItem.ID, Product.ID, Product.ProductName
FROM Invoice
INNER JOIN InvoiceItem ON InvoiceItem.InvoiceID = Invoice.ID
INNER JOIN Product ON Product.ID = InvoiceItem.ItemID
First, the relationship between the fields is a mess because there is no consistent naming. You can’t tell just from looking whether or not the query is correct.
Then, you end up with the following dataset:
ID ID ID ProductName 16 23 49 My Product
Which ID came from which table?
This is a simple example, of course, but when you’re writing a complex query with multiple tables, having an “ID” field in every table gets confusing really fast. It gets exponentially worse when you have more tables and lots of foreign keys.
Choose a Naming Convention
A better way is to use the table name in the ID field:
Product
ProductID
ProductName
Invoice
InvoiceID
InvoiceNumber
InvoiceItem
InvoiceItemID
InvoiceID
ProductID
ItemName
The primary key is always tableID. It’s consistent in every table, and the foreign key has the same name as the primary key.
The InvoiceID fields are now obviously related, and you don’t need to guess which fields to join on. Same with the ProductID fields.
Take the time to give your primary key fields unique names that are consistent from one table to the next. It avoids confusion, and makes it easier to check that your query is correct.
Naming primary keys properly is a simple way to make your database easier to use. The programmer who takes over after you leave will appreciate it.
About the Author Phillip Theriault
I am a computer programmer specializing in building database software with ASP.NET MVC, C#, and SQL Server.
Related Posts
Naming Tables