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.

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:

ProductProductID
ProductName

InvoiceInvoiceID
InvoiceNumber

InvoiceItemInvoiceItemID
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.