I need a little help rationalizing how I should structure my database for my custom tee site and want to know if this is the best way for me to do it and if so, how do I link the tables with the right fields.... Here's a pic to show you how I have my tables set up... There's a couple reason why it's structured like this... First I have two separate shirt tables because some of the shirts have color options and some don't, so instead of having all of them with un-used color options I decided to just break them down into two different tables. Then I have two options for customizing a shirt.. they can either get one of the "quick pick" designs or have one Optiqfied (customized from scratch).... the quick picks are all a set price for each kind of shirt so that just passes on that table then the cart.... I'm still working on the Optiqfied table and building the options n how they'll work to begin with... another reason for making the price table a separate table is because I have a lot of shirts that are the same price, here's another pic to show you what I'd like to accomplish.. Besides just the Men and Women shirts I also have Boys, Girls n Baby crewneck tees that all fit the same price as the xs-1x shirts.... so all together I have 16 total crewneck shirts that are $27.20.... I felt it would be better to somehow route them to a single price point to cut back on the amount of data and make it easier to do price changes or put things on sale.... Besides just the crewneck tees I have v-necks, scoop necks, tank tops, hoodies, etc. etc. all for men, women, boys, girls and babies.... so I have another 10 instances of what you see above to get all worked out. You'll notice in the price table the beginning of the names have QP- and O-.... that's the two options I mentioned before... the quick pick stays that price regardless to which design they pick... the Optiqfied shirts will have a different base price then get added to as they add features to the description of their design which will all have prices set to them, then the entire thing will send to the cart when complete. Is what I'm doing a smart way to be doing this?... if so... how can I set the shirts table to be linked to those fields? I figured once I get that I could just use an "if" statement to toggle between the QP and O prices. what do you guys think?... is there a better way to achieve what I need to do?
1) Normalize your database. One table for shirts, including a field for whether the shirt has color options, and one with the options. You can use bitwise options and a single integer. (IOW, use the numbers 1, 2, 4, 8, 16, etc., and add them together to store them in the field. 1 = Red, 2 = Blue, etc.) 2) "I felt it would be better to somehow route them to a single price point" It wouldn't. You could have a price code for each shirt, then link the code to a price in the price table. 3) How you differentiate the O and QP prices depends on how you calculate them. This really requires a lot more work than you'll get on a forum. A good db design will make the site snap, a bad design will make it crawl and eat database bandwidth.
thanks for the input.. ... as far as #2 I think that's what I wound up doing.. I added a column to both the shirts tables n the price tables and called it "size_cat" on both then used a new CREATE TABLE function to make a new table and told it to select the "shirt_name" column from the shirt table and the "price" column from the price table WHERE the size_cat=Size_cat.... so that way I have the price points with the size_cat IDs and I have each shirt listed in the other table and just have the size cat ID listed there so it links to the right one.... this wound up being tedious and led to me have 94 different items routed and was wondering how I could split it to where I could have "men, women, boys, girls" in another column and just make them available in the site where they apply, so that way I can cut the amount of items down. I'll see what works.... thanks again