The scheme of storing the changing data history


Warning: count(): Parameter must be an array or an object that implements Countable in /home/styllloz/code-flow.club/qa-theme/donut-theme/qa-donut-layer.php on line 274
0 like 0 dislike
36 views
There are about 300 thousand of objects ( e.g. cars) for each car once a week is measurement parameters ( mileage, tire pressure, fuel quantity), the parameters will be around 20 pieces, we need to store in the database.

In osnovnom users will be interested in only the last settings. But sometimes you need to answer questions like "how has varied the tire pressure in time," "And what parameters have been changed last week"

Instinct says probably need to look in the direction of mongo, but the assignment clearly said that will use Mysql :)

Have been born two choices

1)

The first table (called data)

id| object_name | param1 | param1_is_changed | param1_change_date | param2...

The second table (the name data_history)

id| object_name | param1 | param1_is_changed | param1_change_date | param2... | version | change_date

Each time any parameter is changed, the previous version is saved in data_history, the parameter which is changed is vlarok is_changed

2) the First table (called data)

id| object_name

The second table ( stores only the last value)

id | object_id | param_name | param_value | date

The third table ( keeps track of values from second table)


Now we are tracking about 50 thousand objects, in a week there are about 200 changes in the parameters. All parameters are numeric, so the question is redundant storage in the first case, only care about performance in the database, but not disk space. The second method seems good, but it is not very easy to implement using ORM.

Your opinion? how to design the DB? how to find an effective compromise between the database and the ease of writing applications to it.
by | 36 views

7 Answers

0 like 0 dislike
This topic has been raised. Your first model is similar to TYPE 4.
\r
Why field param1_is_changed? Need to define how the field has changed, they don't change group?
\r
Logic in the second method, yet do not see.
\r
I think it will be possible to design so that when sampling the difference and the performance was not.
by
0 like 0 dislike
A variant with RRD is not considered?
\r
A question for the reformulation of the problem statement, because by definition, RRD means the degradation of granularity of old data. But lots of buns — from a fixed database size, ending with a lot of ready-made implementations and visualization.
by
0 like 0 dislike
He used the option 2.
Not strange — very often choose the right not so simple.
Long soared with the groups and the correct order select the last data of a heap of dissimilar material.
\r
The upshot was that history is stored separately and last slice of data separately.
No problems, and operations with the main base have become easier and faster
by
0 like 0 dislike
All natural model, as I understand it, will be like this:
\r
Table 1. Vehicle (ID, Last ID Reading).
\r
Table 2. Reading (ID, Vehicle ID, Date and the measured values: Fuel, Oil, Tire Pressure, etc.).
\r
If it is not satisfied for whatever reasons, then move on to other models. So far, for me, for example, the obvious advantage of storing heterogeneous values in a single field. Yes, it's all numbers, but if you added non-numeric value will significantly change the model.
by
0 like 0 dislike
>The second method seems good, but it is not very easy to implement using ORM.
so, in mysql a long time already triggers, afaik. organize the collection of the history of triggers for insert/update/delete and display a history can already be twist as you like if dancing on a single table (or a view which Union will make current and archival data).
by
0 like 0 dislike
1) in the table. Date ID_объекта, ID_параметра, Parametervalue.
2) the Current table is similar to the first but without the dates, update trigger, or conversion for the crown.
3) a table with the dimensions split by months, engine=ARCHIVE
by
0 like 0 dislike
A three-dimensional table, a La Google BigTable
by

Related questions

0 like 0 dislike
3 answers
asked Apr 13, 2019 by Vika7
0 like 0 dislike
5 answers
asked May 4, 2019 by GilbertAmethyst
0 like 0 dislike
2 answers
0 like 0 dislike
1 answer
asked May 22, 2019 by nioterzor
110,608 questions
257,186 answers
0 comments
23,604 users