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
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
>In both cases i need to keep history.
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.
DCRAPACCESSAuthor Commented:
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:

Content sample
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?
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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.
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

DCRAPACCESSAuthor Commented:
Í 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?
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>how could a be sure that the dates don't overlap for that Unit?
that business logic would not change from what you have today, except that it would be in another table...

in SQL, to check if 2 records are overlapping:
SELECT * FROM yourtable T
WHERE EXISTS ( SELECT NULL FROM yourtable o WHERE o.startdate <= t.enddate AND o.enddate >= t.startdate )

Open in new window

this is not possible using a simple constraint.

>And how would that be different from the structure I have today?
as I said: in regards to those FTE fields : nothing
in regards to the entity design of the other fields: you would follow normalization rules

as I don't know if you are actually aware of what that is, and what it is for:

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DCRAPACCESSAuthor Commented:
This was the answer I was expecting. Thanks for you time.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.