W
W
wawa2018-03-11 16:18:26
Database design
wawa, 2018-03-11 16:18:26

What is the optimal way to store the historical data of table records?

There is a table

CREATE TABLE entity (
id int PRIMARY KEY,
a text,
b text,
c text); -- полей больше, но не суть

The entries in this table are mostly updated.
The task is to store the history of changes in the records of the entity table along with the time of these changes, taking into account the fact that the fields are updated at different intervals: a - hour, b - day, c - week.
Alternatively, create a table
CREATE TABLE entity_history (
id int REFERENCES entity(id),
a text,
b text,
c text,
changed_at timestamp
);

But this is a very memory inefficient option.
Can create a separate table for each group of fields, combined by update frequency. But there will be too many tables entity_a_history, entity_b_history ... - it looks somehow crooked.
How to make sense?

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question