Thursday, May 16, 2019

Naming Relations: Singular or Plural?



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

--The.AgileSQL.Club

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.


------------------------------------------------------------------------------------------------------------------
SUPPORT THIS SITE 

Up to 2018, DBDebunk was maintained and kept free with the proceeds from my @AllAnalitics column. In 2018 that website was discontinued. The content of this site 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. Thank you. 

NEW
  • 04/20/19: Added POSTS page with links to all site posts, to be updated monthly.
  • 04/22/19: Updated the LINKS page.

LATEST PUBLICATIONS (order PAPERS and BOOKS) 

USING THIS SITE 
  • 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. 
  • Following the discontinuation of AllAnalytics, the links to my columns there no longer work. I moved the 2017 columns to dbdebunk and, time permitting, may gradually move all of them. Within the columns, only the links to sources external to AllAnalytics may work.

SOCIAL MEDIA

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: Evidence for Antisemitism/AntiZionism – the only universally acceptable hatred – as the (traditional) response to the existential crisis of decadence and decline of Western (including the US)
  • @ThePostWest Twitter page 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 given 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. Practitioners often confuse 'entity' and 'entity type', so we actually prefer the plural because it reflects the set nature of relations.
“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!”

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.


References:

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

[5] Pascal, F., THE DBDEBUNK GUIDE TO MISCONCEPTIONS ABOUT DATA FUNDAMENTALS.

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

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


No comments:

Post a Comment