How to name database tables

Plural or singular for table names

There is no convention that a table must be named in singular or plural. For our exercises we will use the singular form, but many frameworks (e.g. Laravel) expects tables to be named in plural fashion.

It's only important that you are consistent: Either use plural or use singular but not both.

Language

As it is true for naming classes or variables in programming, don't try to mix different languages. If you already know that you want to sell your application, you might use English in the first place.

Use snake case for table naming

This one is important: Don't use camel-case, whitespaces or any other format to name your database tables:

  1. SQL queries are more difficult to write if you have to quote them because of whitespaces.
  2. Depending upon your system environment, your table names might be case-sensitive. This means, the table "user" and "User" are not the same.

Don't use special keywords of your DBMS

Even if you can use order as table name, you should not. ORDER is an SQL keyword and should not be used as an identifier. The same is true for words like or analyze. Take a look into the keyword list of your database implementation.

Use special words like 'in'

In our exercises you'll find a lot of tables which are using the word in. It has the meaning that this a table for an m:n relationship in which the first the first (left) table belongs to the second (right) table. E.g. user_in_post.

Try to not use abbreviations

It might be tempting to use abbreviations for your table names. For external developers it might be difficult to understand what the meaning is behind this. You should go with a descriptive name.

To be fair: Depending upon your database management system it's not always possible to use the full name due to the length, exceeding the maximum allowed characters for a table name.