Category Archives for Database Design

Naming Tables

Use Singular Names

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:

InvoiceItemDetails
InvoiceItems
Invoices

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:

Invoice
InvoiceItem
InvoiceItemDetail

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.

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.