Can you help me figure out a t-shirt pricing page/form?

[teamspirittees]

Hello, all...

 

In my spare time I make t-shirts and sweatshirts for fun and profit.  I want to build a page/function/form where I (or a visitor) could easily figure out a price quote.  I have tried to build this previously and, every time I start, my eyes glaze over.

 

I was wondering if I could get some input on how you would structure the schema and the basic approach.
 

Here are the parameters:

 

• I first have 3 products: t-shirts, hoodies and sweatpants.
• Each product has 3 different subsets -- white or neutral tees are one prioce, light or heathers are a little bit more, and darks or colors are more than that
• I order my designs from an outside company which charges different prices for a 1-color, 2-color, 3-colors, and 4-color design.
• In addition, this company has price breaks for 5 designs, 10 designs, 20 designs, etc.

To come up with my quote, I would take ((price of garment + price of design) x markup).

 

How would you build the tables in MySQL?  Would you have a table for each garment type (tee, hoodie, sweatshirt)?  Would you have a table for each color category (white, heather, color)?  What about a table for the designs -- how would you structure that?

 

Any tips, suggestions, coding examples, etc. would be appreciated.

 

Thanks,

 

Joe

 

P.S.  You can look at prices.pdf to give you a better idea of what I mean.

[davidannis]

The textbook answer is to normalize things. A table for garment type, a table for colors, a table for design. In practice, if I didn't expect the colors to change I would would combine garment type and color into a single table.

 

table: garment

fields: garment type, color, price

 

table: design

fields: number of colors, company, quantity, price

 

I don't understand when you would use company 2, since it looks more expensive in all cases.

 

If you want the textbook answer separate the first table into 3:

 

table: garment_price

fields: garment_type_code, color_code, price

 

table: garment

fields: garment_type_code, garment_description

 

table: color

fields color_code, color_description

Edited August 14, 2013 by davidannis