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

+45  A: 

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:

This site disagrees (but I disagree with it):

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.

Michael Haren
When applying set theory to tables, any instance in the set is representative of the set, so Apple is an Apple set, it is agnostic of how many apples are in the set - it is an Apple with many instances. A 'bag' of apples doesn't become a 'bags' when it contains many apples.
A relation consists of a heading and a body. A heading is a set of attributes. A body (of an n-ary relation) is a set of n-tuples. The heading of the relation is also the heading of each of its tuples.[]
What if you have a bag with 5 apples inside? Do you call it a bag of apple? or a bag of apples?
Christopher Mahan
I think the theory would be that the set is named Apples. A singular apple is still "a set of Apples" - albeit, a set with a single instance. Multiple apples are also a "set of Apples".
Mark Brackett
How would you define an array of Apple objects in your code?`Apple apples[100]` or `Apple apple[100]`
@Christopher, if the raison d'être of the bag is to hold apples and only apples, then it is an "apple bag" , regardless of whether it contains 1 apple, 100 apples or no apples.
Ian Mackinnon
@ Ian: That's because a table is generic, and can be compared to a shipping container (can contain nearly anything, from apple crates to crates of Harley Davidson motorcycles). You say: a cargo container of oranges, not an orange cargo container. You say: a cargo container of car parts, not a car parts cargo container. If you made a custom data structure meant to hold only a specific type of data, such as names of apples, and you named it "kungabogo", then you could have an apple kungaboko. I know what your thinking, but think first of a sac of balls, and understand the difference in meaning.
Christopher Mahan
@Christopher, a specific database table is designed to hold one type of thing only, forever, and to be associated with this type even when empty, so a shipping container is an inadequate metaphor. They are also abstract concepts, so it's unlikely anyone will confuse them with a scrotum.
Ian Mackinnon
@Ian: Ok, I agree with you. Tables as implemented cannot hold anything except the thing they are meant to hold. Except of course that they can hold many different kinds of things. Red shirts, blue shirts, soft shirts, short shirts, etc. Shirts with sequins. They all fall into the shirt table. Shirts on sale, shirts on order, defective shirts, shirts being designed, shirts in wrappers, shirts being worn, shirts destroyed, shirts in africa, shirts in pakistan, shirts on the ship, shirts in the box, shirts on the shelf, shirts on the floor. All shirts. In the shirt table. Makes sense. Not.
Christopher Mahan
+4  A: 

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.

Dave Markle
I like your 'prefix' idea, but would call it SystemUser.
I would like to vote up the previous comment. :)
Edited accordingly. Thanks!
Dave Markle
+12  A: 

Another vote for plurals.

I've seen some people use singular, but to me it only matters that it is consistent across the database.

I think this answer should really be a comment.
Mark Rogers
+2  A: 

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)

James Curran
This convention has been part of relational theory long, long, before ORM ever existed.
+3  A: 

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.

Agreed. Spaces and prefixes are from the Devil.
Dave Markle
MSAccess encourages table names with spaces. I suspect many MSSQL tables in spaces were imported from there.
James Curran
+1 for tbl-prefixes being hellspawn
Matt Rogish
+1 again. Tables and Fields named with spaces are the mark of the whizz kid Office Junior making a real kool access application for Beryl in account :-).
Hey, I like Beryl in accounts... but it wouldn't ever cause me to put prefixes or spaces in my table names... nor would it cause me to put question marks or exclamation marks in my field names. I don't care how cute she is. :P
tbl as a prefix is the son of devilspawn and smelly carrion, but semantic prefixes are a little better. In our main application, we, Chase Software, prefix all our tables with 'ca' or 'cs', being chase application, or chase system.
(continued) 'ca' tables are business tables and change regularly in day to day use, such as caDocument, which is invoices, PO's, cost sheets, etc. 'cs' tables are more for navigation etc. such as csForm, which we use to navigate between forms (pages), and is normally only changed by us.
+1  A: 

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.

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.

Sql Server only requires braces for names that are reserved keywords ("User" is one). I believe Oracle has the same policy
+4  A: 

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:

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.

Ignoring the horrors of hungarian notation.Never, never, never use sp_ in front of stored procedures because MS-SQL uses that for system stored procedures and treats them special.Since sp_ are stored in the master table, MS-SQL always looks thier first even if you qualify the location.
Will Dieterich
+8  A: 

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.

+4  A: 

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.

Us plural guys either name the 'id' column 'id' like you do, or 'singular_id'. I believe tables should be plural (think of them like arrays), but column names should be singular (attributes of a single element).
+13  A: 

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.

Tom H.
I agree. OrgUsers, AppUsers, anything to avoid using a keyword.

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.

le dorfier
Challenge comes that you're only aware of reserved words at the point you write you application - this pool of words will drift over time as the language is enhanced. Qualifying a word as a name with square paren is the only way to salve that pain.
At the expense of readability, which is problematic enough in sql statements.
le dorfier
Totally agree - CAPS for keywords, Camelcase etc. for names helps improve that a little but still it reduces readability.
Employees may not always be users.
Nope, so for those cases it wouldn't be appropriate. But for other cases, maybe there's another word (like "members", "people", or such that would enable you to avoid "Users" and not be as likely to collide with something.
le dorfier

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.

This is not true. Zend_Db does not impose a naming convention on database table names, table class names, or row class names. I removed that ill-conceived inflection code myself.
Bill Karwin
But I do support the convention of table class names being plural, and row class names being singular. It's just that the Zend_Db framework doesn't enforce any convention.
Bill Karwin
just revisited my post and corrected it. it's true that it doesn't impose anything. it just suggests it.

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.

Patrick Harrington

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 also would go with plurals, and with the aforementioned Users dilema, 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.

What's up with the downvotes? Comments would be nice. Downvotes without discussion implies that the downvoters are actually downvoting a subjective answer to a subjective topic just based on the fact that he/she disagrees.
+25  A: 

In his book "SQL Programming Style," Joe Celko suggests that a collection (e.g. a table) should be named in the plural, while a scalar data element (e.g. a column) should be named in the singular.

He cites ISO-11179-4 as a standard for metadata naming, which supports this guideline.

Bill Karwin
This is exactly the approach that we took with our database.Table Name: CustomersColumn Names: CustomerFirstName, CustomerLastName, etc...We did this because of the amount of joins that we end up doing for data aggregations, it relieved a lot of stress on the database programmers.
Tom Anderson
See my answer for why I don't believe it's useful to consider a table to be a collection.
Celko always loves citing something, usually around sql-92 and 99 :)
+33  A: 

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.

Brian Boatright
I can't imagine why your answer was voted down. Politics? Anyway, I just upped it again.
still getting down votes even after 8 months... odd.
Brian Boatright
Upvoted after 10 months. I like the practicality of this answer.
I voted down and I'll tell you why, because I disagree. ORM by it's very nature is about mapping. Every ORM tool that I've ever used supports specifying the table name to be used for an entity when it is different from the entity's name. This is important because the whole reason we map to relational databases is so that we can easily make ad-hoc queries and reports with different shapes than our object model. Otherwise we'd all just be using object/document databases by now.
+8  A: 

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 : every thing is singular , every collection of things is singular with an 's' appended. Helps with ORMs too.

Ash Machine
what about a word ending with a 's'? If you have a table called 'News' (just as an example), what would you call the collection of news? Newss? Or would you call the table 'New'?
I would call the table NewsItem and a collection NewsItems.
Ash Machine
What if you have to spell-check all code or else it will not compile ;) ?
Hamish Grubijan
+10  A: 

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.

Adam Carr
+6  A: 

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.

To each his own, I guess. A relational database table is by definition a heading (i.e. metadata naming the attributes) and a set of tuples matching the heading. You can focus on the metadata, whereas other folks focus on the tuples. :-)
Bill Karwin
Hey, User_Table is a name I like! :)
Camilo Martin
+3  A: 

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?

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.

+1  A: 

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

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


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


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.

+8  A: 

How about this as a simple example:

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


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.

In that example, yes, but in practical sql it would never be written that way. You'd have a table alias so it'd be more like `SELECT C.Name, C.Address FROM Customers WHERE Customers.Name > 'def'`
Michael Haren
+1, (a year later) You cited a TERRIFIC example on how the singular makes sense. This is somewhat a religious debate. I was turned onto the singular several years ago by a data architect many years my senior and it's felt right to me (after requiring much convincing for me to switch).
Chris Adragna
+1  A: 

IMHO, Table names should be plular like Customers.

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


I always use singular table names but, as already stated, the most important thing is to be consequent and use the same form for all names.

What I don't like about plural table names is that combined names can get quite strange. If for example you have a table named Users and you want to store properties for the user, this would result in a table named UsersProperties...


Tables: plural

Multiple users are listed in the users table.

Models: singular

A singular user can be selected from the users table.

Controllers: plural would list multiple users.

That's my take on it anyway.

Closer to my take, but mine is that the table's storage of multiple users is actually incidental, and that any singular user is represented by the table, or rather the relation that is a set of tuples representing a User entity.

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 actually always thought it was popular convention to use plural table names. Up until this point I've always used plural. I can understand the argument for singular table names, but to me plural makes more sense. A table name usually describes what the table contains. In a normalized database, each table contains specific sets of data. Each row is an entity and the table contains many entities. Thus the plural form for the table name. A table of cars would have the name "cars" and each row is a car. I'll admit that specifying the table along with the field in a table.field manner is the best practice and that having singular table names is more readable. However in the following two examples, the former makes more sense: SELECT * FROM cars WHERE color='blue'; SELECT * FROM car WHERE color='blue';

Honestly, I will be rethinking my position on the matter, and I would rely on the actual conventions used by the organization I'm developing for. However, I think for my personal conventions, I'll stick with plural table names. To me it makes more sense.

+1  A: 

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

Ian Mackinnon
i have no idea what that says
I do understand it, and I like it. +1

I've always used singular simply because that's the was 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.

Just Some Guy