noodle22
Programmer
- May 8, 2008
- 23
Hi,
I have the typical problem of needing to store historical data from a normalized database the problem is I have would be dimensions referencing other dimensions. The worst case is
I may want to do a report on assets, buildings (which are assets - note the one to one relationship), or deficiencies.
So, one way to do this would be to have triggers for each update event. ie
1) An asset type gets updated, trigger inserts a new AssetType with the updated information keeping the same ID but giving it a new surrogate key
2) Now all the current assets that referenced the old assetType must have new values inserted so that they can reference the new asset type
3)Now all the builders that referenced the old Asset must have new values inserted to reference the new assets surrogate key
4) when I insert a new deficiency at a later date, I will grab the current surrogate key from the buildings table
The problem with this is I have cascading updates going through my tables. This seems unpredictable and perhaps not a good way to store this type of a history. So, as far as I am aware, the only other option is denormalization. The problem is, the Assets and the buildings tables both contain a lot of binary data. I don't want to duplicate this data because my database will become way to bloated. Any ideas? I am really really stumped on this one and am in need of some sort of creative solution or something
I have the typical problem of needing to store historical data from a normalized database the problem is I have would be dimensions referencing other dimensions. The worst case is
Code:
Table:Deficiency
sk: surrogateKey for different versions of the same record
pk: ID
fk: BuildingSKID
...
Table: Buildings
sk: surrogateKey for different versions of the same record
pk,fk: AssetSKID
...
Table: Assets
sk: surrogateKey for different versions of the same record
pk: ID
fk: AssetTypeSKID
...
Table: AssetTypes
sk: surrogateKey for different versions of the same record
pk:ID
...I may want to do a report on assets, buildings (which are assets - note the one to one relationship), or deficiencies.
So, one way to do this would be to have triggers for each update event. ie
1) An asset type gets updated, trigger inserts a new AssetType with the updated information keeping the same ID but giving it a new surrogate key
2) Now all the current assets that referenced the old assetType must have new values inserted so that they can reference the new asset type
3)Now all the builders that referenced the old Asset must have new values inserted to reference the new assets surrogate key
4) when I insert a new deficiency at a later date, I will grab the current surrogate key from the buildings table
The problem with this is I have cascading updates going through my tables. This seems unpredictable and perhaps not a good way to store this type of a history. So, as far as I am aware, the only other option is denormalization. The problem is, the Assets and the buildings tables both contain a lot of binary data. I don't want to duplicate this data because my database will become way to bloated. Any ideas? I am really really stumped on this one and am in need of some sort of creative solution or something