This is a discussion on suggestions on schema within the MySQL Database forums, part of the Database Forums category; Hello - I'm working on a project with a fairly meticulous relationhip, compared to projects I've worked on in ...
|
|||||||
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read |
|
|||
|
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 sucks, 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! |
![]() |
| Thread Tools | |
| Display Modes | |
|
|