Hello - I'm working on a project with a fairly meticulous relationhip,
compared to projects I've worked on in the past at least (usually the
data relationships are simpler). What I'm asking of the community is
input on the design; if it seems sensible, if it s, if there are
other perspectives or better designs.
Here's the situation:
It's a clothing company that will sell shirts. A design is placed on a
shirt to be sold. A single design may be placed on several shirt
styles, each style has it's own set of available sizes, and each of the
style's sizes has it's own set of available colors. The price is for a
shirt is determined by the design and the style it's placed on (not
affected by size or color). So at this point I have the following
tables (and their attributes in ()) - these don't include the
association tables:

Designs (id, name)
Styles (id, name)
Sizes (id, name)
Colors (id, name)


Many designs can be on the same style, and designs can have many
styles, and the price for a single shirt is determined by the design
and style. So I need an association table that includes the price:

Designs_Styles (design_id, style_id, price)
Composite Primary Key: design_id, style_id


A single row in that table represents a sellable shirt. Each of these
has a set of available sizes, so I need another association table that
relates a set of sizes to a design-style combiniation:

Designs_Styles _Sizes (design_id, style_id, size_id)
Composite Primary Key: design_id, style_id, size_id


Each association in that table, which reperesents a size of a
design-style combo, has it's own set of available colors. I need yet
another association table that relates a set of colors to a
design-style-size combiniation:

Designs_Styles _Sizes_Colors (design_id, style_id, size_id, color_id)
Composite Primary Key: design_id, style_id, size_id, color_id


I would apperciate any input on this design - good/bad/theoretical.
Thanks!