OLTP and history tracking?

Hello,

We are currently working at designing an OLTP database for an healthcare organization. The requirements and business rules identified tell us that we have to track changes in Member history.

Changes in Member data would have to be tracked according to the principle of Slowly Changing Dimensions (type 2 and 3) to which, most of us are accustomed to in a data-warehousing environment. However, I have never heard of anyone implementing SCDs in a OLTP database. Is it the right approach or instead, uncharted territory?

Can anyone comment on implementing Slowly Changing Dimensions in such scenario? What is the right approach? It seems that it would more realistic to implement a similar method by creating database triggers that would be fired off on INSERT or UPDATE or even DELETE. Then the triggers would populate a Member_History table that has a 1NF relationship with the Member table...

Knowing that the amount of trigger code to write is set to be carrier specific, it is likely that the development effort would be quite enormous...

Any suggestions, comments or pointer welcome.

Thanks in advance!

Regards,

Alan.

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Apr 18 2000 - 00:00:00 CEST