How would this lookup table example work?

Question

Say I have a products table that stores off the shelf products that are not customizable and have static pricing. I also have 1 product in this same table that is customizable and it's price and all sorts of characteristics vary based on the configuration. The final price for this customizable product is based on an equation, not a sum of individual configuration options.

I understand I need a products table and a lookup table for each configuration attribute for this customizable product. But, I only want to store 1 item in the product table to identify this customizable product. I want the frontend to be able to just populate dropdown boxes based on the lookup table data (just a fetchAll and render all options).

My confusion is, how do I have a relation between this customized product in the product table and its children lookup tables. Do I have some column in the product table that says "this item needs lookup tables"? Very stumped on this.

Thanks in advance!

Answer 1

There are a number of different ways to go about this as I am sure you already realize.

I would suggest something to the idea of:

Have a table for products. All basic product information directly related to that product would go in there.

|productID|productName|productDescription|productPrice|....
|INT      |Varcar     |Text              |Double      |....

Create a table for configurations. This would be something to the effect of:

|configID|configName|configDescription|configType|...
|INT     |Varcar    |Text             |Enum      |...

Then create a joining table

|configID  |productID  |....
|FK: config|FK: product|....

The idea is that you create configurations then add them to the join. This allows existing products to get configurations, and to add and remove configurations as needed.