Hi,

i am developing a clothing order management database.

I have so far got [order details], [products], [product details] entities.
This is so that products with the same model code can have a selection of colours and sizes which are held in the product details table.

I followed this website which was very helpful: http://www.princeton.edu/~rcurtis/ul...database2.html

The question is now: each size and colour will have a different stock quantity. How do i model this, for example, so when a Small Red t-shirt is added, the small red t-shirts quantity decreases rather than the overall quantity for the model.

any ideas?

Problem: Similar items different only in details, tracking quantity on hand at detail level.

Solution: The number you track in transactions has to be the detail number (or) has to contain the qualifier that makes stock item #12345 (t-shirt with no pocket) a small red t-shirt. Like, compound key #12345-SmRd, and #12345-XlBlu would be the extra-large blue t-shirt of the same general model.

Another way of looking at this is...

The "Real" inventory item is in [product details] and the stuff in [product] is just a generic lookup table that describes many products of similar styles. I.e. [Product] ISN'T THE PRODUCT. [Product Detail] is the product.

You would probably use [Product] to identify suppliers, though maybe not. You would probably tie your order details to your product details, not to the product table. The link between order details and product details would be the implied transitive link from product details to product.