I have multiple models that need to have their history kept pretty much indefinitely or at least a very long time. The application I would keep track of daily attendance statistics for people in different organizations, and a couple of other similar associations. I've realized that I can't ever delete users due to the user not showing up in a query for attendance anytime before said user was deleted. The problem I'm having is finding a nice way of keep track of old associations as well as querying on those old associations.
If I have a connecting table between Users and Organizations, I could just add a new row with the new associations between a User and the Organization the user belongs to, and query on the old ones based on the date, but I really don't have any elegant way of doing this, everything just feels ugly. I was just wondering if anyone has dealt with anything like this before, and maybe had a solution they had already implemented. Thanks.
views:
42answers:
3Create an is_deleted field so that you can still query those "deleted" users, but modify your code so that they will behave everywhere else as if they are deleted. Then you never need to actually delete the row and lose data.
From a modeling point, the relationship sounds like the one between Employee and Employer, namely Employment. This would hold a reference to both Employee and Employer along with some notion of the TimePeriod (ie, startDate and end Date). If you wanted to query 'active' employees then they are all the ones with a startDate <= Now() && endDate >= Now(), 'terminated' employees have endDate < Now(), etc.
I can't tell in your case what the relationship is between Users and Organizations, and what makes the relationship start and end, but a concept similar to Employment, Membership, Enrollment or Contract is likely there. When you say daily attendance, is it not daily over a period of time? The time period is the basis for your queries.
Hope that helps,
Berryl
There are a number of plugins that keep track of revisions to models, including their associations. Take a look at this search for revision-related plugins.