Let me first say that this is not LLBL specific question but more data modeling question. I am presented with a chalange of how to represent entities that change through time. For example, Employee can be part time and then at some point become a full time, have hourly rate changed or become salaried employee. Now, what is the best way to model this. I would prefer to work with one entity, that has start and end date for any change in status, and flag IsCurrent to represent currently active status. I need previous states so I can make retroactive adjustments etc. On one system that was data centric that I worked 5 years ago, we had Employee for example, and EmployeeHistory. But I don't like that approach, unless LLBL could give me back Employee entity based on the point in time. How do you guys deal with this? Thanks in advance.

I did study temporal patterns myself sometime ago, here are some resources i found useful to me:

http://www.martinfowler.com/ap2/timeNarrative.html

http://www.timeconsult.com/TemporalData/TemporalDB.html

Your case seems to be actually quite close to the examples in both of these articles, meaning your schema would end up being something like;

EmpID Name Department Salary ValidTimeStart ValidTimeEnd 10 John Research 11000 1985 1990 10 John Sales 11000 1990 1993 10 John Sales 12000 1993 INF

I have worked with temporal data myself on some projects, and there we have used the above approach as well. I also remember things getting awfully hairy when you need to make changes to the history if there (as there usually is) is dependent data related to the history data you are changing

I'd say it's better to use another table (say: EmployeeHistory)

EmployeeHistory

EmpID StatusID StartDate EndDate

• And then either add the "IsCurrent" field to the EmployeeHistory Table (simplest solution)
• Or you can add a "StatusId", "StartDate" only, fields to the Employee table to represent the current status, without including this data again in the History table, to keep the data in the history table for past records only, then the current data would be moved to the history data when the employee has a newer status. So new status gets into the Employee table, current data + endDate written to the history table.

One thing you might want to make sure of, is that you keep the time related data only in one place, you don't want to examine multiple tables when fetching data for certain time period(s). Also, if you need to make retroactive updates, it becomes even more cumbersome, you need to manipulate data in both tables. If you have complex relations and business rules in place, you need to apply it in both cases.. And when it comes to reporting it is yet another issue.

In the system I worked with (it was a huge financial app, sort of) there was both of these approaches in use, more recent tables were modeled having the time data in one table, earlier approach was to use historytables for past data and another for current data. The one which made dev team have more gray hairs was the historytable approach.

Naturally you might want to normalize the schema to not to duplicate unnecessary data such as names and so on, the sample in earlier post was just copy paste from the articles sample.

Edit:

Another issue you need to take in consideration is; how often do you access the history data, do you need to modify it a lot, update existing rows, delete history rows. If you are keeping records of history for auditing purposes only, separate history table might be the way to go. But I feel employee salary is more a complex case.

Thanks sami, both articles were great help, and helped me consider some additional problems that I have not considered.