Thread

I’m in the planning stages of a relatively large site and the client has a requirement that I’m not quite sure how I’m going to fulfill.

So - the problem:

We have organizations in the system.  An organization can change their information (and does), and we need to keep a running track of those changes over time. The client gives out small grants, and we need to associate grants with the organization that submits the grant application. It needs to be associated with the submitting organization when they actually submitted the application, not the current version of that organization’s information.

On top of this there is a rather robust reporting side of things which needs to get build.  So I need to be able to get to this old data rather easily. So for example they might want to know how many 501(c)3 groups in a geographic region were given grants of less than $1000 in the year 2005 - and many of those will have changed their info and submitted multiple other grant applications since the year 2005.

The entry versioning system seems like an option (and using related entries)-  but using versioning and related entries seems like a nightmare when it comes to setting up a flexible reporting system because all the old version data is just sitting as a serialized string in one table field in the DB so everything is basically in one massive pile.

Are there other options? How would you set this up?

Thanks for any thoughts…

Not sure how I’d go about it in EE or if I even could without a lot of thought but on a large eCommerce site I did we called it an “auditing” system, we had to be able to go back to any minute in time and be able to show the price and terms for any product. I can’t recall how it was done but I know it wasn’t easy.

This is probably too simple for your needs, but could it just be a weblog that only displays the most recent entry, where every time an organization changes their information, they submit a new entry to the weblog? You could then use categories, or the tags module, or sort by custom fields, or authors, or whatever, so that only one entry for one organization is displayed on the page.

I had thought about that, but I don’t think it’s going to work. There are a few problems - one is the title can’t be identical for multiple entries (although I guess you could use a different field for the actual name of the group).  Another is how does the system know that the entry is the same group - no matter what info is changed (what if the group changes its name?)

The easy part is displaying the current version of the group.  The hard part comes in with needing to develop an in-depth reporting system on top of this. If they are all different entries, how does the system know they really are one group? And then tracking what groups have certain characteristics and how those have changed over time seems like a nightmare if every group is actually multiple entries.  Once again a group needs to appear as one group over time, even though everything about it might have changed since its inception.

I really have no idea how this would best be solved. It seems like it’s either work with the system as it is (using the revisions system to hold historical data or your idea of multiple entries per group) and face huge reporting problems, or try to build modules/extensions to keep historical data somehow and have an easier time pulling out the reporting.

It seems like almost every sizable project I take on always has at least one problem I have no solution for before I start building. I wonder if that ever changes?

This is a tough one. If each “group” is set up as a separate author, then the author ID will be the one constant for each entry no matter what else changes (including the group name). Then you could create different reports by changing the variables of your entries parameters, limiting posts by author to see only a single group, or by some other variable, or custom field to see info on multiple groups. You could essentially set up a template to spit out a giant table of info sorted by group; the problem with that is for each group you would probably end up with a lot of redundant information mixed in with a few changes.

Here’s what I see as a possible solution.

First, separate organizational data from organization description data.  Basically, certain things about an organization don’t change, or at least not that frequently. Since you are providing grants, typically the 501(c)3 status details will not change that frequently. So a weblog ‘organizations’ with the title field being the organizations name, EIN, link to the 501(c)3 letter as a PDF, possibly address, but that’s debatable. 

Next weblog is org_description, this can be serialized to a degree—organization name and current date, such as 20080219 Whole Life Center could make up the description. Must have is a relation field that ties organization to org-description.  Added benefit, is that you can always display the current description, by sorting related data by descending date, and displaying one result.

Three your grants table. You have two options, directly relate to org_description, or relate to organizations with distinct date and amount fields. For the most flexibility custom queries would be needed for either method, but for typical display purposes, the second option would be easier to create one long detailed profile of an organization.

I’m assuming that orgs would update their data via a Stand-Alone Data Entry/Edit Form. If that’s true, then you’d probably want to see if you could use the edit data method to pull over the old description, but still post with a new entry. You could also handle the entry title data using a similar method, display the form title as just the organizations name, but submit the actual title via a hidden field, which includes the date and organizations name.

I am curious about one thing, technically speaking, even if an organizations description is being updated this doesn’t change the organization—which is typically while versioning is enough. Is the organization description change, directly related to the grant or the grant profile?  If so, then I would probably adjust the structure a little bit, by tying having the basic organization description stored in organizations, then changing the org_description table/weblog into a grants_application table/weblog. That way every application with org_description as related to the application is direct, and the report logic will be a bit more streamline.

This is a tough one. If each “group” is set up as a separate author, then the author ID will be the one constant for each entry no matter what else changes (including the group name). Then you could create different reports by changing the variables of your entries parameters, limiting posts by author to see only a single group, or by some other variable, or custom field to see info on multiple groups. You could essentially set up a template to spit out a giant table of info sorted by group; the problem with that is for each group you would probably end up with a lot of redundant information mixed in with a few changes.

That is an interesting idea, but I’m not sure this is do-able (well it would work but it would probably get unwieldy).  Groups can register with the site at any time, and there are thousands of groups in their current DB.  That’s a lot of authors!

We also will need to keep track of individual member registrations on the site. To turn a group into an author, wouldn’t we need to turn that group into a “member” (the equivalent of an individual) in the system?  We would then have both real members and groups in the system as members which seems like it could lead to quite a mess…

Allgood2 - You’ve got a lot of info in your post and I’m still trying to make sure I’ve got my head wrapped around it properly.  So the group name and some other info that doesn’t change often is one weblog, and that has revisions turned on (just in case?).  The other info that changes more frequently is another weblog, and this doesn’t use revisions at all, but instead just relates to the properly org in the first weblog.  I guess that gets around the problem of having revisions saved in a serialized array in one table.

In response to your question - They need to be able to pull up the state that an organization was in when they submitted a specific grant application.  So while it is technically not part of the application, it is somewhat directly related to that application. They also want to be able to run queries like ‘how many grantees grew in staff size after receiving a grant’ or ‘how many changed from ad-hoc to 501(c)3 status between the 1st grant round of 2001 and the 2nd grant round of 2006 and how many of those received grants of more than xxx’. So queries over time. Things like that (there is a lot of info and a lot of potential queries on each organization).

I’m wondering if I shouldn’t just build a module to handle groups and grants and that way I can split it into multiple tables and not lump everything into the entries tables…

Does anyone have any real-world examples of how a database is set up to track info over time? Maybe links to tutorials? I guess part of the problem is I’m not really sure how this is normally done.  Tracking the info seems easy enough, it’s getting it back out in customized reports that seems a nightmare to me.