RE: [SQLDownUnder] [SQLStyle] #1: Singular vs Plural Table Naming

[Huppatz, Jeremy B]

My take (YMMV)...
 
Relational databases are based on entity relationship math.  In a mathematical ER scheme, you refer to a set of a particular archetype - e.g. a set of <<Customer>>, a set of <<Transaction>>, etc.  The Entity name is - by mathematical convention - singular.  The purist in me says that if you want to go plural, then name your table "Customer_Tuples" or "Transaction_Tuples", etc...
 
However, like just about all of you I also think that's a little silly and impractical.  I prefer singular entity names when modelling as it focuses me on the entity and its cardinality with respect to other entities, however this is only really necessary in the logical modelling phase.  By the time a decent logical model has been put together (including relationship specifications, which should include cardinality) the modeller should understand the data structures well enough to be comfortable with whatever standard reigns supreme for the physical data model.  
 
Because of my personal preference for singular naming in LDMs, I tend to prefer singular names for tables in PDM's as well but this is mainly a function of wanting to avoid additional (arguably) unnecessary work.
 
In my experience, the value of this practice scales exponentially with model size... i.e, the level of potential confusion caused by singular names in LDMs and plural names in PDMs processes tends to be greater with more complex databases.  There are others that argue that using plurals in physical models/singular nouns in logical models makes it clearer which model you're looking at.  I'm not entirely convinced by that argument when I'm the poor schmuck who has to go and rename all his entities when going from logical to physical.
 
Finally, I think it's a good practice to keep people focused on the fact that when they insert a row into a table, they are preparing a single tuple in a clearly defined and constrained set.  I think about Entity Relations (i.e. tables) as being distinct from collections in the OOP world.  Collections can be more freely typed, and the concept of an Item in a collection of Objects doesn't necessarily map one-for-one with the concept of a Tuple (row) in an Entity Relation.  There are plenty of situations where they DO, but there are equally many where they do not. 
 
I see a lot of developers trying to process relational data like it's a collection of objects, and have a nasty tendency to smirk at them when they suggest that RDBMS technologies are lacking because they don't allow "OOP"-like functionality that they think should work because they haven't got the point of how ER data is defined and structured at the grass roots level.  CTEs address a lot of these problems and maintain an ER perspective on the data.  Once you start adding in all sorts of "overloads" in an RDBMS to make relational data behave more like a collection of objects I think you're on a slippery slope.
 
So... I also take a vaguely smug satisfaction in persisting with singular naming standards because I think it's a good mnemonic to prod developers to remember that tables are NOT collections of objects or arrays of structs, but a set of Relations between various Attributes of a given Entity.  (Admittedly, under the covers, most RDBMSs implement tables as linked lists of structs... but Sssshhhhh!!! What developers don't know doesn't have to hurt them!)
 
Peace... 
 
Out :)
 
 
Jeremy Huppatz 
| EDS | Australia ADU | Data Engineering SME (SQL Server/Data Warehousing) | 
| Level 10, 108 North Terrace, Adelaide, SA, 5000 | Office: +61 8 8464 1739 | Fax: +61 8 8464 2138 | Email: jeremy.huppatz@xxxxxxx <mailto:jeremy.huppatz@xxxxxxx>  | 

________________________________

From: SQLDownUnderList@xxxxxxxxxxxxxxxxxxxxxx on behalf of Greg Low
Sent: Mon 9/07/2007 2:30 PM
To: SQLDownUnder@xxxxxxxxxxxxxxxxxxxxxx
Subject: [SQLDownUnder] [SQLStyle] #1: Singular vs Plural Table Naming



Hi Folks,

 

I'm going to break these into fairly small chunks as they're discussed. Each one will have [SQLStyle] in the subject so those who wish to ignore can. I'll also have numbers on them (this one is #1) so we can try to group the discussions a bit and each can continue on over time in parallel if need be.

 

I thought I'd start off with one of the topics that seems to raise the biggest religious debates to get that out of the way early.

 

Nothing seems to cause more disagreement than singular vs plural table names. Ultimately, you're going to make up your own mind on this and it doesn't affect most of the rest of the discussion regardless of what you choose. However, I thought I should offer a perspective on it. The issues I looked at (pro and con) are:

 

1. [PRO] Tables are collections, not individual items. Using collective naming enforces the set perceptions.

2. [CON] Much code is written that refers to a single row. This particularly relates to object relational mapping tools and code generators that try to directly map tables (or views) to objects.

3. [XXX] Microsoft documentation is completely contradictory on this topic (MSDN guidelines say singular, MCS guidelines say plural, etc.)

4. [PRO] SQL Server's own system tables and views are named in plural.

5. [PRO] ANSI and ISO documentation encourage plural. The INFORMATION_SCHEMA views are good examples of this.

6. [CON] AdventureWorks uses singular naming. I tracked down the person responsible for this. He said he hired a lady to create it and the only advice given was "be consistent".

7. [PRO] I polled the SQL Server MVPs and product group members. I got an 89% plural response.

8. [CON] Developers seem to think in rows and across the board seem more comfortable with singular. 

9. [PRO] The majority of DBA's I've polled are more comfortable with plural and think half the problem with a lot of database code they see is that the person writing it was thinking in rows instead of sets.

 

My own take on it:

 

Until a couple of years ago, I was definitely in the singular camp. After researching it for quite a while, I've been in the plural camp ever since. 

 

Even in .NET coding, we name collections with plural nouns. Any good ORM or object tool allows you to have a different name for a class and a table anyway.

 

I wish there was a consistent way to indicate within the database what the name of an entity represented by a single row was. I've started to use extended properties for this. That allows me to easily code-generate if I wish.

 

 

Regards,

Greg

 

Dr Greg Low
Readify | Senior Consultant

Suite 206 Nolan Tower | 29 Rakaia Way | Docklands | VIC 3008 | Australia 

M: +61 419 201 410 | E: mailto:greg.low@xxxxxxxxxxx <mailto:greg.low@xxxxxxxxxxx>  | W: www.readify.net <http://www.readify.net/> 

 

to unsubscribe to this list, please send a message back to the list with 'unsubscribe' as the subject. Powered by mailenable.com - List managed by http://www.readify.net 



to unsubscribe to this list, please send a message back to the list with 'unsubscribe' as the subject. Powered by mailenable.com - List managed by http://www.readify.net

<<winmail.dat>>