I currently have a simple php based shopping cart but I need to add a new functionality so that each size has a unique quantity. There may be 1 large, but 3 smalls in stock for example.
Each product has a unique product code, but has multiple sizes and colors. Currently there is a quantity field in the table. I also need to be able to decrement the quantities once a product has been ordered.
For flexibility, I currently reference the options as a single field in the database entry that's a json object and looks something like this:
{"colors":"Red,Blue","sizes":"S,M,L"}
This gives me the flexibility to use multiple options of different names without having to create an options table in the db.
I could simply expand the json object to something like this:
{"colors":"Red,Blue","sizes":{"S":"1","M":"3","L":"0"}}
I'm worried this will cause me unforseen issues down the road. I'm also concerned that by doing this decrementing after a sale becomes more complex.
Currently, I just have to update where the product code matches and decrement the quantity. With the above change, I have to retrieve the product info, decode the options, find the size and decrement, then update the database. This feels overly complex.
Is there a better way of handling product quantities by sizes?