A while ago I blogged about whether to make table names singular or plural. The subject raised itself again recently in the office after Microsoft’s Entity Framework makes some pretty odd decisions when converting from Plural to Singular form. (According to EF the singular of “ranges” is “ranx”… and if you look on places like Stack Overflow you’ll find other examples, such as “changes” to “chanx”)
Here are the updated results
- Singular: 9
- Plural: 10
- Either: 4
Again, there is no overall winner
Those in favour of pluralising the names said:
- To me, table names should always be plural – they’re a collection of records, and the singular form applies to the record.
Those with no preference:
- I’ve tended to find that people who are more used to thinking of the model in class terms tend to prefer singular. People who are used to thinking from the DB first tend to prefer plural.
- For me using an ORM should be removing the need for me to think about database naming because ideally I will never have to go direct to the database tables, I will always be going through objects.
In favour of Singularising names:
- I’ve always used and prefer singular – better ordering of tables and fewer problems with mappings, but as long as we’re consistent (within a single database)
I suspect this debate will continue on for as long as there are table based databases…
Tables in a relational database are relations (hence the name). The name of a relation should be singular, regardless of how many tuples (rows) are in a table. Singular is more efficient (less space is used, there’s less characters to type, and most people know, for example, what a Customer table means). Singular promotes consistency in the English language, which becomes odd when you use plural words. Somebody out there long ago, after Codd, erroneously decided to give tables their plural names because they thought the name referred to the tuples, not to the relation. But, as in all things in life, things are done a certain way because, well, it matters and has meaning. Pick singular or plural naming as a method and be consistent.
Concerning singular versus plural table names, the subject seems to be controversial, but it shouldn’t be.
While a table is a collection of multiple records,
a table is named after the definition of the one type of record that it contains.
If a table was allowed to have a different name than that of the type of record that it contains,
you could give the table a plural name, so that you could for example have an Employees table
containing multiple Employee records.
But the designer of SQL did not provide for separate names for tables and record types.
Things work out more logically for object oriented programs that use the data,
if the name of a record type (and by extension the table name) is kept singular,
as it will correspond with the name of the class you would use to describe one record.
If you then want to identify a collection in the program, you can use a plural,
or better, use an appropriate modifier, such as EmployeeList or EmployeeArray.
There is also a problem with irregular plurals
for automatic code generation and programmers who have different language backgrounds
or ideas about the formation of plurals in a program.
The English language is not a good and proper programming language,
and trying to make database and program statements conform to English is a mistake.
I world agree that this is a matter of personal preference if Entity Framework would make it at least in a consistent way. I mean by consistent only that if the code behind decides that a table name is plural and create a class with the singular name, then it would look for the right table (the name that was given in first place) and not create a plural noun that is different from the table name (and throws an exception that may not be obvious at first sight). Unfortunately this is not always the case. I am developing an application using an already existing MySQL database, which happens to have tables named, for instance, “logradouros” and “bairros”. The model classes were rightly named “logradouro” and “bairro”, but when I tried to run the application I had exceptions because tables “logradouroes” and “bairroes” we’re not found. I have spent a lot of time looking for some mistyping or some other mistake in my code before realizing that the culprit was not me but some “smart” developer at Microsoft or from the MySQL team which made a code that finds out the singular of “logradouro” as “logradouro” but the plural of “logradouro” as “logradouroes”! More wasted time to find out how to force the association of my classes with the correct tables and now it is solved. I do not know if the automatic pluralizing of table names is a good idea, but it should be done right.