Friday, January 24, 2020

Naming Relations: Singular or Plural?

Note: Revised 1/24/20.
“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" [sic].

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".

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!”


Ignoring, for the purposes of this discussion, that a SQL table is not a relation[1], and we don't "store a list of an entity set" in it[2], naming relations involves two choices: (1) the name per se (person, people?), and (2) singular or plural (people or peoples? person or persons)? The former is determined at the conceptual level by the enterprise's business terminology[3]. While the RDM is mute on the latter, nevertheless foundation knowledge (here, what relations represent) is, as always, relevant.


DBDebunk was maintained and kept free with the proceeds from my @AllAnalitics column. The site was discontinued in 2018. The content here is not available anywhere else, so if you deem it useful, particularly if you are a regular reader, please help upkeep it by purchasing publications, or donating. On-site seminars and consulting are available.Thank you.

-12/24/20: Added 2021 to the
POSTS page

-12/26/20: Added “Mathematics, machine learning and Wittgenstein to LINKS page

- 08/19 Logical Symmetric Access, Data Sub-language, Kinds of Relations, Database Redundancy and Consistency, paper #2 in the new UNDERSTANDING THE REAL RDM series.
- 02/18 The Key to Relational Keys: A New Understanding, a new edition of paper #4 in the PRACTICAL DATABASE FOUNDATIONS series.
- 04/17 Interpretation and Representation of Database Relations, paper #1 in the new UNDERSTANDING THE REAL RDM series.
- 10/16 THE DBDEBUNK GUIDE TO MISCONCEPTIONS ABOUT DATA FUNDAMENTALS, my latest book (reviewed by Craig Mullins, Todd Everett, Toon Koppelaars, Davide Mauri).

- To work around Blogger limitations, the labels are mostly abbreviations or acronyms of the terms listed on the
FUNDAMENTALS page. For detailed instructions on how to understand and use the labels in conjunction with the that page, see the ABOUT page. The 2017 and 2016 posts, including earlier posts rewritten in 2017 were relabeled accordingly. As other older posts are rewritten, they will also be relabeled. For all other older posts use Blogger search.
- The links to my columns there no longer work. I moved only the 2017 columns to dbdebunk, within which only links to sources external to AllAnalytics may work or not.

I deleted my Facebook account. You can follow me:
- @DBDdebunk on Twitter: will link to new posts to this site, as well as To Laugh or Cry? and What's Wrong with This Picture? posts, and my exchanges on LinkedIn.
- The PostWest blog for monthly samples of global Antisemitism – the only universally acceptable hatred left – as the (traditional) response to the existential crisis of decadence and decline of Western  civilization (including the US).
- @ThePostWest on Twitter where I comment on global #Antisemitism/#AntiZionism and the Arab-Israeli conflict.


Group of Entities of a Type

A relation represents formally in the database (facts about) a group of entities of a single type -- it is a set of tuples constrained such that each represents a fact assumed by convention to be true about a group member -- its properties[4]. Let's assume that in your enterprise there is a group of entities referred to as persons (i.e., of type person, where person is a name assigned for convenience to the collection of person properties[5]). Users understand semantically the difference between person and persons, but a name is just a symbol in logic and, thus, the difference is meaningless to the DBMS. In other words, there is no relational (i.e., formal theoretical) reason for preferring one over the other -- Codd's reference to the "Supplier relation" should not be taken to mean imposition of the singular. 

You have the choice of naming the corresponding relation for the group of entities (PERSONS), or for the entity type (PERSON) per user preference, as long as you are consistent.

“Finally, if we look at the examples from Microsoft we can see where some of the confusion comes from! The old "pubs" sample ( has a mix of singular and plural. "Northwind" also uses a mixture ( - note we have "Region" and "Territories", where the consistency was with that I will never know!”

Practitioners often confuse 'entity type' with entity (instance of the type), so using the plural helps remind us the set nature of relations. If you go for the singular you can add a G. (group) or T. (type) to prevent the confusion.

Like so many misconceptions[5], this confusion and inconsistencies would have not occurred had practitioners had foundation knowledge. This naming "dilemma" indicates that they don't[6] and, given the substitution of tool self-training for education[7], sadly, are unlikely to.


[1] Pascal, F., Tables: So What?

[2] Pascal, F., Logical-physical Confusion.

[3] Pascal, F., Conceptual Modeling Is Not Data Modeling.

[4] Pascal, F., What Relations Really Are and Why They Are Important.


[6] Pascal, F., The Cookbook Approach to Data Management.

[7] Pascal, F., A Note on Education vs. Training.





No comments:

Post a Comment

View My Stats