Oct 14

It can be common for developers to be involved in the creation and design of databases for an application they are developing. Since most developers aren’t trained as Database Administrators, it is important that they develop some sound approaches to writing SQL and Data Definition Language (DDL). I’ve previously reviewed Database in Depth: Relational Theory for Practitioners by C. J. Date in the post, Book Review: Database in Depth. I highly recommend getting it as a guide to best practices for any relational database work.

Singular or Plural

One of the choices that invariably occurs is what to use for table names. Sometimes the particular RDMS may restrict the choices, but often this is pretty wide open. What I’m interested in is the small decision of whether to use the singular or plural form of a noun for the name. A common example is the table where user data is held. Should the table be named user or users?

My Choice

I am not sure there is a standard answer. This has been discussed before (for example, see Database table naming conventions), but I haven’t seen anyone justify why it is better to make the choice for theoretical reasons. Some frameworks or DDL tools will make the choice for you as I discuss below for Ruby on Rails. My own choice has been to use the singular name. I think this leads to improved code quality because:

  1. It leads to consistency in Foreign key names (order.user_ID references user.ID).
  2. It makes ORM a 1:1 match (the user object reflects a row in the user table).
  3. It avoids some of the annoyance of the English language pluralization rules (see Rails below).
  4. Then there are small things like ordering of tables. For example, user, users, userOrder, and user_line_order may not sort in the most effective way.
  5. I believe it is the simplest design that works. Using the plural form makes me think there is a singular table from which the plural form is distinguished. Would that be a table which is guaranteed to only have a single row?

Rails makes a default choice?

So what happens when you use the command script/generator scaffold user in a Rails project. Rails generates a DB migration for creating a users table. There are ways to control the model names and DB migration, but it is interesting that the developers of Rails decided to make the default choice of using plural table names. (See Agile Web Development with Rails for example syntax on how to not use defaults).

But what about that tricky English language? Well, script/generate scaffold person produces people. Pretty cool. And woman produces women. But then goose becomes gooses. Woops! Now you may have no need for a goose object in your application, but I am sure there are other examples like this. Alas, this appears to be a bug in the Rails framework, but just think how easy it is for you to bump into this yourself when trying to remember the plural form of the object you are working with. In addition, these names show up in the URL for Rails routing. Thus they can become public. I’d rather see geese in applications I work on.

I realize there are pros and cons to the choice of using singular or plural names. In terms of producing quality code, what do you use and why?

One Response to “DB Table Naming Convention”

  1. J says:

    Small point against: a users collection object would reflect the rows in a users table, and a user object is one of those rows.

    Not sure if this is a point against or not, but in your foreign key example I would have written “order.user references users”

    Perhaps the reason that there’s anything to discuss about singular/plural is that RDBs are expecting to always deal with sets of rows, never with rows on their own. Programming languages are generally the other way round.

Leave a Reply

preload preload preload