historical data modelling literature, methods and techniques

Question

Howdy,

Last year we launched http://tweetMp.org.au - a site dedicated to Australian politics and twitter.

Late last year our politician schema needed to be adjusted because some politicians retired and new politicians came in.

Changing our db required manual (SQL) change, so I was considering implementing a CMS for our admins to make these changes in the future.

There's also many other sites that government/politics sites out there for Australia that manage their own politician data.

I'd like to come up with a centralized way of doing this.

After thinking about it for a while, maybe the best approach is to not model the current view of the politician data and how they relate to the political system, but model the transactions instead. Such that the current view is the projection of all the transactions/changes that happen in the past.

Using this approach, other sites could "subscribe" to changes (a la` pubsubhub) and submit changes and just integrate these change items into their schemas.

Without this approach, most sites would have to tear down the entire db, and repopulate it, so any associated records would need to be reassociated. Managing data this way is pretty annoying, and severely impedes mashups of this data for the public good.

I've noticed some things work this way - source version control, banking records, stackoverflow points system and many other examples.

Of course, the immediate challenges and design issues with this approach includes

• is the current view cached and repersisted? how often is it updated?
• what base entities must exist that never change?
• probably heaps more i can't think of right now...

Is there any notable literature on this subject that anyone could recommend? Also, any patterns or practices for data modelling like this that could be useful?

Any help is greatly appreciated.

-CV

Answer 1

This is a fairly common problem in data modelling. Basically it comes down to this:

Are you interesting in the view now, the view at a point in time or both?

For example, if you have a service that models subscriptions you need to know:

• What services someone had at a point in time: this is needed to work out how much to charge, to see a history of the account and so forth; and
• What services someone has now: what can they access on the Website?

The starting point for this kind of problem is to have a history table, such as:

• Service history: id, userid, serviceid, start_date, end_date

Chain together the service histories for a user and you have their history. So how do you model what they have now? The easiest (and most denormalized view) is to say the last record or the record with a NULL end date or a present or future end date is what they have now.

As you can imagine this can lead to some gnarly SQL so this is selectively denomralized so you have a Services table and another table for history. Each time Services is changed a history record is created or updated. This kind of approach makes the history table more of an audit table (another term you'll see bandied about).

This is analagous to your problem. You need to know:

• Who is the current MP for each seat in the House of Representatives;
• Who is the current Senator for each seat;
• Who is the current Minister for each department;
• Who is the Prime Minister.

But you also need to know who was each of those things at a point in time so you need a history for all those things.

So on the 20th August 2003, Peter Costello made a press release you would need to know that at this time he was:

• The Member for Higgins;
• The Treasurer; and
• The Deputy Prime Minister.

because conceivably someone could be interesting in finding all press releases by Peter Costello or the Treasurer, which will lead to the same press release but will be impossible to trace without the history.

Additionally you might need to know which seats are in which states, possibly the geographical boundaries and so on.

None of this should require a schema change as the schema should be able to handle it.