First and foremost I'd like to say that I KNOW THERE'S NO EASY ANSWER TO THIS!!!!!..... so please don't assume I'm expecting a simple 2 sentence explanation and spend 3 paragraphs telling me how ridiculous it is to think that.... I know better.... If there's something I need to think about FIRST before this can go forward please just let me know that so I can get it worked out.... now that that's out the way.. lets get started..
I make custom t-shirts and am trying to figure out a good way to organize my database... here's the idea I mapped out so far..
(the pic wouldn't show up for some reason so here's a link to a page with the table on it.)
Let me explain my theory and purpose behind these relationships...
Master Shirt Table - Size Table
All the shirts I offer cost the same price for every size up to 1X. After 1X the price goes up a little more with each size going up to 5X. So instead of having:
Crewneck Tee Newborn $27.20
Crewneck Tee 6 months $27.20
Crewneck Tee 12 months $27.20
Crewneck Tee 18 months $27.20
Crewneck Tee 2T $27.20
Crewneck Tee 3T $27.20
Crewneck Tee 4T $27.20
Crewneck Tee XS $27.20
Crewneck Tee small/kids $27.20
Crewneck Tee medium/kids $27.20
Crewneck Tee large/kids $27.20
Crewneck Tee 1X/kids $27.20
Crewneck Tee small/adults $27.20
Crewneck Tee medium/adults $27.20
Crewneck Tee large/adults $27.20
Crewneck Tee 1X/adults $27.20
Crewneck Tee 2X $28.10
Crewneck Tee 3X $29.11
doing this with every kind of shirt I offer, I figured I'd just represent it with
Crewneck Tee (size options) $27.20
Crewneck Tee 2X $28.10
Crewneck Tee 3X $29.11
so that way I can cut down on the amount of items listed in the table.
Master Shirt Table - Type Table
I decided to use this table for the same reason I came up with the "Size Table"... most of my shirts are available in Men, Women, Boys, Girls and a couple in Babies.... which would be the long example I gave above X5... and for just one shirt that came out to about 96 different items, which means I'd be looking at about 1,000 different items in the table if I had it all broken down into size and type for every kind of shirt.
Type Table - Photo Table
I made the "Photo Table" and linked it through the "Type Table" because I want my coding to automatically retrieve the product images while the page is loading as well as have somewhere to link my backdoor to so I can update my products. If I were to add a new kind of shirt I want it to be able to add a new column then fill in the spaces with the file name so in my html coding I can have
<img src="http://blahblah/folder/($id).png" />
linking to it automatically rather than having to go in and re-code my galleries and product areas so I can just tell the coding to
and let it populate on it's own.
White Shirt Table - Master Shirt Table
I decided to make separate tables for the white shirts and colored shirts. The reason for this is because the colored shirts will each have at least 20 different color options so I didn't want to bog up one table with all those different options. The "White Shirt Table" has a column called "Color_Op01" because some of the shirts have different colored sleeves though the torso is white, and even those shirts in the colored shirts have multiple combinations of options so I figured it would be better to just include those all into one column in the "Colored Shirts Table" when I make it instead of having two color option columns. So I want to pull the core data from the "Master Shirt Table" and add more options in the "White Shirt Table" to set the core prices of the shirts.
I have two columns in that table named "qp_price" and "o_price". The reason for this is because I offer two services. One is what I call "Quick Pick" which is pre-made designs that I only need to do the name for. the "o" stands for "Optiqfied" which means fully custom from scratch and the "o_price" represents the core price of the shirt, there will be more tables added later to represent those price options as well. I also have another table for the quick pick designs but I decided to hold off until I figured out weather or not this structure would be efficient because it's structured to pretty much the same theory. The price table will also have prices for the colored shirts when I add them on.
White Shirt Table - Color Op01 Table
As I mentioned before, the Color Op01 Table is to store the color options for the shirts. I have it set up the way I do because I'm thinking if I can use the "code" column in a
statement I can get them to partition off that way. For example, for the Men's Raglan shirt, if I put "ma-rag" in the "Color Op01" column it would list all the shirts in the Color Op01 Table with the code "ma-rag", same with the women raglans and the "fe-rag"... and those options are pretty much universal too, the men and boy's raglans are the same colors so I could use the same code name for both. I have the picture column in there too because
Overall my goal is to represent each kind of shirt I have available in all sizes and types without it becoming too bulky. I have more tables I need to build for other options but it's hard to think out how I want to do them because I'm not too sure of a good method to go with to make sure everything will be smooth and stay organized as I add to it. I'm also building an admin login so I can just upload the photos and enter the data using PHP to auto-populate the actual site so I don't have to go in and code everything manually. Does what I'm thinking and doing so far make any sense? If not... what would be a better more efficient way to accomplish what I'm going for with this idea? Thanks in advance..