sql - when - why table names should be singular




Table Naming Dilemma: Singular vs. Plural Names (20)

As others have mentioned here, conventions should be a tool for adding to the ease of use and readability. Not as a shackle or a club to torture developers.

That said, my personal preference is to use singular names for both tables and columns. This probably comes from my programming background. Class names are generally singular unless they are some sort of collection. In my mind I am storing or reading individual records in the table in question, so singular makes sense to me.

This practice also allows me to reserve plural table names for those that store many-to-many relationships between my objects.

I try to avoid reserved words in my table and column names, as well. In the case in question here it makes more sense to go counter to the singular convention for Users to avoid the need to encapsulate a table that uses the reserved word of User.

I like using prefixes in a limited manner (tbl for table names, sp_ for proc names, etc), though many believe this adds clutter. I also prefer CamelBack names to underscores because I always end up hitting the + instead of _ when typing the name. Many others disagree.

Here is another good link for naming convention guidelines: http://www.xaprb.com/blog/2008/10/26/the-power-of-a-good-sql-naming-convention/

Remember that the most important factor in your convention is that it make sense to the people interacting with the database in question. There is no "One Ring to Rule Them All" when it comes to naming conventions.

Academia has it that table names should be the singular of the entity that they store attributes of.

I dislike any T-SQL that requires square brackets around names, but I have renamed a Users table to the singular, forever sentencing those using the table to sometimes have to use brackets.

My gut feel is that it is more correct to stay with the singular, but my gut feel is also that brackets indicate undesirables like column names with spaces in them etc.

Should I stay, or should I go?


How about this as a simple example:

SELECT Customer.Name, Customer.Address FROM Customer WHERE Customer.Name > "def"

vs.

SELECT Customers.Name, Customers.Address FROM Customers WHERE Customers.Name > "def"

The SQL in the latter is stranger sounding than the former.

I vote for singular.


I always thought that was a dumb convention. I use plural table names.

(I believe the rational behind that policy is that it make it easier for ORM code generators to produce object & collection classes, since it is easier to produce a plural name from a singular name than vice-versa)


I am a fan of singular table names as they make my ER diagrams using CASE syntax easier to read, but by reading these responses I'm getting the feeling it never caught on very well? I personally love it. There is a good overview with examples of how readable your models can be when you use singular table names, add action verbs to your relationships and form good sentences for every relationships. It's all a bit of overkill for a 20 table database but if you have a DB with hundreds of tables and a complex design how will your developers ever understand it without a good readable diagram?

http://www.aisintl.com/case/method.html

As for prefixing tables and views I absolutely hate that practice. Give a person no information at all before giving them possibly bad information. Anyone browsing a db for objects can quite easily tell a table from a view, but if I have a table named tblUsers that for some reason I decide to restructure in the future into two tables, with a view unifying them to keep from breaking old code I now have a view named tblUsers. At this point I am left with two unappealing options, leave a view named with a tbl prefix which may confuse some developers, or force another layer, either middle tier or application to be rewritten to reference my new structure or name viewUsers. That negates a large part of the value of views IMHO.


I don't like plural table names because some nouns in English are not countable (water, soup, cash) or the meaning changes when you make it countable (chicken vs a chicken; meat vs bird). I also dislike using abbreviations for table name or column name because doing so adds extra slope to the already steep learning curve.

Ironically, I might make User an exception and call it Users because of USER (Transac-SQL), because I too don't like using brackets around tables if I don't have to.

I also like to name all the ID columns as Id, not ChickenId or ChickensId (what do plural guys do about this?).

All this is because I don't have proper respect for the database systems, I am just reapplying one-trick-pony knowledge from OO naming conventions like Java's out of habit and laziness. I wish there were better IDE support for complicated SQL.


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 "Statuses" instead of "Status". 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 = 100

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]


I only use nouns for my table names that are spelled the same, whether singular or plural:

moose fish deer aircraft you pants shorts eyeglasses scissors species offspring


I personaly prefer to use plural names to represent a set, it just "sounds" better to my relational mind.

At this exact moment i am using singular names to define a data model for my company, because most of the people at work feel more confortable with it. Sometimes you just have to make life easier to everyone instead of imposing your personal preferences. (that's how i ended up in this thread, to get a confirmation on what should be the "best practice" for naming tables)

After reading all the arguing in this thread, i reached one conclusion:

I like my pancakes with honey, no matter what everybody's favorite flavour is. But if i am cooking for other people, i will try to serve them something they like.


I stick with singular for table names and any programming entity.

The reason? The fact that there are irregular plurals in English like mouse ⇒ mice and sheep ⇒ sheep. Then, if I need a collection, i just use mouses or sheeps, and move on.

It really helps the plurality stand out, and I can easily and programatically determine what the collection of things would look like.

So, my rule is: everything is singular, every collection of things is singular with an s appended. Helps with ORMs too.


I think using the singular is what we were taught in university. But at the same time you could argue that unlike in object oriented programming, a table is not an instance of its records.

I think I'm tipping in favour of the singular at the moment because of plural irregularities in English. In German it's even worse due to no consistent plural forms - sometimes you cannot tell if a word is plural or not without the specifying article in front of it (der/die/das). And in Chinese languages there are no plural forms anyway.


I've always used singular simply because that's what I was taught. However, while creating a new schema recently, for the first time in a long time, I actively decided to maintain this convention simply because... it's shorter. Adding an 's' to the end of every table name seems as useless to me as adding 'tbl_' in front of every one.


IMHO, table names should be plural like Customers.

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


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.


My take is in semantics depending on how you define your container. For example, A "bag of apples" or simply "apples" or an "apple bag" or "apple".

Example: a "college" table can contain 0 or more colleges a table of "colleges" can contain 0 or more collegues

a "student" table can contain 0 or more students 
a table of "students" can contain 0 or more students.

My conclusion is that either is fine but you have to define how you (or people interacting with it) are going to approach when referring to the tables; "a x table" or a "table of xs"


Possible alternatives:

  • Rename the table SystemUser
  • Use brackets
  • Keep the plural table names.

IMO using brackets is technically the safest approach, though it is a bit cumbersome. IMO it's 6 of one, half-a-dozen of the other, and your solution really just boils down to personal/team preference.


Singular. I'd call an array containing a bunch of user row representation objects 'users', but the table is 'the user table'. Thinking of the table as being nothing but the set of the rows it contains is wrong, IMO; the table is the metadata, and the set of rows is hierarchically attached to the table, it is not the table itself.

I use ORMs all the time, of course, and it helps that ORM code written with plural table names looks stupid.


Tables: plural

Multiple users are listed in the users table.

Models: singular

A singular user can be selected from the users table.

Controllers: plural

http://myapp.com/users would list multiple users.

That's my take on it anyway.


The SQL definition of a table is in actuality the definition of one potential row of the table, not the collection. Therefore, the name used in that definition must designate the type of the row, not the name of the collection. People who prefer plural because it reads well in their English statements need to start thinking more logically and look at all of the logic and programming code that is involved with actually using a table. There are several very good reasons mentioned in these comments to use singular table names. These include very good reasons NOT to use plural table names. "Reading well" should not be any reason at all, especially since some may read the idea differently.


This may be a bit redundant, but I would suggest being cautious. Not necessarily that it's a bad thing to rename tables, but standardization is just that; a standard -- this database may already be "standardized", however badly :) -- I would suggest consistency to be a better goal given that this database already exists and presumably it consists of more than just 2 tables.

Unless you can standardize the entire database, or at least are planning to work towards that end, I suspect that table names are just the tip of the iceberg and concentrating on the task at hand, enduring the pain of poorly named objects, may be in your best interest --

Practical consistency sometimes is the best standard... :)

my2cents ---


We run similar standards, when scripting we demand [ ] around names, and where appropriate schema qualifiers - primarily it hedges your bets against future name grabs by the SQL syntax.

SELECT [Name] FROM [dbo].[Customer] WHERE [Location] = 'WA'

This has saved our souls in the past - some of our database systems have run 10+ years from SQL 6.0 through SQL 2005 - way past their intended lifespans.







naming-conventions