Jump to content
Larry Ullman's Book Forums

Designing Database For Same Product With Multiple Size And Colors


Recommended Posts

I have gone through the book  back to back and I found the tips and trciks very useful. However, I would like to develop further on the example give in the second site.

 

I have a situation where a product has many sizes (different sizes with different stock values). The database structure and stored procedure described in the example is certainly not applicable here.

 

I am thinking of creating a join table, removing the stock columm from the product table and add it to this join table but I am not sure how to write stored procedures that fetches same product, diffrent sizes and stock. I would appreciate if someone could help out.

 

 

Link to comment
Share on other sites

Hello Ojo-sama, and welcome to the forums.

 

It's hard to offer advice without more information, but I can offer the following pointers:

  • You should have one table that contains all your product info, minus the stock info, as you described above.
  • You should then have a second table that contains the product ID as a foreign key, and then the size and stock information. Please note that this table will likely contain the same product ID multiple times. In other words, every size/stock combo for a given product will have the same product ID foreign key.
  • You will then use a query similar to the following to get the size/stock info about a product:
SELECT * FROM products, product_sizes_and_stock WHERE products.id = product_sizes_and_stock.product_id AND products.id = ?;

Given the nature of your question, it seems like you might need some more knowledge on database normalization and how to perform joins before you go much further, but that's the basic idea.

If you have any further questions, please don't hesitate to ask.

Thanks.

  • Upvote 1
Link to comment
Share on other sites

 Share

×
×
  • Create New...