Table Naming Dilemma: Singular vs. Plural Names

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.

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.

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.

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

[To see links please register here]

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.

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.

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.

Tables: plural

> Multiple users are listed in the users table.

Models: singular

> A singular user can be selected from the users table.

Controllers: plural

>

[To see links please register here]

would list multiple users.

That's my take on it anyway.

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.