This past year we released http://tweetMp.org.au - a website devoted to Australian politics and twitter.

Late this past year our politician schema must be modified because some political figures upon the market and new political figures arrived.

Altering our db needed manual (SQL) change, and so i was thinking about applying a Content management systems for the admins to create these changes later on.

Additionally, there are a number of other sites that government/politics sites available for Australia that manage their very own politician data.

Let me develop a centralized method of carrying this out.

After considering it for some time, maybe a great way would be to not model the present look at the politician data and just how they connect with the political system, but model the transactions rather. So that the present view may be the projection of all of the transactions/changes which happen previously.

By using this approach, other sites could "subscribe" to changes (a la` pubsubhub) and submit changes and merely integrate these change products to their schemas.

Without it approach, most sites would need to destroy the whole db, and repopulate it, so any connected records will have to be reassociated. Controlling data by doing this is fairly annoying, and seriously impedes mashups of the data for that public good.

I have observed several things work by doing this - source version control, banking records, stackoverflow points system and several other good examples.

Obviously, the immediate challenges and design difficulties with this method includes

  • may be the current view cached and repersisted? how frequently could it be up-to-date?
  • what base organizations must exist that never change?
  • most likely heaps more i can not think about at this time...

Can there be any notable literature about this subject that anybody could recommend? Also, any designs or practices for data modelling such as this that may be helpful?

Any assistance is greatly appreciated.

-CV

This can be a not unheard of condition in data modelling. Essentially it comes down lower for this:

Are you currently interesting within the view now, the vista in a time or both?

For instance, for those who have something that models monthly subscriptions you should know:

  • What services someone had in a time: this is required to see how much to charge, to determine past the account and so on and
  • What services someone has: so what can they access online?

The beginning point with this type of issue is to possess a history table, for example:

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

Chain together the service histories for any user and you've got their history. How do we model what they've now? The simplest (and many denormalized view) would be to the last record or even the record having a NULL finish date or perhaps a present or future finish date is what they've now.

Understandably this may lead to some gnarly SQL making this selectively denomralized so you've a Services table and the other table for history. Every time Services is transformed a brief history record is produced or up-to-date. This type of approach helps make the history table much more of an audit table (another term you will see bandied about).

This really is analagous for your problem. You should know:

  • Who's the present Mega pixel for every chair in the home of Reps
  • Who's the present Senator for every chair
  • Who's the present Minister for every department
  • Who's the Pm.

However, you should also know who had been all of individuals things in a time so you'll need a history for those individuals things.

The like the twentieth August 2003, Peter Costello designed a pr release you should realize that at this time around he was:

  • The Member for Higgins
  • The Treasurer and
  • The Deputy Pm.

because certainly someone might be interesting to find all press announcements by Peter Costello or even the Treasurer, that will result in the same pr release and can be impossible to follow with no history.

Furthermore you will need to understand which seats have been in which states, probably the physical limitations and so forth.

None of the should need a schema change because the schema should have the ability to handle it.