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

[Greg Low]

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 | W: 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