×

Error message

  • Deprecated function: Methods with the same name as their class will not be constructors in a future version of PHP; views_display has a deprecated constructor in require_once() (line 3161 of /home/gleamly/public_html/includes/bootstrap.inc).
  • Deprecated function: Methods with the same name as their class will not be constructors in a future version of PHP; views_many_to_one_helper has a deprecated constructor in require_once() (line 127 of /home/gleamly/public_html/sites/all/modules/ctools/ctools.module).

Should we use Singular or Plural Database Table Names

I have been in a Dilemma about this for a while. After reading several posts on Singular vs Plural Database table names, I realized that both have its advantages and disadvantages, and that everyone has their own opinion on this, and that the most important thing is that your naming conventions are consistent across the database since it would be annoying to remember which table names are plural and which are singular. Here are a few things to consider when choosing your naming convention.

Reasons to use Plural Names

Several reasons support the use of plural database table names

Tables represent the instances of an entity

Your table stores many instances of an entity (Say User), Since the table is storing multiple instances of "User", it would be better to name make your table name a plural word. This would also help remove problems with mixing the table names with the entity it represents. This will also help remove ambiguities from code in that, Class names like Customer will map to one row in the Customers table.

SQL sounds more English-like

Plurals make SQL sound more like English when spoken aloud. Therefore the table name may explain the table contents better.
  • Table Name: CustomerPayment - Sounds like we are storing in Payment Information from one customer here.
  • Table Name: CustomersPayments - Understood that we are storing information about many Customers Payments here.

Working with others

If we are working with others on a project, Using Plural names would be better since everyone would understand what the Table is about; whether this developer is accustomed to using Singular or Plural.
  • CustomerPayment - for a developer who is accustomed to using Plural table names, he may think this table stores information about the company's one customer.
  • CustomersPayments - even if a developer is accustomed to using Singular table names, and he looks at this table, he can understand what data is stored here.

Grammar

Grammar Or "Grammatics" if you please. Writing a query with tables having singular names would be grammatically incorrect:
  • Select * from Customer...
This may seem easy to read, but think of very large queries, if a large bit of writing is different from what we are accustomed to, it will get confusing. And debugging large grammatically incorrect queries may be a tedious task.

Reasons why you may use Singular Names

As with the plural database table naming convention, several strong reasons support the use of singular names.

Concept

You can think of bag containing apples like "AppleBag", it doesn't matter if contains 0, 1 or a million apples, it is always the same bag. Tables are just that, containers, the table name must describe what it contains, no how much data it contains.

Convenience

It is easier come out with singular names, than with plural ones. Objects can have irregular plurals or not plural at all, but will always have a singular one (with few exceptions like News).
  • Customer
  • Order
  • User
  • Status
  • News
Some may argue that exceptions such as "news" may cause confusion in the naming convention and make other developers think that the database uses a combination of both singular and plural names.

Aesthetic and Order

Specially in master-detail scenarios, this reads better, aligns better by name, and have more logical order (Master first, Detail second):
  • Order
  • OrderDetail
Compared to:
  • OrderDetails
  • Orders

Simplicity

Put all together, Table Names, Primary Keys, Relationships, Entity Classes... is better to be aware of only one name (singular) instead of two (singular class, plural table, singular field, singular-plural master-detail...)
  • Customer
  • Customer.CustomerID
  • CustomerAddress
  • public Class Customer {...}
  • SELECT FROM Customer WHERE CustomerID = 100
Once you know you dealing with "Customer", you can be sure you will use the same word for all your database interaction needs.

Globalization

The world is getting smaller, you may have a team of different nationalities, not everybody has English as native language. Would be easier for a non-native English language programmer to think of "Repository" than of "Repositories", or avoid them type "Statuses" instead of "Status". Having singular names can lead to less errors caused by typos, save time by avoid spending extra seconds to think "is it Child or Children?", hence improving productivity.

Why not?

It can even save you writing time, save you disk space, and even make your computer keywords last more! SELECT Customer.CustomerName FROM Customer WHERE Customer.CustomerID = 100 SELECT Customers.CustomerName FROM Customers WHERE Customers.CustomerID = 100 You have saved 3 letters, 3 bytes, 3 extra keyword hits :)

Reserved Keywords

You can name those ones messing up with reserved names like: User > LoginUser, AppUser, SystemUser, CMSUser,... Or use the infamous squared brackets [User]

Multiple rows in a table is obvious

Since everyone knows that a database stores more than one row, using plural database table name is redundant, unless you're only planning for the table to contain one row.

Conclusions

I'd say that plural database names are easier to read and Singular database names are better for mapping. Using Plural or Singular names for database tables is up to the developer(s) of the project and which they feel comfortable with.

Opinions of other Developers

I don't see the difficulty of creating it or envisioning it with plural or singular table names. How hard is to to say "what aspects do users have" vs "what aspect does a user have"?
To think either singular or plural is wrong I think. The correct way is surely to use both. If we have a table full of thousands of customers then surely the table name "Customers" is the way to go. The Customers table would just be ID, Name, etc, more details on the customer would be stored in a "Customer" table. Much the same as in a common OO model.
I, too, firmly believe that this issue is simply religious, but other developers - both at my place of employment and in the developer's community at large - disagree.