Advice on handling product variables combined with stock control...

Our client has a product, a light bulb, with product code LWP0001. It is, by default, Cool White colour and Non-Dimmable. It has a colour variable and a dimmable variable so...

LWP0001 Cool White + Non-Dimmable
LWP0002 Warm White + Non-Dimmable
LWP0003 Cool White + Dimmable
LWP0004 Warm White + Dimmable

On their website, however, they only want LWP0001 displayed and drop-down menu's for the colour and dimmable variables. At the same time, they want to have a stock/inventory control system in place that updates as purchases are made so the system needs to log the additional product codes however, as it stands, when a customer adds LWP0001 to their cart, regardless of the variables they choose, it's LWP0001 that's purchased.

My personal view is that they'll need to have each product code as a separate record in the product table, if they want to monitor stock, but I'm not clear how I can convert the variables drop-downs to update the product code depending on what variables are chosen.

I would appreciate advice on how best to handle this scenario.
Thank you
NJ

Since the stock is related to the Product / options that are ordered, you should have a separate table to track inventory.

you would need to have a separate record for each product / option combination and on purchase do a look up of the product / option combination to update the inventory.

We can help implement the table and inventory update script in a premiere support appointment.

I already have a separate record for each product / option combination in my main product table (although the variables are hidden from the website) but my client doesn't want them all listed on the website because it will look like, taking the above as an example, 4 of the same product (the product photos are identical). Is it the look up function that solves that issue?

So our inventory table would be as simple as:

ID Product Code Stock
1 LWP0001 5
2 LWP0002 12

...and we'd only need the separate record for each product in the inventory table?

If you can suggest the fields I'll need for the inventory table I can make sure that's set up and in place for tomorrow as I already have a Premier support appointment booked with Ray for 9:30am.

Could you also pass this on to Ray as a requirement for that appointment?

Thank you
NJ

To be honest, these are all questions that should be asked at the time of the appointment. the implementation of the inventory table and the look are all part of the development of the inventory system. If you already have am appointment set up for this, then you should work with ray on these questions during the appointment.