I was looking for ideas on how to design a database so that reports could use time-sensitive lookup information. Our production and quality configurations change regularly; an employee changes departments, a department gets a new supervisor, a task is moved from one process to another, a product's attribute must meet a higher standard, an attribute is checked during a different inspection. I would like a scheme where the production and quality data is matched to the configuration information based on the date of the production or inspection. Every other application that I've seen implement an idea like this the current configuration data is simply written into the data; so instead of a record that says employee #12 produced 100 pieces during task #23 on 12/1/09, the 'flat' record includes the names of the supervisor and department at the time.
I have a working design but it is difficult to use and a bit slow.