AnsweredAssumed Answered

Which approach to having multi-year data?

Question asked by njem on Nov 18, 2014
Latest reply on Nov 21, 2014 by Mike_Mitchell

Which approach to having multi-year data?



I have what has grown to be a rather complex db. It is for a non-profit that tracks fundraising events and donors and participants. Some events get complex with all the details to track. The structure I inherited just has current year data available. Previous years' data for a participant, or donor, or a company that's helping, is moved to separate fields and tables at the end of the year and is somewhat orphaned there.



For the most part workers need to see current year data but sometimes they need to refer to previous years' data. Participants, too, see their account by web and need to see their previous data.



I could mix all the data together with year fields to separate them. So when we just want to see this (coming) year's data we filter to records marked 2015. That would require going through an enormous array of relationships, many that are many levels deep, and finding everyplace where the relationship would now need to be filtered. (I.e. a relationship that used to be a simple "donor ID = donation ID" would now have to have AND "yr = 2015") I suppose the filter would be based on some global so it could filter for "2015" or "2014" or ">2000<2016". I would worry that so much filtering of so many relationships would slow things down or create other complications.



Another option is to leave previous years' data in sort of a separate world, separate tables and/or separate fields. The data would be a little less integrated. To view previous years would require a different layout or tab. Summary fields would be a little more complicated (how many donations has a donor given across years) but that could be worked out.



There may be other methods I'm not thinking of. There must be good methods of doing this in really huge DBs beyond what I'm used to working on.



Any thoughts on the pluses and minuses of different methods, or other methods? Or white papers I haven't found or such?