sql - Table Naming Dilemma: Singular vs. Plural Names

ID : 656

viewed : 119

Tags : sqlsql-servernaming-conventionssql

Top 5 Answer for sql - Table Naming Dilemma: Singular vs. Plural Names

vote vote

91

I had same question, and after reading all answers here I definitely stay with SINGULAR, reasons:

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, not how much data it contains. Additionally, the plural concept is more about a spoken language one (actually to determine whether there is one or more).

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 are dealing with "Customer", you can be sure you will use the same word for all of 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 a native language. It would be easier for a non-native English language programmer to think of "Repository" than of "Repositories", or "Status" instead of "Statuses". Having singular names can lead to fewer errors caused by typos, save time by not having 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 keyboard last longer!

  • SELECT Customer.CustomerName FROM Customer WHERE Customer.CustomerID = 100
  • SELECT Customers.CustomerName FROM Customers WHERE Customers.CustomerID = 103

You have saved 3 letters, 3 bytes, 3 extra keyboard hits :)

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

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

Or use the infamous square brackets [User]

vote vote

85

If you use Object Relational Mapping tools or will in the future I suggest Singular.

Some tools like LLBLGen can automatically correct plural names like Users to User without changing the table name itself. Why does this matter? Because when it's mapped you want it to look like User.Name instead of Users.Name or worse from some of my old databases tables naming tblUsers.strName which is just confusing in code.

My new rule of thumb is to judge how it will look once it's been converted into an object.

one table I've found that does not fit the new naming I use is UsersInRoles. But there will always be those few exceptions and even in this case it looks fine as UsersInRoles.Username.

vote vote

75

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.

vote vote

61

I prefer to use the uninflected noun, which in English happens to be singular.

Inflecting the number of the table name causes orthographic problems (as many of the other answers show), but choosing to do so because tables usually contain multiple rows is also semantically full of holes. This is more obvious if we consider a language that inflects nouns based on case (as most do):

Since we're usually doing something with the rows, why not put the name in the accusative case? If we have a table that we write to more than we read, why not put the name in dative? It's a table of something, why not use the genitive? We wouldn't do this, because the table is defined as an abstract container that exists regardless of its state or usage. Inflecting the noun without a precise and absolute semantic reason is babbling.

Using the uninflected noun is simple, logical, regular and language-independent.

vote vote

57

What convention requires that tables have singular names? I always thought it was plural names.

A user is added to the Users table.

This site agrees:
http://vyaskn.tripod.com/object_naming.htm#Tables

This site disagrees (but I disagree with it):
http://justinsomnia.org/writings/naming_conventions.html


As others have mentioned: these are just guidelines. Pick a convention that works for you and your company/project and stick with it. Switching between singular and plural or sometimes abbreviating words and sometimes not is much more aggravating.

Top 3 video Explaining sql - Table Naming Dilemma: Singular vs. Plural Names

Related QUESTION?