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!”
--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
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.
LATEST UPDATES
-12/24/20: Added 2021 to the POSTS page
-12/26/20: Added “Mathematics, machine learning and Wittgenstein to LINKS page
LATEST PUBLICATIONS (order from PAPERS and BOOKS pages)
- 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).
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.
- 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.
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 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 (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!”
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.
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