Use singular nouns for database table names

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

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;

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.

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.

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)

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

Sounds like bike shedding to me

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

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.

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.