This is the tabs vs spaces of SQL. Pick one and stick with it. If you join a project that has already chosen the “other” option that is not your favourite, just grow up and deal with it. Once decided, try to apply it consistently, unlike the final example given here which really ought to be “UserFactsCollection”, or List or Bag or whatever singular object they are actually saving per table row… and if you absolutely must write a blog post like this, don’t forget to preface the title with “I prefer to…”

I agree.

It is better to be consistent but wrong, than inconsistent but correct.

Consistent and correct might seem ideal, but merely the fact that what is 'correct' is in the eye of the beholder most of the time, making it basically unobtainable.

But being consistent is at least something that is far less subjective.

An old boss of mine used to say "Consistent is better than better" and it's always stuck with me.

I don't see consistent and correct as separate. Consistent is correct, and correct is consistent.

Be sure to be consistent with each of the edge cases in order to be correct!

> than inconsistent but correct.

than inconsistent but sometimes correct.

> which really ought to be “UserFactsCollection”, or List or Bag or whatever singular object they are actually saving per table row

I try to avoid putting nouns on things when they could otherwise be inferred from their context of use. In this case we know it's a table. The possibility of multiple items being included is implied.

Singular/plural debate is driving us to name stuff in weird ways. This is why you should just go with the flow. Consider what a non technical person somewhere else in the business might call it. Aligning with this language has tremendous benefits. Trying to force purity into a natural language and communications context is how we make type systems and schemas that are indecipherable to the business.

It's really not, when you have an ORM, eg with Rails (point #3).

Automatic conversion between singular and plural can be a real problem, so just eliminate the problem and stick with singular throughout, so your object names match table names. Unless your object names are plural too, though that seems unintuitive. And then you'd still have problem #4.

1. Strictly speaking, we’re not naming a table, we’re naming a relation.

And a relation is a set, hence plural.

2. It reads well everywhere else in the SQL query:

   SELECT Employee.Name
        , Manager.Name
     FROM Users AS Employee
        , Users AS Manager
    WHERE Employee.ManagerID = Manager.ID
      AND Employee.DateOfBirth IS NULL;

3. The name of the class you’ll store the data into is singular (User). You therefore have a mismatch, and in ORMs (e.g., Rails) they often automatically pluralize, with the predictable result of seeing tables with names like addresss.

The class User represents a single row, not the entire table, hence singular. If the O/R mapper or some other tooling has issues with singular and plural, then I agree, it might not be worth fighting the tools.

4. Some relations are already plural. Say you have a class called UserFacts that store miscellaneous information about a user, like age and favorite color. What will you call the database table?

I think having the table and the class name both in plural would be fine. That also seems rare enough in practice that I would not let this dictate the decision. In the given example I would also tend to record the user facts as a list of them. A user fact is a key value pair associated with an user, the keys living in their own table. Having the keys implicit as column names will also make some queries unnecessarily complicated and as the number of facts grows, the table will become increasingly wide.

Also sometimes we have singular names for collections of things, then it is fine to have a singular table name, you can name your Trees table Forrest if that makes sense in the domain.

> they often automatically pluralize, with the predictable result of seeing tables with names like addresss.

This is a very poor example, that case is literally in their unit tests file:

https://github.com/rails/rails/blob/b0c813bc7b61c71dd21ee3a6...

That test has been there over 18 years!

Are you advocating for using “select … from plural as singular” in all schema and queries?

  select sum(cat.length)
  from cats as cat

That’s an interesting approach I haven’t seen before.

(For me, I’m very much a singular it’s-a-datatype-not-a-collection person, but selecting from cat has always felt a bit awkward with that pattern.)

I think that is the correct way to do it, you iterate over all the cats picking one cat at a time and it becomes quite obvious when you join a table to itself forcing you to do this. I am not writing that many SQL queries and I am certainly too lazy to always do that, especially if I am writing not too complicated ad hoc queries, but if I want the code to be as good as I can make it, then I always do this. Sure, it makes things a bit more verbose but you can also make the query more readable by picking a descriptive alias, FROM Users AS Manager, FROM Users AS NewEmployee, and so on.

Whether you alias your plural tables to singular nouns would probably be one of the very last things I would check out if I would need to assess your code.

And you should not but I think it is still useful. You will probably not even become consciously aware of the difference but Cat.Name will be ever so slightly easier to read than Cats.Name and maybe that difference in cognitive load is what makes you spot some issue that you would have missed if your brain got repeatedly slightly tripped up by incorrect grammatical numbers.

A relation is a set in the same way that a function is a set. I don’t think that’s a useful fact when thinking about naming.

Funnily enough, the blog post itself has a counterexample in their singular noun example: the table^Wrelation name is `user` which is highlighted by the syntax highlighter as being a keyword. I'd wager the same thing would happen for a table named, say, `transaction`.

Some DB engines won't let you use a keyword as identifiers for anything unless it's quoted, and then you either have to quote everything, or end up in a weird situation where some things are quoted and some aren't.

I don't like plural table names as a convention, but not enough to not use Rails or change its default behaviors.

For me, it's in the same category as preferring generated primary keys were named <table-name>_id over just "id" - you may have a preference, but it's not got that much big picture importance.

If you have self-referential columns you are going to have to name them something else, like `parent_id` and tell your model code about the discrepancy.

My other favorite best practice with SQL is to have unique ID column names, so you never mix up IDs, and can use JOIN with the USING syntax:

  SELECT *
  FROM customer
  JOIN order USING (customer_id)

I've never heard of `using` syntax. And now you might make me reconsider my identifier naming usage :) It certainly makes sense.

The downside of this is GORM ( or new developer ) often just default to "id". Fighting against it will take away bits of your attention and effort.

Yep, it's unfortunately the industry standard, but in my career I've seen it lead to mixing up ids and painful debugging.

My reason for doing this is that there can be multiple foreign key columns pointing at the same table, each with a distinct purpose and name.

The general case is a named key.

Also related:

I skimmed classic Codd paper "A relational model of data for large shared data banks" which gave birth to RDMS.

Codd uses singular nouns for example relation names: PART, PROJECT, COMMIT, supply, component, employee, jobhistory, salaryhistory. The only exception is "children" relation.

So that's one small argument in favour of singular approach :)

SQLite has specific naming advice:

"For most SQL code, your safest bet is to never use any English language word as the name of a user-defined object...

"SQLite adds new keywords from time to time when it takes on new features. So to prevent your code from being broken by future enhancements, you should normally quote any identifier that is an English language word, even if you do not have to."

https://sqlite.org/lang_keywords.html

I want to drop here the not very well known fact, that the SQL Standard grammar distinguishes between "SQL language identifier" and "regular identifier". According to the rules, a SQL language identifier can not end with an underscore (copied from ISO/IEC 9075-2:1999 "5.4 Names and identifiers":

<SQL language identifier> ::=<SQL language identifier start> [ { <underscore> | <SQL language identifier part> }... ]

<SQL language identifier start> ::= <simple Latin letter> <SQL language identifier part> ::= <simple Latin letter> | <digit>

So, using names with trailing underscore should always be safe.

It_ might_ be_ safe_, but_ it_ is_ definitely_ annoying_.

I think (I hope!) we are probably done adding keywords to SQLite. Furthermore, all of the more recently added keywords (ex: WITHIN, RETURNING, MATERIALIZED) make use of special capabilities in SQLite's parser that allows keywords to be used as identifiers as long as the identifier usage does not occur in a context where the keyword is allowed.

So, for example, you can used MATERIALIZED as a keyword in a common-table expression ("WITH xyzzy(a,b) AS MATERIALIZED (...)") but MATERIALIZED can also be used as a column or table name. Hence, the following SQL actually works in SQLite:

   WITH xyz(MATERIALIZED) AS MATERIALIZED(
     VALUES(1),(2),(3)
   )
   SELECT * FROM xyz;

French table and column names, here I come! La vache query!

English also changes, so the only way to be safe is to quote all identifiers.

I'll just stop upgrading SQLite if they ever add "rizz" as a keyword

Sounds like bike shedding to me

Naming things is one of the hardest things in software engineering

Join tables are a bit more difficult if you pluralize tables. For example at work we have table names like:

    customers
    customer_attributes
    customers_labels

On one side you want to be consistent with the "customers" table you're joining to. On the other hand it reads weirdly to have an extra "s" in the middle of the table name.

After you've got three or so words in a table name it really becomes inconsistent and you can't really guess what they're called when writing code.

There are solutions of course: whether to use the "s" on join tables could be a policy documented somewhere and everyone, including new employees, could be made aware of it. But it's a problem you don't have if you use singular table names.

Having table names plural e.g. "customers" and object names in your programming language e.g. "customer" is one extra piece of complexity that's not necessary.

For example we have a table "customer_data" at work and in our generated ORM code it's called "customer_datum".

The open-source singularization library is doing its job, but it sure made it difficult for me to global-search for that object name when I joined the company and didn't know the object was called that.

data is basically the singular word at this point in English language history :). "Datum" now remains as a fun quiz for the people who don't know.

Strong disagree; there are multiple "things" in the "things" table. Also lead with your strongest argument, also `UserInfo` works, also it's probably not a good table (why isn't it just on `User`, why isn't it a many-to-many of user IDs to fact IDs w/ a value, etc. etc. etc.)

I agree with the article on the basis it's easier to not bother trying to use a convention of plural names as there's a lot of time-wasting edge cases, and generally it's less weird seeing no plural than seeing a plural in the middle of an expression where it doesn't work.

The database vendors themselves don't seem to be consistent on this issue.

* PostgreSQL system table names are singular e.g. "pg_class"

* Oracle system tables are plural e.g. "ALL_TABLES"

* MySQL is also plural e.g. "schemata".

Does this qualify as bike shedding? What is the business impact either way? If someone prefers one or the other, so what!

All arguments seem pretty frail, always reduced to some form of "well this way makes it easier for ME to reason about!". I used to have opinions on this crap, and nowadays I've got way bigger fish to fry than worrying about table name plurality. As long as it's human-legible and consistent, who cares?

>As long as it's [..] consistent, who cares?

That's what the article is about..

It is clearly not, and that's the whole reason people have an issue with it.

The only mention of the word "consistency" was used to support the plural approach.

+1000

Use whatever convention (per app/db/team), as long as it is consistent. Developers who use mixed singular and plural in the same db should be fired (into the sun).

Good point. I am going to make a local form that will check a name I want to use for being a poor choice in rails.

Seriously? I don't care what you call your tables, as long as it's consistent and it's nothing egregious.

Sure, unless most the team is international, and the referenced "quote" was actually meant to function as "quota"... you just haven't earned your grey beard till you spent days chasing down a developer to explain their code.

Nice guy, but a conditional recursively defined polymorphic class on a transaction server should be a crime. lol =3

user is a resevered word in Postgres so it will be actually

> select * from "user"

which is ugly

Not really related, but I hate that postgres have keyword `user` which makes it cumbersome to name table like that (you technically can do it, but you would need to quote it everywhere). This table is present in 99% projects, so it's PITA. I'm usually using "account" table because of that, but "user" feels more natural.