Table Naming Dilemma: Singular vs. Plural Names

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.

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.

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.

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 am of the firm belief that in an Entity Relation Diagram, the entity should be reflected with a singular name, similar to a class name being singular. Once instantiated, the name reflects its instance. So with databases, the entity when made into a table (a collection of entities or records) is plural. Entity, User is made into table Users. I would agree with others who suggested maybe the name User could be improved to Employee or something more applicable to your scenario.

This then makes more sense in a SQL statement because you are selecting from a group of records and if the table name is singular, it doesn't read well.

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.

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.

Singular. I don't buy any argument involving which is most logical - every person thinks his own preference is most logical. No matter what you do it is a mess, just pick a convention and stick to it. We are trying to map a language with highly irregular grammar and semantics (normal spoken and written language) to a highly regular (SQL) grammar with very specific semantics.

My main argument is that I don't think of the tables as a set but as relations.

So, the AppUser relation tells which entities are AppUsers.

The AppUserGroup relation tells me which entities are AppUserGroups

The AppUser_AppUserGroup relation tells me how the AppUsers and AppUserGroups are related.

The AppUserGroup_AppUserGroup relation tells me how AppUserGroups and AppUserGroups are related (i.e. groups member of groups).

In other words, when I think about entities and how they are related I think of relations in singular, but of course, when I think of the entities in collections or sets, the collections or sets are plural.

In my code, then, and in the database schema, I use singular. In textual descriptions, I end up using plural for increased readability - then use fonts etc. to distinguish the table/relation name from the plural s.

I like to think of it as messy, but systematic - and this way there is always a systematically generated name for the relation I wish to express, which to me is very important.

I also would go with plurals, and with the aforementioned Users dilemma, we do take the square bracketing approach.

We do this to provide uniformity between both database architecture and application architecture, with the underlying understanding that the Users table is a collection of User values as much as a Users collection in a code artifact is a collection of User objects.

Having our data team and our developers speaking the same conceptual language (although not always the same object names) makes it easier to convey ideas between them.

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.

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.

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.

The system tables/views of the server itself (SYSCAT.TABLES, dbo.sysindexes, ALL_TABLES, information_schema.columns, etc.) are almost always plural. I guess for the sake of consistency I'd follow their lead.

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.

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.

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

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.

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

I will just give my opinion why I use singular names.

For example, I need to get all the fields from an user:

-- Select every fields from 'user' table
SELECT * FROM user

I need the name of the user that is 21 years old:

-- Select every fields from 'user' table which have 21 years old
SELECT * FROM user WHERE age = '21'

Of course the plural way can be used by the same means, but for my brain to read, I really think that's the right way to go.

If you go there will be trouble, but if you stay it will be double.

I'd much rather go against some supposed non-plurals naming convention than name my table after something which might be a reserved word.

There is no "convention" that requires table names to be singular.

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

About the other problem (quotes) it depends on the SQL dialect. Oracle doesn't require quotes around table names.

A TABLE name is ONE definition of a table structure. A VIEW or QUERY name is ONE definition of a view or query of (a or many) tables. A TABLE, VIEW or QUERY may contain one of the following:

0 records 1 record Many records.

Why on earth would you want to put an 's' on the end of a single object name? What do you want to signify by placing this 's' on the end of an object name?

If you want to differentiate, then add '_tbl'. A View is '_vew' (not the silly '_v' convention).

Minimum 3 character suffixing - that stops this discussion dead.

A table is a DB object - no different to any other.

The saving of 3 characters saves nothing except clarity of meaning.

Red ;-)

If you use certain frameworks like Zend Framework (PHP) it is only wise to use plural for table classes and singular for row classes.

So say you create a table object $users = new Users() and have declared the row class to be User you will be able to call new User() as well.

Now if you use singular for table names you would have to do something like new UserTable() with the row being new UserRow(). This looks more clumsy to me than just having an object Users() for the table and User() objects for the rows.

I solved the same problem by naming the table "Employee" (actually "Employees"). I try to stay as far away as possible from any conflict with possibly reserved words. Even "Users" is uncomfortably close for me.