Designing new database to keep history

Topic

New Reply

Hello…I am trying to develop a database that tracks position numbers, names, and other fields in a database and I am having a hard time figuring out how to set up the database. What I want to do is assign the primary key to a position number, but this position number could change. I also need to keep the names of all the employees that have been assigned to a position number. For example position #1 had John from January 2014 to October 2014 and now position number #1 has Jason. I also need to track when a position number changes. For example position #1 became position number #15. I want a trail that can tell me that #15 used to be position #1. What is the most logical way to set this up in Access? Thank you in advance for all your help.

Reply | Quote

Replies

Hi Seba,
My immediate impression is that position number is too fluid to be a viable primary key. Instead, the primary key for position should probably be an immutable position_ID that the user might not ordinarily need to see. Whenever a given position_ID would be assigned a different position number, a tuple consisting (at minimum) of position_ID, position_number, and effective_timestamp could be entered into a Position Number History table. Analogously, there could be a table consisting of (at minimum) position_ID, employee_ID, and effective_timestamp, to which a row would be inserted whenever an employee were assigned a different position (but not if the change were merely to the position_number associated with the employee’s present position_ID).

Presenting the data as if they were keyed by position_number rather than position_ID would require creating “views” (or whatever they’re called in Access) that would join tables by position_ID and involve other criteria (such as the most recent row in Position Number History for the given position_number).

Hope this helps,
Dave

Reply | Quote

Thanks. I will give it a try.

Reply | Quote