Designing a shared database that required yearly revisions
I'm currently working on a project for a client that needs to store products in a database (barcode, size, name etc).
The database will serve 2 sites (a consumer site and a trade site). The products will be identical for half the year but in September some or all the products on the trade site get updated and new products added. In January, the consumer site will again match the products on the trade site until September when the trade site receives updates again (and so on ad infinitum).
My idea is to have a single products table that stores all product information. I would then have a table that has the id's of all the consumer products and a table for the id's of the trade products (called consumer_products and trade_products respectively).
Is this the best way to setup the table:-
- As I've described above with one master product table and create a new product in the product table when a change is made and use some kind of revisioning system and update the reference from the consumer_product and trade_product tables
- Have a separate table entirely from trade products and consumer products