Tracking past group memberships of multiple groups

[Meir Guttman]

Hello all!

I have an application in which I have to track all group-membership history of multiple groups, each group comprised of multiple members. Think of it as fraternity membership in a college: many fraternities, many members in each.

Group-members are either added or deleted, never updated. A given member however can leave and then rejoin later its former group or another group. Typically, more then one member is joining and leaving at a given date.

And a key requirement is to be able to reconstruct the group membership as it was at any past date. (The time granularity is days.) So far it seems that I am talking about a classical "Point-in-Time Architecture" or PTA.

After searching these forums and the web, I came across two approaches:

* Arthur Fuller's http://www.simple-talk.com/sql/database-administration/database-design-a-point-in-time-architecture/:
Essentially adding "DateJoined" and "DateLeft" and having special INSERT and DELETE SQL queries;
* Arthur Fuller and Peter Brawley's http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch21.pdf:
Essentially a straight-forward table, devoid of any history, actually depicting current membership. The table is given SQL triggers on its INSERT/DELETE/UPDATE that record all changes in a log-table. A rather elaborate stored procedure reconstructs the group membership at any past date from the main table and the change-log.

These two approaches deal only with single rows, at least in their examples. The question is whether or not either one can deal with retrieving a multiple members group, given the Group-ID and a (past) date.

Any ideas?

Regards,
Meir