Re: How to implement historical data with this EERD?

[Robert Wolf]

While implementing historical data in your MySQL database presents challenges, there are still effective options to consider. Here's a breakdown of two potential approaches:

Separate History Tables (Denormalization): This strategy involves creating dedicated history tables for each entity, like SupplierHistory and CustomerHistory. These tables would store the entity's ID along with the historical data for each change. This approach might introduce some data redundancy, but it simplifies querying historical information.

Hybrid Approach: You can combine the "isActive" strategy with separate history tables. Here, you would flag inactive rows in the main tables (Supplier, Customer) to indicate outdated information. Then, store the historical details, including address changes, in the corresponding history tables. This hybrid approach offers a balance between maintaining data integrity in your main tables and effectively tracking historical information.