TQ
dev.com

Should table names be singular or plural?

02 Jan 2021 - by 'Maurits van der Schee'

As we all know "naming things" is jokingly named as one of the two hard problems in software engineering (the other one being "cache invalidation"). This post is about the question whether your (database) table names should be plural or singular, e.g. "posts" or "post". You should read the following Stack Overflow post to understand that this is a heated debate:

Stack Overflow: Table Naming Dilemma: Singular vs. Plural Names

In this post I will portrait my (albeit late) take on this in the context of web application development (spoiler: you should use singular).

How it was

• Rails has a convention to use plural table names. It also sports a "pluralize" function to facilitate conversion.
• Other frameworks required "configuring" the table name for every entity/resource, in which case plural made sense (in SQL) and was recommended.

What changed

• We have been building CRUD apps and API for more than 10 years and we started to automate using default routes and reflection based APIs (note that Rails was already facilitating this meta programming with it's inflector functions).
• REST is the new SQL and we moved from MVC/ORM based web apps to REST API based. This also meant that URLs like "/posts/delete/23" became "DELETE /posts/23".

Why plural is better

• Typically a table contains multiple records, hence a plural name fits the content better. Your socks drawer will be labeled "socks", not "sock".
• Also, the SQL reads less naturally: "SELECT * FROM posts" reads better than: "SELECT * FROM post".
• Typically the route for "list" in MVC/REST is "GET /posts", which makes more sense than "GET /post".

Why singular is better

• The models, routes and tables have consistent naming when you the database table is named "post" instead of "posts".
• Foreign keys can have simple consistent naming, such as: "{relation_name}_{table_name}_id". So that the author of a post becomes: "author_user_id".
• The routes for "read", "update", and "delete" in MVC/REST make more sense in singular form: "GET /post/23"
• Inflection functions (such as "singularize" and "pluralize") are language specific and thus hard to implement (and often not implemented).

Conclusion: use singular

The world is changing, generated REST and MVC are becoming the standard and inflection functions have proven to be problematic. This makes the best practice of plural table names cumbersome. We should adjust the best practice and switch to singular for everything now. Let's do this now, it is not too late.

Enjoy programming!

PS: Liked this article? Please share it on Facebook, Twitter or LinkedIn.