so I was looking some design schemes for storing a Product's quantity of a certain color and variant sizes, @Gilbert Le Blanc gives the perfect scheme for me as answered in this question, and the scheme goes as follows:
Product table.
Product
id product
------ ------------
0 Nike Shirt
Color table
Color
id color
------ -------
0 black
1 white
2 blue
Size table
Size
id size
------ -----
0 XS
1 S
2 M
3 L
4 XL
5 XXL
Stock table
Stock
id product color size quantity
---------------------------------------------
0 0 0 2 5
1 0 1 3 10
2 0 2 2 2
3 0 2 4 3
This works perfectly well, my issue is: some times a product(e.g. black and white patterned T-shirt) can be categorized as black as well as white!! so we will end up with multiple records in Stock table for the same product! -see tables below for more clearance-
Product
id product
------ ------------
0 patterned T-Shirt
Color
id color
------ -------
0 black
1 white
Stock
id product color size quantity
---------------------------------------------
0 0 0 2 5
1 0 1 2 5
This will causes table Stock to be inaccurate in terms of quantity, I need the product to have more than one color if need for sorting purposes in the UI(user-end/Front-end) and I also want to keep track of the quantity of each color along with the sizes.
I am thinking of creating a separate table that includes the product along with all colors that it can be categorized in. this table will be meant for sorting purposes only. this is a great solution except that it will require more data-entry.
Is there any other way around?!
Best Answer
Add to the color table 'black,white' as this is predominately a stock control system.
This comma separation can be handled in the UI to display in a friendly way.