1

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?!

1

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.

  • Well.. that was just one possibility. Is this really the only way to go? – jes Feb 22 at 6:22
  • Like color1 and color2 in the stock table? Or a new 1->many relationship with colors? All would involve a large number of query changes to effectively deal with the change. You could comma separate them and handle that in the UI. – danblack Feb 24 at 21:17
  • hmmm..comma separate them seems like a good idea. I was really really hoping for a database scheme kind of a solution though. Thanks – jes Feb 24 at 21:24
  • kindly update the answer with the "comma separated" solution so that I can mark it as Accepted 😊 – jes Feb 27 at 5:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.