Use and abuse of Identity columns
-
Have we had a topic on the use and abuse of Identity columns?
Most of our development work has been outsourced and the data structures that are coming back have identity columns everywhere. There's no sign of a normalised data structure at all, more like linked lists. It feels more like kindergarden design from people who were taught databases using MS Access.
I'm struggling to explain to the outsource developers why this offends me so much, and the trouble it will cause. It's difficult to call this a quality issue unless I can offer specific "rules".
-
Might be best to concentrate on lack of normalisation. There tends to be debate on just when it's appropriate to use identity cols / artificial keys etc, but it should be easy to find unarguable textbook examples of TNF. (Incidentally, normalisation is just as relevant to Access as it is to SQL Server - nothing wrong with learning on Access!
) -
All tables need a unique row id and an identity column is the easiset way to implement this, I personally like to use the data as the key where possible but at some point in a relational structure integer values become more efficient than the actual data .. look up tables are a prime example. The main catch on identity columns is that it is a property of the column and does not enforce uniqueness - so make sure every identity column has a unique constraint defined.
Don't know anything about Access, having only ever used rdbms systems. Normalisation is part of the design of a database, an over normalised database will increase the overhead of excessive joins - the more normalised a structure the more likely there are to be surrogate keys - this is of course a generalisation in it's self !!
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply