Shopping cart database design: adding functionality for quantities by size

Question

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?

Answer 1

I would never save a string or json chunk in a single product row with sizing/quantity embedded.

I would go with a product table.

Then a Junction table using the productId with a size column and a quantity column.

schema idea:

create table Products
(   prodId int auto_increment primary key,
    prodName varchar(100) not null
);

create table ProductQuantities
(   -- table to store quantities for Product/Sizing combos
    id int auto_increment primary key,
    prodId int not null,
    size varchar(10) not null,
    quantity int not null,
    constraint foreign key (prodId) references products(prodId),
    key(prodId),
    unique key (prodId,size) -- maintains sanity for no duplicates at the combo level, plus a general index
);

Granted, the 2nd table is not a junction table, but you get the idea