The concept behind effective dating is that a table can contain multiple history rows and the distinguishing factor between the rows for a constituent ID is the effective dated field value -- a date/time stamp that is unalterable except through some correction-mode facility. When on a page displaying information about a given constituent, only the most recent row of data is displayed, but there would be an option to display all the history rows, newest to oldest. Note, the history rows would not be alterable except in highly-privileged correction mode.If the current, most recent row needs to have an element changed, the current row is copied, and the changed data row is saved out as a new most recent record with that moment of change's date/time stamp recorded.
Example: Without effective dating, as currently is the case, we rely upon the "type" field (nametype, addrtype, phntype, coretype, reltype, etc) and L to help maintain uniqueness. But, what if you wanted, say, the last five or more preferred, good, home addresses for the constituent? Or, maybe you wanted to have the history of their preferred names? Or, you needed to change your chart of accounts because your Financials ERP is (or has) changed, or you need to discontinue using one account number and start using another account number (feeds to the Financials ERP) but for reporting purposes you CANNOT change the account numbers themselves and need to continue using the same chart_code?
Conversely, if effective dating were available (and, I am not talking about the usage of gifteffdat in the Gifts table because its business practice is tied to tax related purposes at end of fiscal / calendar year when a gift needs to be back-dated in difference to when it is actually posted), you would be able to keep track of the changes to someone's name, addresses used, relationships, your institution's department / degree names within the Schools set of tables, etc.
Having worked with other ERPs (Enterprise Resource Planning systems) since the late 1990s, this has been a constant source of frustration at my organization. Yes, this is a fundamental design change on all data and lookup (translate) tables. It's a paradigm-shift in data architecture / design and business process logic.
I'm open for giving more details on how effective dating might help with display, data entry, reporting, etc.
I hope people might see the value in this proposed enhancement. I welcome discussion about it.
Stuart Fermenick, Sr. DBA, Claremont Graduate University, Claremont, California. MS-IST, soon to be EMBA.