Skip to content

SelectDistinct.io

A tech blog about data, warehousing and analytics.

Home » Dimensional Design » Plural Table Name(s)

Plural Table Name(s)

October 8, 2020 / Matt / Leave a comment
Gears of plural table name

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:

  • Hierarchy of objects
  • Ordering of objects
  • Collection of objects

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:

Data quality checks ERD of example schema using a plural table name

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”.

PgSQL
0
1
2
3
 
select * from check_results;
select * from checks;
 

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”.

PgSQL
0
1
2
3
 
select * from check_results;
select * from check_result_details;
 

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:

PgSQL
0
1
2
3
4
5
6
 
Table_Name           Order  Logical_Order
-------------------- ------ --------------
check_result_details 1      3      
check_results        2      2      
checks               3      1      
 

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.)

Share this post on your page!
Share on Facebook Share
Share on TwitterTweet
Share on LinkedIn Share
DBMS, Dimensional Design Data Quality, Metadata, Naming Conventions

Post navigation

Older post
SQL | Find Patterns In A Dataset

Recent Posts

  • Plural Table Name(s)
  • SQL | Find Patterns In A Dataset
  • When To Use A CTE – And When Not To
  • ETL, DDL, & Self-Documenting Code Generator
  • It’s Performance Review Time!

Categories

  • Business Objects Data Services
  • DBMS
  • Dimensional Design
  • Interview Questions
  • Javascript
  • JSON
  • Python
  • Random Thoughts
  • Reporting
  • SAP
  • SQL Foo
  • SSRS
  • Window Functions

Archives

  • October 2020
  • July 2019
  • June 2019
  • May 2019
  • November 2018
  • September 2018
  • July 2018
  • June 2018
  • May 2018
  • March 2018
  • February 2018
  • December 2017
  • October 2017
  • September 2017
  • January 2017
  • December 2016
  • November 2016
  • October 2016
  • September 2016
  • August 2016
  • July 2016
Delivery by Matt Komyanek