I’m in the process of designing a schema in our MySQL database to properly link products and product components. This is fairly straightforward using a product and product_components table design. However, there is one challenge I’m facing in the design.

This challenge relates to the fact that we have many different products that are interchangeable. To provide context DVD sets are one of our products. For something like “Chronicles of Narnia 1-3”, we may have several instances of the 1st film in our product table. These products may have little to no physical difference but will have different UPCs.

Because these products are the same our warehouse treats them as essentially the same item when we receive them. I’ve created two tables inventory_sync_group and inventory_sync_group_products to allow us to identify and properly track inventory for products like this.

I’ll provide a picture of an example of real data in my proposed schema. Please note in particular the Chronicles of Narnia (1st Film) records in the product table and how they are associated with an inventory_sync_group.

My main challenge is determining how best to define a product as a product_component if it is interchangeable with various other products, or in other words, if it is associated with an inventory_sync_group.