What are best practices for storing many iterations of a product in an RDBMS? We are building a tool to track the prices of products over time, and using Postgres as our RDBMS. It is important that product attributes can be changed, and that the history of an product's attributes be preserved forever. Here is a schema we designed based on OpenStreetMap's internal schema:

We have a 'products' table on the left storing every version of every product, and a 'current_products' table on the right storing only the most recent version of each product. Every time we want to change a store, we:

create an entry in changesets
read the latest entry of the product in 'products', increment version by one, and create another entry with the changes
delete the corresponding entry in 'current_products' and create a new one with the changes and the latest version number from 'products'

We want to enforce as many business rules in the database engine as possible rather than relying on our software to keep things consistent, and this schema feels pretty &quot;off&quot;, so we welcome any suggestions. Thanks in advance!

Edit:
Revised the schema based a response from @bbaird . Also decided to include versioning of stores and users. Tied products, stores, and users together with price table.
It should work. But looking on your diagram I think you could merge products with product_changeset as you could have for every product version info who and when changed that. You could also consider to avoid to have dedicated table current_product and instead use some kind of flag (active or timestamp on main products table). There is lot of possibilities, but best solution needs to think about:

how many products do you expect to have?
how often such changes can be made?
do you want to change any attribute or only to specific ones?
what is usage of this product history? Do you need to be able to revert to some point of history? Or do you want to show it somewhere to the user? Or is just it should be in db just for some audits, just as kind of logs?


added as a response to comment, it doesn't allow for such long ones :)
So I see two solutions here:

your original one with that change that current_products should provide unique product id which would be then referenced by products directly. I think you could change relation of product_changesets, and connect that
to primary key of products. This seems like less used entity.

+ better queries for only current versions of product - which seems to be major of your needs
- more complicated when new version is provided
- more complicated when you need to revert to some previous version
- risk of desynchronize tables - you can have different actual versions in both tables; which one should be a source of truth?


alternatively you could have one table with immutable values (like product id or similar) which would be main table in fact for products. Then there could be kind of product_history or product_versions which would
have relation to main product table and would have all attributes plus some flag (like active) which would declaratively say that this only row should be considered as valid one.

+ easier than previously to add new version - you need only to have some trigger or similar to update flag of previous version, and some default on this flag, so modifying version would be only adding new rows in fact
+ no risk that you will have different values for current version in separate tables
- slightly more complicated standard queries - you need to take product by, say, its id and this active column - you can have view for that
- there is risk that after many years for many changes, this table will be quite big; so maybe you could store only previous version, and previous ones somehow remove?



In any case, you have just to connect this price_entries tables like you have already on diagram - using main unique id of product. Then, again, you have figure out how to say that specific entry is the current one - similarly like above you can
try to reflect that by some current_price entity, but it seems like overcomplicating. There some active columns seems like doing a job. Do you need to connect specific price entry with specific version of product?
And please make sure that products attributes are kind of lookups (with dedicated tables), not free text fields, as it easily brings a mess to db.