I have general question. I would like to versioning of table records. Typically you need in order remember product and his features (especially price). Commonly is price save within order item. But it is not good solution. Basically you can have much more reasons to remember features of table record. What is the best solution to remember version of records. I have two ideas:
Each table has table name tableName_log and every saving record in tableName, save origin to tableName_log, if is record changed. Problem is with foreign keys in tableName. I solved it by create column data in tableName_log where is array of objects from foreign keys encoded to json. It is very dificcult to manage.
Primary key of record is composite from ID and number of version (two columns). Actual is last version. Old versions is necessary mark by flag (eg. active = false) to possibility obtain all products (just last versions) from the table. It has some other problems.
Which way is better or is some other way to solve it more effectively?
Edit
That solution is something between my first and second example. But when in table of history is some ID (some foreign key), you will store just that ID, but not all record belong to that ID. Really is not better solution?
