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.
I personally agree with you on all of your points, but… I've had this exact debate over the years, mostly with Object Oriented application developers. I can see their points (I happen to span both database and application dev worlds)- eg. ORM's are (usually) set to pluralise the table name, so if you name your table [Customers], this then becomes Customerss (ie. double 's') at the object collection level in application code which is annoying. App devs see the table name as being the singular entity and they want a collection of entities. Whereas a db guy sees the table as the collection of rows and a row as the entity. I don't think there is a right or wrong answer to this. If a dev project is mostly db dev work, I'd pick the db guy approach, but if the db is just dumb storage for an application, I'd pick the app guy approach. Whatever the team picks, just be consistent.
Hi Kevin, I get what you're saying but doesn't the message really boil down to having a poor database design, because of issues with some ORMs? Is that a good reason? Even auto-pluralization doesn't work anyway. You can't just add an "s" to a word to make it a plural.
The dev guy who just sees the DB as a dumb storage location is also the problem. I see these guys all the time causing issues in enterprises. Many are used to building toy systems and they take these approaches into enterprises. A simple example is that in almost every enterprise, other applications or tools will want to talk to that same data. Devs shouldn't be building tons of unrelated silos of information.
Is it a good reason? No, I don't think so.
In recent years I have found myself part of a 5-10 person dev team spanning both app and db dev work, with most devs having a background in app not db. In my experience at least, I'm never going to win that debate.
I decided long ago that it isn't worth getting frustrated about, I just go with the flow.
Who cares? I mean really. I have used both conventions depending on the preferences of the group of devs and DBA's I happen to be working with for each project. You know what, at the end of the SLDC it meant nothing and the final product or service always works as advertised.
It's surprising how many care, so worth stating an opinion and justifying it.
The other point worth making is that often development teams will think it matters at the code object level but not at the database, which is a curious concept. If they have a car object and it has a collection of wheels, they'd say that car.wheels is appropriate and car.wheel isn't appropriate for the collection. But will decide it doesn't matter for the database. That's odd.
If you treat the table (data entity) as a class then any rows in that table will be instances. So instead of thinking of a table as a bucket of customers think of it as being a pointer to one customer or more, but it's not a collective of customers, each customer is individual.
Hi Martyn, it's not a class though. And a direct mapping from a table name to a class name isn't needed.