Table Naming Dilemma: Singular Vs. Plural Names

Table Naming Dilemma: Singular vs. Plural Names

Others have given pretty good answers as far as "standards" go, but I just wanted to add this... Is it possible that "User" (or "Users") is not actually a full description of the data held in the table? Not that you should get too crazy with table names and specificity, but perhaps something like "Widget_Users" (where "Widget" is the name of your application or website) would be more appropriate.

Table Naming Dilemma: Singular vs. Plural Names

Others have given pretty good answers as far as "standards" go, but I just wanted to add this... Is it possible that "User" (or "Users") is not actually a full description of the data held in the table? Not that you should get too crazy with table names and specificity, but perhaps something like "Widget_Users" (where "Widget" is the name of your application or website) would be more appropriate.

Database tables naming, plural or singular

This question calls for a religious war.

I have no doubt it should be plural because...

• A table is a collection of rows.
• The SQL syntax becomes more natural - SELECT * FROM Customers instead of SELECT * FROM Customer.
• The analogy to OOP - you have a class Customer and a list or other collection of customers called Customers.
• SELECT * FROM Customers AS Customer WHERE Customer.FirstName = 'John' - Customers refers to the whole table while Customer refers to the current row.

Negative things

One has to switch several times between singular and plural during the development. You may start with a conceptual model - for example an entity relationship model - where the natural choice is to name the entity Customer. From this model you generate a database and must pluralize the name to get the Customers table. Finally you pick your favourit O/R mapper and it has to singularize the name again to get a class named Customer.

If you have to do this manually because the tool is lacking support (for example EntityFramework prior to .NET 4.0) it might be a reasonable choice to keep the table names singular but therfore get a class Customer instead of Customers without changing it by hand.

Singular or plural database table names?

IMHO, Table names should be plural like Customers.

Class names should be singular like Customer if it maps to a row in Customers table.

Should I use Singular or Plural name convention for REST resources?

The premise of using /resources is that it is representing "all" resources. If you do a GET /resources, you will likely return the entire collection. By POSTing to /resources, you are adding to the collection.

However, the individual resources are available at /resource. If you do a GET /resource, you will likely error, as this request doesn't make any sense, whereas /resource/123 makes perfect sense.

Using /resource instead of /resources is similar to how you would do this if you were working with, say, a file system and a collection of files and /resource is the "directory" with the individual 123, 456 files in it.

Neither way is right or wrong, go with what you like best.

Data model. ¿Pluralize or not pluralize the names of the tables?

I guess it all depends on what you are actually going to use the database for:

I do not pluralize, because with this data model, I then generated entities for ORM and it seems more correct to instantiate a new 'User' that a new 'Users'.

But you don't give much information regarding which language you use besides sql, which framework, which tools.

I usually say that the best is to conform to standards of the framework or ORM, for instance in .NET Entity Framework the tables are generated with a singular notation and class names that map to these tables are singular as well, whereas in Ruby on Rails ActiveRecord they are generated pluralized in the database but mapping classes are singular.

There is no better way I think but, if the data for your application is ever going to be more important than the application itself (as in, we may migrate from that framework or language eventually to something completely different) or if there are going to be others working directly on the database (people doing reports, DBA's, analysts etc.) then I'd suggest you make the tables plural as database people are more keen to use it that way.

If you think at the database level pluralizing makes sense, a users table is probably going to have more than one user, and so on and so forth. Why would you want to store data in a table fashion if you weren't going to store more than one record with the same structure? Than it makes sense to pluralize.

If you create an Array type of data in X language, let's say, for users, you don't call it useryou call it users probably, because it may contain more than one, even if you only store one thing on it initially.

So that would be my reasoning behind why pluralize a table name is a good approach, besides, you can almost always setup your mappings in the ORM if pluralizing breaks the convention so that you can set that the User class maps to the users table for instance.

Update: here is another good discussion on StackExchange's DBA where they make a point regarding joint associations

appropriate model name and db table plural naming convention

I think that table names should not be in plural. That said and since your db will contain students too, Person I think is the best option. Imho table/schema definition is more important than table names. In the end the table name doesn't matter that much.

Is there a naming convention for MySQL?

I would say that first and foremost: be consistent.

I reckon you are almost there with the conventions that you have outlined in your question. A couple of comments though:

Points 1 and 2 are good I reckon.

Point 3 - sadly this is not always possible. Think about how you would cope with a single table foo_bar that has columns foo_id and another_foo_id both of which reference the foo table foo_id column. You might want to consider how to deal with this. This is a bit of a corner case though!

Point 4 - Similar to Point 3. You may want to introduce a number at the end of the foreign key name to cater for having more than one referencing column.

Point 5 - I would avoid this. It provides you with little and will become a headache when you want to add or remove columns from a table at a later date.

Some other points are:

Index Naming Conventions

You may wish to introduce a naming convention for indexes - this will be a great help for any database metadata work that you might want to carry out. For example you might just want to call an index foo_bar_idx1 or foo_idx1 - totally up to you but worth considering.

Singular vs Plural Column Names

It might be a good idea to address the thorny issue of plural vs single in your column names as well as your table name(s). This subject often causes big debates in the DB community. I would stick with singular forms for both table names and columns. There. I've said it.

The main thing here is of course consistency!

---

Related Topics