Table Names - Singular or Plural?
- Category: Software Development
- Posted On: 5/10/2006
- Views: 25,437
I have always worked under the rule of thumb that table names should be singular. The debates between singular or plural table names continues without end... Recently, I have challenged my own assumptions and come to a new conclusion, which should satisfy all parties. Perhaps today will be the day when the debate stops.
The answer may be both at times!
It's important to make note at the beginning that most people prefer singular table names, but it's also interesting that Microsoft standardizes on plural. Just to be different? Let's find out...
I have come to a unique conclusion (IMO) that parallels object-oriented programming:
"Table definitions are analogous to class definitions, but the use of a table as a set is analogous to a collection of instances of the table definition itself."
The designer of a database is thinking "class" definition and the name becomes singular ("a set of type Employee"). Architects might be correct to use singular names in logical data models (Data Definition Language), with the exception of modeling "many" relationships, cardinality rules, and other set-based constraints, which would assume a collection of instances of the table definition (class). When using a table, it is also true that you are working with a set or collection of objects of type "table", so the name used as a set (Structured Query Language and not DDL) would be plural.
Using this methodology, technically an architect may be correct to name their entity "Employee". But, the use of a table as a collection far outweighs the conscious use of its definition, so, IMHO, designers should simply adopt the standard to pluralize their entities from the beginning (including logical models) to unify the process.
The OOP analogy breaks down when you begin to name tables as you would in a class library with names like "EmployeeCollection". Since all tables will represent collections of type "Employee", distinguishing collections from classes with the "Collection" suffix is pointless. But, the fact that the table represents a collection remains and should therefore be pluralized "Employees" (or be given a more meaningful collective name like "Personnel").
The Ultimate Conclusion
- For database servers that support defining entity synonyms, make everyone happy by giving tables singular names (for logical design and modeling) and create plural aliases for everyone that actually uses them. — SQL Server 2005 supports Synonyms
- For servers that do not support entity synonyms, I would personally suggest plural table names. Although you'll be deviating from logical design and modeling standards, you'll be making hundreds and possibly thousands of queries easier to understand as set-based operations on a collection of instances of the table definition itself. If you really want the extra maintenance, keep your entity names singular in your logical models and designs.
- Category: Software Development
- Posted On: 5/10/2006
- Views: 25,437
Comments
- From washeeq
- On 5/10/2007
It is quite funny to read that 90% of time is development. Maybe some school homeworks :o) I have passed through several quite a big projects and development is max about 30%. Analysis and design is the major phase followed by tests.
- From Amit
- On 9/12/2006
Thank you for this summary, which reflects my own conclusion. Too much argument out there on this tpic for someone looking for a quick right answer. Both sides have merits , but I agree with the conclusion to go with plural because that applies to 90% of the time, which is development and not the initial design when singular is correct. Collective nouns are best but who is going to be bothered with looking for one for each table.