Geek asks and answers

Database Design: How to Track History?

What are the general strategies in designing a database to keep track of change history? If this was only one table that I was dealing with, I think it would not be so difficult. Just save each update as a new entry in the table. The last entry will always be the latest version.

But when data is stored on multiple tables, what's a good way to create this to track changes?

+5
source share
5 answers

I prefer to have an additional historical table for each version of the table. The same structure as the main table with additional fields time_fromand time_to. Transparently filled with triggers. time_toThe latest version installed in the distant future.

The state for the specified moment can be restored with the request as follows:

SELECT * FROM user_history 
WHERE time_from >= '2012-02-01' AND time_to <= '2012-02-01'

As for me, storing the history in the main table is usually not a good idea, since it requires difficult conditions when extracting or combining current data.

+2
source

Turn on MySQL binary logging and just use it.

0
source

, , , 1 , , . :

  • _HISTORY ;
  • 2 , start_dt end_dt, ;
  • start_dt NOT NULL, end_dt NULL, , ;
  • , , 1/Jan-2013, end_dt 31/Dec-2012 23:59:59 start_dt of 1/Jan-2013 00:00:00;
  • revision, .

RI , 2 . , Customer obejct :

customer (customer_id INTEGER, PRIMARY KEY (customer_id));
customer_history (customer_id INTEGER, start_dt TIMESTAMP, end_dt TIMESTAMP,
                  name VARCHAR(50), sex CHAR(1), ...,
                  PRIMARY KEY (customer_id, start_dt));
customer_bank_history (customer_id INTEGER, start_dt TIMESTAMP, end_dt TIMESTAMP,
                       bank_id INTEGER, iban VARCHAR(34));

customer(customer_id) . :

SELECT c.customer_id, ch.name, ch.sex
  FROM customer c
  JOIN customer_history ch ON c.customer_id = ch.customer_id
       AND now() BETWEEN ch.start_dt AND coalesce(end_dt, now());

​​:

  • ;
  • ;
  • , - / ;
  • .

, .

0

"" - , .

.

, . , "" , .

0

Datadiff. DB API.

:

Datadiff. , MongoDB SASS. SQL.

key:val. i.e id:123

0

More articles:


All Articles