Click here to Skip to main content
Click here to Skip to main content

Should we use Singular or Plural Database Table Names

By , 2 Jan 2012
Rate this:
Please Sign up or sign in to vote.

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 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 a 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, not how much data it contains.

Reason 2. (Convenience) It is easier to come up with singular names, than with plural ones. Objects can have irregular plurals or no 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 has 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 are 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. It 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. Smile | :)

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

Related Content

References 

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

JoshuaKissoon
Student Codestorm Guyana
Guyana Guyana
Currently pursuing my M.Tech in Information Security at NIT Warangal, India.
Follow on   Google+

Comments and Discussions

 
QuestionA table name is actually the name of the definition of the type of record that it contains. PinmemberBruce Patin7-Feb-13 8:51 
QuestionGrammar against plurals Pinmembersweavo30-Jan-12 5:30 
GeneralSingular/Plural table names Pinmembernoel.carey10-Jan-12 4:02 
SuggestionReusing an argument for the side of singular PinmemberRobert S Sharp4-Jan-12 6:19 
QuestionMy usual solution PinmemberLockwood3-Jan-12 4:00 
QuestionInteresting PinmemberNick Leaton3-Jan-12 2:14 
QuestionORM PinmemberSarafian3-Jan-12 1:19 
BugOne thing... PinmemberThomas Giles3-Jan-12 0:54 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140421.2 | Last Updated 3 Jan 2012
Article Copyright 2012 by JoshuaKissoon
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid