Toad World® Forums

Modeling question

Looking to model an efficient mechanism to be able to store versions of data. An example is that we have a “member” entity and that member has name fields. Those names can change over time, and I would like to be able to query a specific date to get back the appropriate name.

Are there any “best practices” or design patterns that support this?

_mike

The usual way is to create the history copy of entity table. The history table should include extra fields to keep track when record was insert, type of data modification (INSERT, UPDATE) and who modified data (user). On original entity table are hooked triggers to capture modifications (INSERT, UPDATE) and insert new/modified data into history table.

OK, got that, but it bring up a question. Lets assume that I have an orders table that has the person table foreign keyed into it (identifying who created the order). Now, the person changes their name and a new person history record is created, and the new record has their current name. I need to use their old name on the order. What key value exists on the orders table?

_mike

Our shop’s method is to maintain an “active date range” on entities that
may change in such a manner. You can still maintain a history table,
which is a more detailed log of such changes.

Assume you have person_history table with datetime column when was record changed/inserted in person table and rest of columns are the same as in person table. I assume you know in which history data you interested in. If you know history range (example: from 2009-01-01 to 2010-06-01), you can join orders table data with person_history table using the same person_id in orders table and add history range filter. The history range is crucial to to retrieve proper data from person_history table.

Thanks all for the responses, they helped a lot. In my research, I also found this, which is a great resource.

_mike

Thank you all, guys. That’s how a community should work.

Regards,
Lukas