DCRAPACCESS
asked on
SQL correct table structure
Hi Experts,
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
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
ASKER
Hi, and thanks for your feedback.
Sorry if that was not clear enough.
I need to know for example if the FTE number has changed like ín this picture, from what period a certain FTE was used:
About the calculation, I should be able to use the FTE number on a daily basis. So if i make a report of some kind with for example a graph. I should be able to illustrate on what specific date the FTE number changed.
And if i go in and change the FTE number 360 to 365 the graph should show that instead of 360.
So when I extracts data i get something like this:
The database structure is much more complex. But I would like to know if what I have done I totally wrong or not? And if it is how would you make the table structure? To support multiple Start and End dates for a record and be able to work with all changes afterwards?
Sorry if that was not clear enough.
I need to know for example if the FTE number has changed like ín this picture, from what period a certain FTE was used:
About the calculation, I should be able to use the FTE number on a daily basis. So if i make a report of some kind with for example a graph. I should be able to illustrate on what specific date the FTE number changed.
And if i go in and change the FTE number 360 to 365 the graph should show that instead of 360.
So when I extracts data i get something like this:
The database structure is much more complex. But I would like to know if what I have done I totally wrong or not? And if it is how would you make the table structure? To support multiple Start and End dates for a record and be able to work with all changes afterwards?
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
it's not normalized, as you are repeating a lot of data for each of the Unit/Department rows, which are not relevant to the FTE value and the changes of them.
The unit does not change it's name, so you should keep that 1 record, and put the FTE history/changes into it's own dedicated table.
you may there consider to have 1 record per unit and per day, depending on the final reporting needs.
in short: whatever works, so far the design is wasting data space.
you may not go too far in the normalization, but right now you are at the beginning.
The unit does not change it's name, so you should keep that 1 record, and put the FTE history/changes into it's own dedicated table.
you may there consider to have 1 record per unit and per day, depending on the final reporting needs.
in short: whatever works, so far the design is wasting data space.
you may not go too far in the normalization, but right now you are at the beginning.
ASKER
Í understand what you are saying. The last picture was a quick view to illustrate a dummy sample of the output.
I in this case it would only be two records in the Unit table and 3 records in the Department table. So that does not seem like a waste of space???
The view was just to show that the information in the tables could be used to show the FTE number on each day in a period of time.
If i took out all fields of the Unit table that could change (it is more than the FTE number that can change) and put it into another table with the Start and End date, how could a be sure that the dates don't overlap for that Unit? And how would that be different from the structure I have today?
I in this case it would only be two records in the Unit table and 3 records in the Department table. So that does not seem like a waste of space???
The view was just to show that the information in the tables could be used to show the FTE number on each day in a period of time.
If i took out all fields of the Unit table that could change (it is more than the FTE number that can change) and put it into another table with the Start and End date, how could a be sure that the dates don't overlap for that Unit? And how would that be different from the structure I have today?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This was the answer I was expecting. Thanks for you time.
history of "what" and "why". you speak about going back in history and calculate... so far, in the columns, I don't see anything to calculate.
hence, you should rather have a table with the data (and without the time fields), and another one with the historical values (and who changed etc ...)
that way, you have 1 record per unit and per department in the main table (with a Primary Key there), so you can refer to them as entity in other tables also.