Basic mysql version control?

Question

We have a shopping basket as shown in the image below. The setup works well, except for one fatal flaw. If you place an order, the order is associated with the product, so if I update the product after purchasing the product, I have no way to show what you want the product to look like when you bought it (including the price). This means that we need to manage versions.

At the moment, my plan is that when a new product or option has been created or an existing one edited, duplicate the product or option in the database. When making a purchase, link the order to the version, not the product.

This seems pretty simple, except that I see that the only things that we don’t need for the version are the categories (since no one cares about which categories were in it). Therefore, we need to run the version:

• Products
• Options
• Key pair → attribute values ​​for each version
• Images

My modern thinking:

Note: When creating a product, a default option is created that cannot be deleted.

• When the product is created
  • Insert the product into the product table.
  • Create default option
  • Duplicate the product in the products_versions table
    • Replace current identifier column with product_id column
    • Add id column
  • Duplicate option in option table
    • Replace current id column with variant_id column
    • Add id column
    • Replace product_id column with product_version_id column

---

• When the product is edited
  • Update the product in the product table.
  • Duplicate the product in the products_versions table
    • Replace current identifier column with product_id column
    • Add id column
  • Duplicate all product variants in varians_versions table
    • Replace current id column with variant_id column
    • Add id column
    • Replace product_id column with product_version_id column
  • Duplicate all options_image_links in table variant_Image_link_version
    • Replace the current variant_id column with the variant_version_id column

---

• When option added
  • Add an option to the options table.
  • Duplicate the product in the products_versions table
    • Replace current identifier column with product_id column
    • Add id column
  • Duplicate all product variants in varians_versions table
    • Replace current id column with variant_id column
    • Add id column
    • Replace product_id column with product_version_id column

---

• When editing an option
  • Update the option in the options table.
  • Duplicate the product in the products_versions table
    • Replace current identifier column with product_id column
    • Add id column
  • Duplicate all product variants in varians_versions table
    • Replace current id column with variant_id column
    • Add id column
    • Replace product_id column with product_version_id column
  • Duplicate all options_image_links in table variant_Image_link_version
    • Replace the current variant_id column with the variant_version_id column

So the final structure looks like Full Size

Now it all seems great, except that it looks like a lot of duplicate data, for example. if we update the product, we duplicate the options, even if they have not been updated since they were added. It also seems like a lot of work.

Is there a better way to do this?

Answer 1

You can do what ERP systems do (and possibly Payroll as well): Add a start and end date / time . So that...

• option and prices match their product based on common dates.
• all queries are launched by default on the current date, and the joins between each table should also take into account the date / intersection date ranges. parent_start_date <= child_start_date AND parent_end_date >= child_end_date
• In each case, you will get duplicate rows for each change in price or option, but then you will not need to update as many records (for example, option identifiers) when the product price changes.
• Valid dates must be provided. PS: Use the maximum date of your system for the end time and the time of the most recent / last record.

Btw, some related questions on the same line:

• Ways to implement data versioning in MongoDB
• Ways to implement data versioning in PostreSQL
• Ways to implement data versioning in Kassandra
• MySQL string versioning

Answer 2

Another approach to this would be to never edit or delete your data, but create new data. In SQL terms, the only operations you perform in your tables are INSERT and SELECT.

To accomplish what you want, the following columns are needed for each table:

• version_id - this will be your main key.
• id - this will be what supports the versions of your object together (for example, to find all product versions, SELECT * FROM products WHERE id =?)
• CREATION_DATE
• is_active - you do not delete anything, so you need to mark (logically) get rid of the data

With this, here is what your product table looks like:

 CREATE TABLE products ( version_id CHAR(8) NOT NULL PRIMARY KEY, id INTEGER NOT NULL, creation_date TIMESTAMP NOT NULL DEFAULT NOW(), is_active BOOLEAN DEFAULT true, name VARCHAR(1024) NOT NULL, price INTEGER NOT NULL ); CREATE TABLE variants ( version_id CHAR(8) NOT NULL PRIMARY KEY, id INTEGER NOT NULL, creation_date TIMESTAMP NOT NULL DEFAULT NOW(), is_active BOOLEAN DEFAULT true, product_version_id CHAR(8) NOT NULL, price INTEGER NOT NULL, override_price INTEGER NOT NULL, FOREIGN KEY (product_version_id) REFERENCES products(version_id) ); 

Now to insert into both tables

• Create a unique version of id (there are several strategies for this: you need to use a database sequence or use MySQL ant AUTO_INCREMENT).
• Generate an identifier. This identifier is consistent for all versions of the product.

To update a row in a table, you need to insert the entire graph, for example. To update a product, you need to insert a new product and new options. (There are many opportunities for optimization, but the easiest way to start is with an unoptimized solution.)

For example, to update a product

• Create a unique version_id file
• Use the same identifier
• Insert new product options. The options will be the same as those associated with the previous version of the product you are updating, except that product_version_id will be different.

This principle can apply to all your tables.

To find the latest version of a product, you need to use the create_date column to get the product that was recently created.

This model will use more space, but I think it can be a fair compromise, given its simplicity: there is only INSERT and SELECT, and the data is never mutated.