How to create a table to store the history of changes?

Question

Database design

The table has a column with a price, the price changes over time. It is necessary to make a table that would store the history of price changes. That is, in the table with history there should be columns with the date of change and the price for which they changed.

Answer 1

Probably the most optimal way is to make a field with time changes. If it is filled, then this is history, if not filled, then the current state. In order not to complicate queries, create triggers for selection, update/delete, that perform operations with this field, but for the user it seems to be non-existent, except for specific questions about the history. And, of course, set up table partitioning so that the current and history are physically stored separately.

Answer 2

look towards temporal tables: https://docs.microsoft.com/ru-ru/sql/relational-da...