Should I name a Database Table “User” or “Users” ? (or “Apple” or “Apples”). Logically you would think that a table contains multiple users so you name it “Users”, however if you want to select a user you would want to us a singular: “select * from user where user.name = ‘Ed’”. You could even be more consistent and name each table “UserCollection” but since that would then be applicable to each table… that would be nonsense.On StackOverflow I found a huge thread on this and it seems most votes go for Singular Table Names.
I will list the arguments for using plural names:
1. ISO IEC 11179 chapter 4 is referenced but it does not explicitly define table names, it defines meta data which sould be singular, like class names which should be singular
Arguments for SINGULAR “User”
1. Even although you could make it a standard to make table names plural, in different languages this will become troublesome e.g. in Chinese there are not even Plural names (so your standard is by default wrong) and in German you would have to prefix in some cases “der-die-etc”. On top of that you will have irregular plurals and in even in English “sometimes plurals” (Water, Soup, Cash). you will spend a long time debating on the correct plurals …. So singular names is a natural standard when derived from natural language. Using the uninflected Noun means in English Singular. Because inflecting the number on the table causes numerous orthographic problems. On top of that why would you want to append “s” to each table.
2. A table is a bag / container, just like “AppleBag”, which is singular. Because you don’t want to attach “bag” or “collection” (or even “table”) to each name the singular name is short name because we do not want to attach “collection” to each table name. So table “User” is actually table “UserCollection”
3. In schema’s, ER modules, UML models, documentation, code it looks more aesthetic, orderly and logical to use singular verbs and no top of that it’s shorter.
select customer.name from customer where customer.id= 1
select customers.name from customers where customers.id = 1
4. When making references/relations between tables things would start to look very unnatural when using plural: “CustomersProperties” versus “CustomerProperty”. On top of that in Relational Databases , the same object is an entity for some users and a relationship for others. So when you would use a plural form…. you end up in chaos.
5. It makes code more easier, transparant and more bugfree. The object “user” belongs to a class “user” which is stored in a table “user”
6. Your shirts you put in the shirt drawer. Not in the shirts drawer.
7. ORM tools, libraries etc… are becoming more widespread which means you would probably use singular table names since they map the object “User” to table “User” although the ORM tools allow you configure those mappings differently… in most cases it’s not really practical to do so.
8. In set theory an instance of a set is representative for the set, so Apple is an Apple Set. So also when you create a table , you create it in the context 1 record: create table [user] [name] text, not in the context of multiple records.
9. A sql query selecting multiple users returns… user not users, so its not logical to use the plural form
</p> <p>select * from <font style="background-color: #00ff00">users</font> where <em><font style="background-color: #ff0000">users</font>.</em>name like ‘Ed’ (returns <font style="background-color: #ff0000">user</font>)</p> <p>select * from <font style="background-color: #00ff00">user </font>where <em><font style="background-color: #00ff00">user</font>.</em>name like ‘Ed’ (returns <font style="background-color: #00ff00">user</font>)</p> <p>
10. the table is a collection of metadata for an entity e.g. in a user table you store properties for a real world user not for users.
11. Most Database Modelling Standards and Examples (Chen, Codd, Martin, …) use singular names + that is the way it is taught in universities and schools
Arguments for Plural “users”
1. The table contains users so we name it “users”, each row is a “user” , otherwise it “would look unnatural to non techical readers”. (so is non argument)
2. The System Tables in a Database are sometimes plural (mostly in SQL Server, some in Oracle), let’s follow that example (so is non argument)
3. On W3 schools and some other example sites they use the plural form. (so is non argument)
I see totally no arguments for using the plural form, other than “it makes some people feel better” as opposed to a very strong, long list for using the singular form.