Opinion: Singular vs Plural Table Names in SQL Server

Opinion: Singular vs Plural Table Names in SQL Server

There is a near-religious debate in the development and DBA communities about singular and plural table names. It’s pointless rehashing all the arguments but I want to spell out what I do, and why.

What’s driving this post is that I had a developer tell me that I was doing it inconsistently because even though I generally use plural names, that he found a table where I used a singular name. He thought this was inconsistent. It’s not, and this is why.

OLTP Databases 

In traditional OLTP databases, I use generally use plural table names ie: my table will be Sales.Customers, not Sales.Customer. This is to make it clear that the table is a set of data about a set of customers, not information about just one customer.

The main people who complain about this are those who think a table should directly relate to an object. Well, it doesn’t. If it did, it still would be a Customers object that was a collection of Customer objects. The table would not map to one Customer.

However, all rules are meant to be broken. The one exception that I make to this is for tables that will only ever hold a single row of data ie: where more than a single rows is not logical or meaningful.

So I might have a table called Application.SourceDatabase if that table will always have one and only one row. And I won’t consider that to be inconsistent.

OLAP Databases

Dimensional warehouses are perhaps treated differently.  For me, it depends upon whether there will be a view layer above the database, that’s used by any analytic system that accesses the database.

I want analytic systems to see human-presentable data.

By human-presentable, I mean names that are ready to go directly into analytic or reporting models. This does mean they will have, for example, embedded spaces in names where sensible.

So I use one of two options for dimensional models:

  • (Less preferred) The database has singular table names, all names have embedded spaces where needed, except for key columns, lineage columns, etc. that are not ever part of the UI. The database can be consumed directly by those analytic applications.
  • (More preferred) The database is set up with the same naming as an OLTP database above, and has a view layer that provides the human-readable data. The analytic applications talk to the view layer, not to the database tables.

2018-07-10