Use singular nouns for database table names (teamten.com)

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

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

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?

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

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.

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.

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 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.

Sounds like bike shedding to me

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)

No.

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.

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.

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

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.)

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.

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".

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.

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 :)

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

> select * from "user"

which is ugly

I like singular, most pick plural

I like tabs, most pick spaces

I like mercurial, most pick git

But no one’s taking my vim!