I have been working on a project that I had worked in 2005, trying to get a handle on what I had done about 3 years back. Exploring code and the database has been fun, also discovering the data layout and building new set of data for production has been extremely entertaining. What I learnt from this whole experience was this <blockquote>if your code(application or other wise) is not expecting data, this data should not be provided or even considered valid by the database, the database should be designed such that it does not even allow invalid combinations of the data.</blockquote>
Here is an example. We have a base table named as SETTINGS, that can be changed by the user by overriding the value in the extension table known as SETTINGSEXTENSION, table structure is show below
SETTINGS ID (PK) KEY (BusinessKey) VALUE (Value for the Key)
SETTINGSEXTENTION ID (PK) SETTINGSID (FK to SETTINGS table) OVERRIDDENVALUE (Value overriding the value in the SETTINGS table) BYUSERID (Value over ridden for user)
in this scheme the same user can create extensions for the same base key causing the application to barf. If we introduce unique index on SETTINGSEXTENSION TABLE(SETTINGSID, BYUSERID) we ensure that the database does not allow this data and makes sure that the application will not barf.
There are many more other things I learnt.. those will follow..