How to design a db that will show history of data at a specific date?

Question

0.00/5 (No votes)

See more:

I must design a system for lets say a road network. There are measurements taken every 10 m on a road. This measurements is done every two years. We are looking at approx 4 million records. Not very big, but also not insignificant. Data is just text.

If a road is rebuilt, that measured data must be deleted, because it is not valid for the current state of the road. And must not be used in current calculations. This will be between a specific start and end km.

Reporting must be done on current data, but also what was the data previously.

User would just like to select a date and all the data must be seen, that was valid for that specific date.

What I have tried:

I have created audit tables that are populated with triggers. But that is difficult to select the valid data at a specific date. Remember not the whole road's measurements are changed, only a portion between a start and end km.

What design model would you suggest?

Posted 22-Jan-20 2:20am

Member 12195606

Updated 22-Jan-20 3:24am

v2

Add a Solution

Comments

Richard Deeming 22-Jan-20 12:39pm

It sounds like you're looking for Temporal Tables[^].

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2020-01-22T03:25:00

Remember not the whole road's measurements are changed, only a portion between a start and end km.

Then I would find a way of identifying each "bit" of the road - e.g. What3words[^]
Make that fixed. So if work encroaches into a neighbouring section then that 3m square is also re-measured - even if it the same as before.

I have created audit tables that are populated with triggers. But that is difficult to select the valid data at a specific date.

I don't see why that would be difficult, but it is difficult to offer help without details of those tables.

An alternative approach (used where I currently work), is to record the changes on the same table in a new row - each row has a valid-from and valid-to date ("current" record has valid-to date of 9999-12-31). That way you are not relying on your audit table(s) and can quite quickly determine which records were "current" at any particular time.

It should also be more performant because there will be no need for the triggers ... use a Stored Procedure to upsert so that you maintain the date sequence consistently.

Edit: Just noticed

Quote:
Data is just text

If these are measurements then use the appropriate data types - Dates should be stored in date or datetime column types and numerical data should be stored in a numerical column type. Apart from wasting space and being inefficient, using the wrong data type can lead to all sorts of problems that can be very difficult to diagnose and fix.