Should we use Singular or Plural Database Table Names

JoshuaKissoon's picture

Well, I have been in a Dilemma about this for a while. After reading several posts on Singular vs Plural Database table names, I finally concluded 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. I can therefore conclude that naming your database tables with Singular or Plural names is up to the developer. However, here are a few things to consider when choosing your naming convention:

Reasons why you may use Plural Names:

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

Reason 2. Plurals because they 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.

Reason 3: (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.

Reason 4: (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:

Reason 1 (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.

Reason 2. (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

Reason 3. (Aesthetic and Order). Specially in master-detail scenarios, this reads better, aligns better by name, and have more logical order (Master first, Detail second):

  • 1.Order
  • 2.OrderDetail

Compared to:

  • 1.OrderDetails
  • 2.Orders

Reason 4 (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.

Reason 5. (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.

Reason 6. (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 :)

Reason 7: And finally, you can name those ones messing up with reserved names like:

  • User > LoginUser, AppUser, SystemUser, CMSUser,...

            Or use the infamous squared brackets [User]

Reason 8: 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:

            So from all of this we can conclude that plural database names are easier to read and Singular database names are better for mapping. I can now safely say that 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.

 

References: 
http://en.wikipedia.org/wiki/ISO/IEC_11179
http://stackoverflow.com/q/338156/802742
http://vyaskn.tripod.com/object_naming.htm#Tables
http://justinsomnia.org/2003/04/essential-database-naming-conventions-and-style/
http://discuss.fogcreek.com/joelonsoftware/default.asp?cmd=show&ixPost=5904
http://discuss.fogcreek.com/joelonsoftware/default.asp?cmd=show&ixPost=5904