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. It's only important that you are consistent: Either use plural or use singular for table naming but not both.

For our exercises we will use the singular form, but many frameworks (e.g. Laravel) expects tables to be named in plural fashion.

Personally, I am an advocate of naming tables in their singular form:

  1. There is consistent relation between your database table and the corresponding data model of your application. Both are named the same.
  2. Table names should describe the type of data in it and not their amount. Almost all of the time, a database table contains more than one tupel. There is also no need to say that a database a number of tupels in it.
  3. Your might encounter problems with your tooling, e.g. framework or code generators, if your model has an 'y' as the last character in its name, like city or boy. The plural of a noun depends the character before the 'y', a table would be named cit__ie__s or bo__y__s. You have to consider those cases.
  4. When having models with an optional 0..1 relationship, e.g. a User and their UserDetail, using plurals would result in table names like users_details or details_in_users. The table name implies that there can be more than one detail per user.
  5. Pseudo argument: Writing an SQL statement like SELECT * FROM city is shorter than SELECT * FROM cities.

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 the English language 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.
  3. When using Microsoft SQL Server, you can use PascalCase instead of snake_case: A lot of Microsoft SQL Server-based applications are using PascalCase for naming tables, including the famous Adventure Works sample database.

Don't use special keywords of your DBMS

Even if you can use order as table name, you should not use it. 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.