Database design to hold a person's information that changes with time?

Question

We use a third-party product to manage our sports centre membership. We have several membership types (eg. junior, student, staff, community) and several membership statuses (eg. annual, active, inactive, suspended). Unfortunately the product only records a member's current membership type and status. I'd like to be able to track the way our members' type and status have changed over time.

At present, we have access to the product's database design. It runs on SQL Server and we regularly run our own SQL queries against the product's tables to produce our own tables. We then link our tables to pivot-tables in Excel to produce charts. So we're familiar with database design and SQL. However we're stuck as to how to best approach this problem.

The product records a member's membership purchases and their start and expiry dates. So we can work back through that data to determine a member's type and status at any point in time. For example, if they bought a junior membership on Jan 1, 2007 and it expired on Dec 31, 2007 and then they bought a student membership on Jun 1, 2008, we can see their status went from active to inactive to active (on Jan 1, 2008 and Jun 1, 2008, respectively) and their type went from junior to student (on Jun 1, 2008).

Essentially we'd like to turn a member's type and status properties into temporal properties or effectivities a-la Fowler (or some other thing that varies with time).

Our question (finally :) - given the above: what database table design would you recommend we use to hold this member information. I imagine it would have a column for MemberID so we can key into the existing Member table. It would also need to store a member's status and type and the date range they were held for. We'd like to be able to easily write queries against this table(s) to determine how many members of each type and status we had at a given point in time.

UPDATE 2009-08-25: Have been side-tracked and haven't had a chance to try out the proposed solutions yet. Hope to do so soon and will select an answer based on the results.

Answer 1

Given that your system is already written and in place, the simplest approach to this problem (and the one that affects the existing database/code the least), is to add a membership history table that contains MemberID, status, type and date columns. Then add an UPDATE and an INSERT trigger to the main member table. When these triggers fire, you write the new values for the member (along with the date of the status change) into the member history table. You can then just query this table to get the histories for each member.

This is fairly simple to implement, and won't affect the existing system at all.

I'll write this for you for a free membership. :)

Answer 2

I would put the membership info in it's own table with start and end dates. Keeping the customer in separate table. This is a pain if you need the "current" membership info all the time but there are many ways to get around that either through queries or triggers.

Answer 3

I would create a reporting database that was organized into a star schema. The membership dimension would be arranged temporally, so that there would be different rows for the same member at different points in time. That way different rows in the fact table could pertain to different points in history.

Then I would create update procedures for updating the reporting database periodically, say one a week, from the main database. This is where the main work would come.

Then, I would drive the reports off the reporting database. It's pretty easy to make a star schema do the same things a pivot table does. If necessary, I'd get some kind of OLAP tool to sit in front of the reporting database.

This is a lot of work, but it would pay off over time.

Answer 4

I cannot recommend you enough to read Joe Celko's "Sql for smarties - advanced sql programming". he has a whole chapter on temporal database design AND how to (effeciently and effectively) run Temporal Projection, Selection and Temporal Join queries. And I would not do him justice to even attempt to explain what he says in his chapter in this post.