General Design Question
FMP Advanced v12.0v5, Mac OS X 10.9. Windows 8
One of the purposes of my application is to track donations made to a local charity. The primary tables are (1) DONORS (donor_id, name, address, city, etc.) and (2) related DONATIONS (donor_id, date, amount, type, etc.). The charity administrators would also like to have historical data available. Therefore, I need to provide a place to store or calculate monthly and annual totals over a period of two or three years for each donor. And, I have to provide that capability into the future.
My first inclination is to create another related table called HISTORY which would include fields: donor_id, sepTotal, octTotal, novTotal, decTotal, 2013Total, etc.
My first question is: Is the additional table a good plan? Or, would it be better to add these historical fields right on to the exiting DONOR table? This historical data will not be used extensively; but, of course, it needs to be accurate and readily available.
My other questions are about defining these historical fields; but that can wait.