suggestions on schema

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 ...


Go Back   Usenet Forums > Database Forums > MySQL Database

FAQ Members List Calendar Search Today's Posts Mark Forums Read
  #1 (permalink)  
Old 09-01-2006
chrislewis
 
Posts: n/a
Default suggestions on schema

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!

Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are Off
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 09:25 AM.


Powered by vBulletin® Version 3.6.8
Copyright ©2000 - 2008, Jelsoft Enterprises Ltd.
Content Relevant URLs by vBSEO 3.0.0