I need help to verify my solution or get help how to make the correct solution.
Look at the sample below:
I have two tables Unit and Department.
Each of them have a ID column with a incremental number (Autonumber).
Unit also have a Unit_id and Department have a Department_id.
In the Department table you will have the foreign key Unit_id.
In both cases i need to keep history.
So the Unit table could have a Unit_id=1 and a Start_date=01-01-2013 and End_date=31-12-2015.
And another record with Unit_id=1 and a Start_date=01-01-2016 and End_date=31-12-2100.
The same logic have been used in the Department table.
The reason for that solution is that we should be able to keep historical changes and in our reporting we need to calculate backwards in time and all changes should have effect.
I'm not sure if this descrition is enough to understand what I need the table structure to support. Is this the right way to handles this requirement and if it is, is it possible to make constraints so the same Unit_id and Start_Date and End_Date are not overlapping?
thanks for your help in advance