Table Naming Dilemma: Singular vs. Plural Names

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?

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)

Guidelines are really there as just that. It's not "set in stone" that's why you have the option of being able to ignore them.

I would say that it's more logically intuitive to have pluralized table names. A table is a collection of entity after all. In addition to other alternatives mentioned I commonly see prefixes on table names...

- tblUser
- tblThis
- tblThat
- tblTheOther

I'm not suggesting this is the way to go, I also see spaces used a LOT in table names which I abhor. I've even come across field names with idiotic characters like ? as if to say this field answers a question.

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

There is no "convention" that requires table names to be singular.<br/><br/>
For example, we had a table called "REJECTS" on a db used by a rating process, containing the records rejected from one run of the program, and I don't see any reason in not using plural for that table (naming it "REJECT" would have been just funny, or too optimistic).
<br/><br/>
About the other problem (quotes) it depends on the SQL dialect. Oracle doesn't require quotes around table names.

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:

[To see links please register here]

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.

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.

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)][1], 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][2] out of habit and laziness. I wish there were better IDE support for complicated SQL.

[1]:

[To see links please register here]

[2]:

[To see links please register here]

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:

[To see links please register here]

This site disagrees (but I disagree with it):

[To see links please register here]

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

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.