Hi,

Im looking to store history of changes for 'master' information about suburbs, so I can easily retrieve the current profile/view, but also determine when any attribute has changed, and also look back at the status of a particular suburb for any point in time... One important usage of this database is to determine when attributes have changed, therefore should be able to drive from a query on some 'audit attributes', etc...So, Im trying to keep this as simple as possible but also provide the above funcitonality, the way I see it I have 3 options, if anyone has any opinions on them and which may be the best way in relation to complexity, I would be happy to hear it...

1. Single Master Table row : No actual history would be kept as direct updates would be performed, but if I added fields such as Last Change Date and Last Change Type, etc. which may store the name of the field(s) which just changed, I would at least be able to see what changed 'today'... Probably easiest but least functionality, considering I cannot see past history or changes.

2. Single Master Table row + corresponding History row : For example, a table called Suburb ( which contains a single row per suburb, maybe no actual attributes or maybe the current version ) and another called Suburb_History, which has Effective To / Effective From dates and stores a row for each 'change instance'... Would contain the same 'Last Change Date/Type' fields as per above also... I would have thought I the first table needs to exist only as a 'driver', otherwise queries against the History table directly could be confusing if people forget to limit by date... Think this covers all the requirements, but probably not the best modelling practise..

3. Single Master Table row + 4th Normal Form table for each attribute : Similar to Point 2 above, but instead of a Suburb_History table, have a table such as Suburb_Attribute_History, which contains a row per attribute per value... With this option at least each individual change is isolated ( as its a row per attribute ), so probably cleaner ( wouldnt need a Last Change Type ) and uses less storage space as it only records the fields that actually change... As per above, the actual Master table itself could maybe store current values so in the 95% of cases you just want current values, the utilisation of the database is streamlined ?

4. Any other option ? Cant think of any myself...

In case it matters, this is an OLTP database on Oracle 9i... Any opinions / suggestions would be appreciated, especially in relation to the existance of the Master table in conjunction with the History table...

Thank you...
Arnzie

if you are "trying to keep this as simple as possible" then you will choose 2 over 3

ask yourself how easy it is to re-assemble a given master row at some point in the past

Thanks for the reply... I actually probably over-emphasised the need to do a quick and dirty solution, it is quite important that I get the modelling right as this data layer will be becoming the basis for our data warehouse at some point in the future. I guess what I was trying to say was that I didnt want to over-complicate the matter and put in the 'absolutely perfect solution' if a easier yet still correct/robust option was there...

On that premise do you still think option 2 is better than option 3, or is there no real right or wrong option here ? At least with option 3 you can isolate individual changes per row as it is the 4NF solution, so would handle multiple changes at once from a audit/notification point of view much better..

Either way I still there is value in having 'current values' stored on the Suburb Master record so I can get the current information out easily without having to go to any History / 4NF table...

Thanks Again,
Arnzie