5

I have to design a database covering business assumptions using MS SQL-Server 2008 R2. The current data of the tables will be exported to text files and used as input for a certain application. I would like to implement the tables as bitemporal tables as described by R. Snodgrass in order to track valid and transaction times.

However, it is possible and quite likely that the logical schema of the tables could change, i.e. new columns could be added or old columns could be removed. How would I reflect this in the design of the tables?

Obviously, I couldn't really delete a column, as all old data would disappear. Should I 'flag' not available columns in rows with a NULL entry? Likewise, how would I keep track of columns which are currently in use? This would be crucial for the export of the data as only 'active' columns should be exported.

And last, but not least, how could I log that a new column was added or an old one removed?

2

Im not sure if this answers all of your questions but this sounds very similar to what I do with our data warehouse. However the way i interpret your post you would want to have 1 table with your data, and another with your table definition meta data.

For the data table add a column to flag is_current. (Either an int or bit datatype) Also have 2 date columns valid_dt_from & valid_dt_to.

If a row changes you update the old row is_current = 0, and valid_dt_to =getdate ().

Insert the new row with is_current =1 and valid_dt_from = getdate ().

You can do the above with a single merge query.

To get the current data select * from table where is_current = 1.

Add new columns as they are needed. But don't delete old columns. In theory if you have to reconstruct the table to previous point in time all rows from that time should have null values at that time.

Create a similar table to define your data table definition. Add a record to show when each field gets added, removed and if it is current.

Your select query could be built dynamically. Where is current =1, or reconstructed as at a point in time.

  • Thanks for your input. The is_current part would be covered by my transaction time columns (e.g. tt_start and tt_end). However, my takeaway from your answer would be: NULL values in the data table indicate a non-existent column in the valid period. Also, I would create a bitemporal meta data table with a column for each column in the data table where e.g. a zero would indicate that the column didn't exist at that date and a one would indicate the opposite. When the schema of the data table changes, so would the schema of the meta data table (with updates). – p.vitzliputzli Aug 7 '15 at 11:02
  • Yes. You're on the right track. Depending on how important it is to you to, you could use the definition table in the query to indicate if the column existed at the time or the rows you are looking at had null values. – Sir Swears-a-lot Aug 8 '15 at 3:08
2

If you want to follow the Snodgrass approach, you have my deepest sympathies. You may want to glance at my approach, if only for comparison. And I would be remiss if I did not at least mention Tom Johnston, who has a new book on the subject. It is at least an improvement on Snodgrass!

However, the issue of adding or removing columns to temporal data is fairly implementation independent. But one goal of my implementation is that current and past (and even future) data all reside in the same table or set of tables and is available with the same query. This effects the rest of my answer.

When adding new attributes to temporal data, you must decide what the value of those attributes will be during the time period prior to their existence. And that value may be quite different for valid time and transaction time.

The valid time query asks, "What was the state of the entity at the designated date and time?" How you implement the new attributes will depend on if you can answer that question using those new attributes. If you don't know, or if the past state is irrelevant, then just leave the value null (the default for new columns). Otherwise you must update the new attributes in all the past versions (and by "update" you should know I mean "create new versions"). Not a task I would relish.

A transaction query asks, "What was the state of the entity as it was stored in the database at the designated date and time?" Obviously, the new attributes had no representation prior to their creation, so to be absolutely true to the contract of the query, those attributes should not even appear in the result set. That will be extremely difficult to implement. Good luck with that. Probably the best compromise would just be to show the values as null which, meaning "unknown", is about as accurate a description as any.

Removing attributes means those attributes are no longer relevant. The question then becomes, "Were they ever relevant?" If the answer is no then just drop the columns. Yes, you are changing history, but you are removing irrelevant data so the meaning of the past will not be changed. If the answer is yes then the column(s) must remain. The current values may be set to null and/or any "current" view may be rewritten to ignore those columns, but history must be maintained.

This illustrates the point that temporal modeling takes much more effort than non-temporal modeling. But, of course, sometimes the real-world object changes or we discover new attributes. Who could have foreseen that "ChargeTime" or "SelfDriving" would be needed as attributes when modeling an automobile as recently as a decade ago?

The above are considerations from strictly a designer's point of view. Regulatory requirements, if applicable, may add their own restrictions.

Good luck. Keep in touch.

  • 1
    Thanks for your answer! Your approach sounds interesting. Do you have other explanations about it besides the slides? – p.vitzliputzli Aug 9 '15 at 7:30
  • 1
    There is a pdf document (dropbox.com/s/8hnkzet6fueblz7/TemporalDBDesign.pdf?dl=0) I can make available. It's kind of a mess, I've been working on it off and on for several years. But it has a lot of details of exactly what is going on in the design. If there are still questions, let me know. – TommCatt Aug 17 '15 at 3:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.