The task is to take into account that customer data may change and it is necessary to keep an archive of changes in customer data. Previously solved the problem of storing customer data

As a result, it turned out that there is basic information for all clients (full name, telephone), but there is also additional information on the types of clients. The scheme for storing customer data is shown here .

Problem: I think that you need to store only the information that has changed, not the entire data slice for the client, therefore I think that you need to add a table Archive with fields {change id, data, client id, name of the field that changed (?), OLD value}. If the AdditionFields table had all the fields including the client’s name and full name, then FieldId would have taken it. But it turns out that there are some fields in the table, customers + additional fields, and how to specify the variable field in the archive like this, when they are scattered, I can’t think of anything, it’s natural that there is no data redundancy. Share, please ideas? can somehow think differently?

  • Instead of changing the fields in the database, I would simply mark them as obsolete, then add new ones with relevant information. Thus, the entire record history is stored in the table. Instead of rewriting a new foreign key to all dependent fields, you can assign a new field with current information to the id of the outdated entry. IMHO easier and better than creating a new table with an archive of fields. You will not need to change the archive table when changing the main one. - mirypoko
  • mark as obsolete this is for example a date to add or a column with a boolean value? Ie it will be necessary to go through all the data about customers to find a customer with a current mark? and when changing for example the client's phone, do you need to take all the fields and overwrite them with new ones? with foreign keys something is not clear, i.e. In order not to add new lines, for example, in the AddatinalInfo table, you need to take a new client id and replace it in all fields that have not changed, I understand correctly? - All888
  • There is no need to sort out anything, you are accessing the field by key. Another option is to add a bool column and the time to add a record (if you need to know the time besides the order of changes). When changing a record, add a field with old data, mark it immediately as obsolete and set the old date. Then enter the actual information in the field referenced by other records. - mirypoko

0