Should I pluralize table names, is it Person, Persons, People or People?
Reading Time: 5 minutes (just do it!)
There is a lot of confusion when it comes to designing tables in SQL Server around whether to pluralize names or not. How do you choose whether to pluralize or not?
If we want to store a list of people and their details do we use “Person”, “Persons”, “People” or “Peoples”? Some people will use “People” and some will use “Person”, other persons or people would go for “Peoples” or “Persons”.
The defined standard is to go for non-plural because in a table we are storing a set of an entity and we name the table as the entity so if we want to store one or more people in a single entity or table, we store it or them in the “Person” table.
If we stick to this then it makes other situations simpler and stops us having to think about how to pluralize a word, I have for example seen hierarchy pluralized as “hierarcys”.
Need evidence to back up your battle with your co-workers?
If we look at “Relational Model of Data Large Shared Data Banks " by none other than “E. F. Codd” who basically invented the relational database, the examples he gives are singular (supplier and component).
If we then look at the ISO standard for naming things (11179-5: Naming and identification principles), this also says that singular names should be used “Nouns are used in singular form only”.
Finally, if we look at the examples from Microsoft we can see where some of the confusion comes from!
The old “pubs” sample (https://github.com/Microsoft/sql-server-samples/blob/master/samples/databases/northwind-pubs/instpubs.sql) has a mix of singular and plural.
“Northwind” also uses a mixture (https://raw.githubusercontent.com/Microsoft/sql-server-samples/master/samples/databases/northwind-pubs/instnwnd.sql) - note we have “Region” and “Territories”, where the consistency was with that I will never know!
Wide World Importers again uses a mixture of plural and singular, the funny thing is that they use the singular version of “Archive” where the dictionary shows that “Archives” is good for the singular or plural version so they could have easily (accidentally?) been more consistent.
It is easy to see where the confusion has come from and the chances are you will work on an old database so try and stay consistent with the rest of the code and team. If you have to use plural names then define when you will just append an “s” to the end of the name and for which words you will use “ies” or whatever.
For new projects or where you can easily change the name of entities then I would say you must use singular names, for older projects you’ll need to be a bit more pragmatic!
References:
“A relational model of data for large shared data banks”:
https://cs.uwaterloo.ca/~david/cs848s14/codd-relational.pdf
