I recently had a discussion with a colleague about the naming of tables and whether or not a plural table name makes sense. The argument for pluralizing had two interesting, competing viewpoints for which I’d like to discuss and give my reasoning why I prefer the singular form.

“A sock drawer contains socks. You wouldn’t label your sock drawer ‘sock’.”

“It’s not a ‘drawer of socks’, it is a sock drawer.”

Two competing arguments for the plurality of naming conventions.

Firstly, I’d like to point out that “socks” is inherently plural as they come in a pair; I would argue that this alone negates the entire conversation. However, I still think it’s a common struggle when naming tables and objects.

The question is: Do you look at a table from a holistic point of view, the collection as a whole, or do you look at it from the view of the records within the table, which make up the collection?

Records within a table represent the collection and that too is how you should classify the collection.

Records within a table represent the collection and that too is how you should classify the collection. It’s really that simple. There are numerous ways to debunk and disprove any argument for pluralizing your naming convention. For me, however, I feel there are three strong arguments and I’d like to discuss them using a real-word example:

Plural Table Name Real-World Scenario

Let’s say we have a data pipeline which has strong data quality (which should go without saying). In this pipeline there are many data-quality-checks which are performed throughout the process, some tied directly to a single data point, some to its sources and targets, and others between two entirely different data sets. These checks are stored in a table we’ll call “checks.” Additionally, as the checks are performed, we will log the check counts/deviations/results into a “check_results” table. Taking this one step further, each record within the result is stored individually into a detailed log of each deviation, we’ll call this “check_result_details”.

Here’s a look at what a schema like this could look like:

Hierarchy Of Objects Rule

There’s usually a logical hierarchy of objects within data, the one-to-many and many-to-many relationships within data models can’t help for this to be a reality. This hierarchy is natural and pluralizing seems to make perfect sense. Until, that is, you work with these objects and begin to see an annoying pattern when using a plural table name.

When I query my check_results table, I need to know what check the check_id is referring to, now I can’t simply remove the “_results” I also have to add an “s”.

Same for when I query for check_results and now want to see the details of the check’s result. I cannot simply add “details” I have to first remove the “s”.

It would make much more sense for ease of querying if that pesky “s” wasn’t there at the end of each plural table name.

Ordering Of Objects Rule

This should be quite obvious, but if you look into your database’s information-schema (metadata) and order by the tables in our example, this is what you’d see:

Notice how the order is completely wrong and doesn’t make sense at all! The hierarchy is broken, the ordering is broken, and now everything is wrong and scary 😱

For this to work properly, you’d need to make everything plural:

1. checks
2. checks_results
3. checks_results_details

Collection Of Objects Rule

The main point here is we’re referencing a table during a query and that table is made up of rows of data. The descriptor isn’t the table but what the table contains. Like our example of check results, it’s referencing a single result for a given check, it’s referencing a single detail-record of a given result. Making these singular objects plural doesn’t make sense in this example or in any example.

Your customer table is place to store your single customer record. Your sock drawer is a place to store your single sock; since, as we all know, that matching sock is behind the dryer and gone forever (because of your poor data sock quality.)