Let’s take a table that stores invoices. A lot of people would name that table “Invoices”.
Simple enough, but what about related tables?
A table that contains invoice line items may be called “InvoiceItems”. Let’s add a third called “InvoiceItemDetails”.
What you end up with now, is the following order:
Now your tables are in alphabetical order, but completely out of logical order. Invoices is the parent table, so it should come first, followed by InvoiceItems, and InvoiceItemDetails.
This is why I like using the singular:
Simply removing the “s” puts everything in logical order, the way they should be.
It’s a simple thing, but it makes it easier to find what you’re looking for.
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).
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
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.
A better way is to use the table name in the ID field:
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.