AnsweredAssumed Answered

Data management: To store data or derive it? ... that is the question

Question asked by pasleeth on Sep 9, 2016
Latest reply on Sep 10, 2016 by philmodjunk

For the theory wizards among you, How do you decide when to keep numbers solely within the confines of a calculation field as "derived" information, and when it's preferrable to give that data a permanent home within a database?  Here's the real-world example I'm wrestling with, which occurs within the context of student records.


A key statistic of interest in higher ed is the time it takes for students to complete their degrees, something typically reported as a decimal number in units of years.  For the purpose of my calculation, a student's starting term and graduation term are assigned standardized month/day dates (year is variable, of course) that approximate when semesters -- fall, spring, and summer -- usually begin and end.  (Scripting actual semester beginning and end dates is out of the question.)  Computing completion time is simple math ... but here's the storage rub.


1)  If term/semester values are already being stored in lookup tables as four-digit integer codes (CYYM), does it make more sense to store the nominal start and end dates as part of the lookup table rather than calculating them individually on auto-entry for each student record?


2)  For the purpose of computing elapsed days, does it make more sense to create two fields, one for start date and one for end date, in which to store those values for a student, in addition to having the calculation field for completion time that uses those two dates?  Or instead, is there a way to write the calculation so that the terms already being stored in the student record as 4-digit codes (CYYM) are converted into dates/numbers using the lookup tables, fed into the math, and then storing only the resulting elapsed years and nothing else?


3)  And lastly, wherever start/end dates are stored -- if they are stored anywhere -- is it more efficient to have FileMaker store them as numbers using the GetAsNumber function rather than as calendar dates, considering that the actual dates themselves don't ever have to be displayed/output anywhere, only the term codes, which are already part of the visibly displayed record?


-- Andrew