Archive

Plural vs. Singular

Edit: 24 April, 2021

If you’re thinking about naming your table with a plural name, let me stop you right there! Some people will say “who cares, it doesn’t matter,” or suggest a plural name, but I would like to argue it does matter and there is a reason and intention for singular table names.

When giving little thought into designing a relational database or not having a greater understanding in the context of relationships between objects and what a record represents, it would be very easy to think “Hey, this table contains millions of rows so I will name it Orders instead of Order.” Sure, it makes sense, we are dealing with a plural set of data so why not call it as it is? No! Let’s think about what the data and record is actually representing.

Using the same example Order, how would you describe each record and what the representation of that record is? Of course, we understand each record is a representation of a single Order so that is what we want to refer it as, Order. While a table is a collection of multiple records, a table is named after the definition of the one type of record that it contains. Just because a table contains a list of records, it doesn’t define how we understand the data and the relationship with other objects. The name should identify what a single record is.

If we have a table name with Order we can have columns as such:

OrderId CustomerId CartId VendorId Status CreatedDate

Now say we name our table Orders, technically we would want a single record represent a set of Orders, so each record would represent an aggregate of orders:

OrdersId TotalCount PurchaseDate Status

I don’t feel comfortable with this example because I wouldn’t even have an Orders table to aggregate the data, I would just use the Order table and write a query but I’m using this for demonstration and comparison purposes to show the intent of a table name. As you can see, the two tables represent very different information.

Shane Lively & Michael Sarsany explains it well in their SQL Server Standards report, “Table names should be singular, for example, "Customer" instead of "Customers". This rule is applicable because tables are patterns for storing an entity as a record – they are analogous to Classes serving up class instances.” This is a great report and they cover many other naming standards including prefixes and using special characters. Check it out here.

What NOT to do:

  1. tblEmployees
  2. tblProducts
  3. tblStudents
  4. tblEmployeeDetails

If you have a good reason to use plural names and can think of a good scenario, please share your thoughts.


References

Lively.S, Sarsany.M. (Unknown date). SQL Server Standards v1.5. Retrieved from https://www.isbe.net/documents/sql_server_standards.pdf

Live Chat
Enter username to begin chatting: