Wednesday, 14 March 2012

Database table naming conventions

Opinions are like... belly buttons and so are naming conventions, right? Some people ignore them and some are ready to go to some lengths in proving superiority of theirs. And the truth is it doesn't really matter, unless your convention happens to be something as silly as Hungarian notation, for example.

When it comes to naming database tables there are two schools of thought: you should either use singular or plural nouns. Some people argue that singular looks better in projections and selections because you tend to think about an individual item there. Some people, and I am among them, think that the noun should be plural as it is semantically closer to what it is: a set.

But I am easy, I am not going to try to convince anybody that a table called sales will perform better then one called sale. It is just that sales fits better in the way I think about databases and in turn the way I think about them have on a few occasions proven to result in better performance ;)


Recently when starting a new project we were discussing the said conventions and when I mentioned my plural nouns for tables I was hit with 'What? Nobody does it'. 'There are two schools of though', I said, 'but I'm easy and can work with either.' but the discussion didn't end there. I was asked to support my claim with some references. Now, where do you get such reference? After all these are opinions. The only thing that sprung into my mind was an old MS demo database Northwind which is using the plural convention, but that was dismayed with 'That's MS, they do everything wrong!'. Besides, something I realised later, newer AdventureWorks database follows the singular noun convention.

So I decided to do some googling, a bit of research to have a set of references ready for the next time. Obviously there is a lot holy war stuff going on in which supporters of singular table names seems to have an upper hand right now but if something is common or popular does not make it right. And when it comes to some more balanced, technical considerations I have found only one worth mentioning which was in favour of singular table names and I found it in a rather vintage blog post on justinsomnia.org.

I have also found quite a few references supporting plural table names including MVP Pinal Dave's SQL Authority here, and here. A document on ss64.com. about Oracle standards and probably most interestingly  an ISO/IEC 11179 standard which gives a set of recommendations in regards to formulation of data definitions and naming and identification principles. How it applies to database tables is explained in plain English on wikipedia.

Funny enough one of the most common reasons for not doing it 'right' is the problematic nature of the English language and its rare cases of irregular plural nouns. Really? Aren't we supposed to be the clever ones and here we are bitten by simple declination?

3 comments:

  1. Three years later, I have an answer. The three DBMSs I have used all have plural names for their system tables: Sybase, MS Server, and Oracle. What do the makers of the DBMSs know that the users of the DBMSs don't know. It seems obvious to me: if the makers use plurals, then the users should use plurals.

    ReplyDelete
    Replies
    1. OK, It is also obvious I can't count. 2017 - 2012 = 5, not 3.

      Delete
  2. This feature refers to the capability of various mechanisms in a DBMS to handle isolation and all ensure correctness of the database information subsequent to a transaction. kpi dashboards

    ReplyDelete