The table naming dilemma: singular vs. plural
The other day, while in a planning poker session, the question of the naming of a particular table arose. During that conversation, one of our developers suggested that the table shall have a singular name, while others questioned that idea and thought that every table names should be plural. This led me to ask this question: is there a better choice? Should table names be singular or plural?
It’s funny to see that despite the fact that there is no clear convention regarding this topic, it is a question that gets asked pretty often, and it is the source of big debates in the DB world.
Using plural names
Narayana “Vyas” Kondreddi (long-time DBA and SQL Engineer) wrote back in 2001:
Tables represent the instances of an entity. For example, you store all your customer information in a table. Here, ‘customer’ is an entity and all the rows in the customers table represent the instances of the entity ‘customer’. So, why not name your table using the entity it represents, ‘Customer’. Since the table is storing ‘multiple instances’ of customers, make your table name a plural word.
It feels logical, and somewhat “natural”. You store several customers inside a table (those “multiple instances”), so the table should naturally be names
customers. It also makes sense when writing an SQL statement. When you want to go through all your customers, you
SELECT * FROM customers...
When using plural name, one can consider a table like a crate containing several items. A crate of apples should be labelled Apples, whether it contains one or a hundred apples.
Using singular names
The people that advocate the usage of singular names often cite the fact that when pointing at a database record, it feels confusing to use a plural name to describe a single item. Thus, why should you use
clients.name when you're pointing only at one client?
This is enhanced when writing SQL statements, that suddenly appear more natural. For exemple,
SELECT activity.name feels better than
It also avoids confusion of english pluralization which can sometimes be tricky for non-fluent writers. e.g. activity becomes activities while person becoms people and data remains data…
When using singular names, one can consider using the “set theory” to tables, meaning that any instance in the set is representative of the set, so
apple is an Apple set. It is agnostic of how many apples are in the set.
What should you use?
I personally do believe that a database table is the representation of a collection of items. As such, it should be named using a plural word, since it stores several copies of an entity. Thus, each
user is stored inside the
The only thing that could have made me consider using singular names is the SQL statement point, as it feels less natural to use a plural name for a query on a single item. If you’re bugged out by that point you can still use this simple trick to circonvert that issue:
SELECT id, name, description FROM products product WHERE product.name = ‘foo’ AND product.description = ‘bar’
It’s even more practical when dealing with more complex pluralization:
SELECT id, name, description FROM activities activity WHERE activity.name = 'foo' AND activity.description = 'bar'
If you’re really concerned about writing good looking code that feels like proper english, my advice is to use plural names for tables, but single names for the corresponding entities. As such, for e.g. in Symfony you’d declare an entity
Activity as such:
activities stores all instances of the possible object
Activity. Thus, when invocating and working with that object it makes sense to use the syntax
$activity = new Activity() and
If I had to give you one piece of advice though, it would be to this: be consistent. Stick to whatever convention you’ve decided, and apply it throughout your project. Use what feels the most natural to you and your team. Having the ability to rely on convention is often a good thing, but there are times when a convention is not needed. What’s the most important is to make a clear choice and adopt it. Of course, it’s also important to keep an open ear to the arguments people could provide that’d make you divert from that choice.
If you’re still looking for some guidance, here are some other tips you might want to use when naming database tables:
- Use short names for your tables
- Use underscore to separate words (no spaces or camelCase), for e.g.
- Be somewhat descriptive: a table name should represent its content. It’s easier to read
- Use unique names that cannot collude with SQL/RDBMS reserved words (avoid
- Do not use the table name followd by “id” (e.g.
client_id) as your PK.
idis more than enough and everyone will understand
- Actually, never use capital letters in your table or field names. Ever.
Feature image: Eli Francis