I make custom t-shirts and am trying to figure out a good way to organize my database... before I go on to ask HOW to do it... I'd like to know IF the way I'm planning it so far is a smart/good idea. I want to say off top THAT I KNOW THERE'S NO EASY ANSWER TO THIS!!!!!!... so please don't assume that I think you can give me a magical 2 sentence breakdown that makes it all clear n spend 3 paragraphs telling me that.... I KNOW HOW INSANE ORGANIZING THIS IS!!!!... which is why I need help... lol...

check the attached image to see what I have so far before continuing.....


Let me explain why I structured it like this...

With all the shirts I offer every size from New Born-1X adults will be the same price, then 2X is a little more, 3X is a little more and so is 4X and 5X. So I figured I could just like the "shirt_type" column from the Master Shirt table to the Size table and just use the IDs as values in the actual html elements I'll be using to select them. for example... in he men and women sections I'll call to the IDs 9,10,11,12 for s,m,l,1x... then 8,9,10,11,12 for boys and girls size options for xs,s,m,l,1x.

I made the "Type Table" because most of my shirts are available in Men, Women, Boys and Girls, so instead of trying doing an ENUM on each one I figured it would save space by linking them to the same table and calling the options I want in the html. The type table is also linked to the "photo table" because I would like for the photos to be called up from the tables as well. I'm going to be building a back door to my site so I can just upload product photos and have them go directly where I need them.

The "White Shirt Table" is for the white shirts. I decided to do it this way because there's a lot of shirts and I wouldn't want to have a whole column that will only be %30 used once I do start offering colored shirts. It has a column called "color_op01" because some of them have different colored sleeves, such as the raglan shirts... the torso of those shirts are white, but there are options for the sleeves and I'd like those represented from the color_op01 table. I'm going to make a table for the colored shirts too once I start offering them and plan to link them the same way.

The "Price Table" has two columns with two sets of prices because I have two different routes they can go with everything. I have what I call "Quick Pick Designs" which are designs that are already pre-made, all I'd have to do is add the name to them that they'd like done. The next is what I call "Optiqfied" which is getting the shirt customized from scratch and the prices in the "o_price" column will be the core price for those shirts, which will branch off into another section with more options and prices. I'm not done planning out the organization of the different options, but that's because I'm hitting a brick wall here first not knowing if this is a good setup to go with and second, not knowing exactly how I'll have to link all this together and if certain things will be possible... such as pulling data from the photo table into the type table to the master shirt table THEN to the white/color shirts tables... I'm also concerned about how efficient it will be a few months down the line once I've added more shirts and designs. Will this work out?... if not, what would?.. if you have any questions to help make this madness clearer please ask.. Thanks for you help..

It looks overly complex to me (I had to download your image and open it in Photoshop before I was able to see what you actually had).

A system that I devised and which has been used by both Google and Mozilla in their merchandise stores and thousands of other online stores is far simpler:

1. A "categories" table. This table stores all of your different categories and includes a 'parent_id' column so that you can build sub-categories. For example, Men. Women, Boys, Girls, Babies would be top level categories (parent_id = 0).
Then you can have sub categories within each of the top level categories, eg. Crew Neck Tee, V-Neck Tee etc.

2. A "products_to_categories" table - simply two columns: products_id and categories_id - products would normally live within the bottom level categories only.

3. A "products" table. This table has all the product data, including size, color and price and image and stock quantity.. It would also have two very important columns:

1. master_id - the way this is used is like this: First you create a "master" product. This product is a "holder" for all the products related to it. For example, the master product is a "White Crew Neck T-Shirt". It includes a picture, description and possibly a price. Then you create all the products that are related to it >> all of your different sizes of this particular white t-shirt. All of these products have the "products_id" of the master as the "master_id", thus you can pull them all together under the master, whether just to display as options or to display as a list under the master info. One very important thing here is that you will be able to track stock on the individual sizes and each can have their own SKU.

2. product_rel - this column will contain a code which can relate the same products with different variations, eg. "Black Crew Neck T-Shirt", which naturally is the same as our white one above, only black.

Its up to you whether you use a combination of master_id and product_rel to pull all of the products together on a single page (I assume this is for the web) or to display them on separate pages.

This is a very simple system that works very well for clothing such as t-shirts. I have also use similar for music websites: albums/tracks.

See it on Google: Men's 4 oz. Triblend T-Shirt

thanks for your response!!!!.... I wanted to respond sooner but I wanted to put something else together before I did... but I'm not too sure about what to do. I've been trying to figure out if foreign keys would be best to use to represent my options. You're suggesting a slightly different kind of set up... but according to how I was originally thinking, I was thinking of linking the tables with foreign keys... especially the size table seeing that it would apply to ALL the shirts. as opposed to using ENUM on every column I figured it would save time n memory to just make one table and call to it in every column that applies.... is this a good method?.. how would this work?

Goods such as you have should be represented as they really are. For example, I have two white t-shirts. One is size XL and the other is size S. Now, I have often seen that people get "clever" and try to represent this as a single product in their main products table, with other tables linking options to it.

The problem with this is that you lose one very important factor >> stock control. You cannot query your database and find out exactly how man XL t-shirts you have in stock.

The products should be represented as they are in the physical world >> two related, but distinct products. This is what my "master" system does.

Each product should have its own distinct record in the products table.

Thus, in relation to sizes, it is far simpler to have a "size" column in your products table that simply stores "XL", "S" or whatever other sizes you have, rather than a separate sizes table.

Did you look at the Google page? It uses my system. Each product you see listed below the "master" is a separate product in the database - they are tied to the master using "master_id". The masters are what you see as you are browsing the catalog. The product page is identified using the master's ID.

I looked at your page last time and seen how it's set up... though there isn't a way to see the table structure.... is it?.... I understand what you're saying about the quantities, and that's something I could actually use in my regular retail section which will be different designs I've come up with for retail... as opposed to the custom shirts which are actually my main focus.

In the custom sections they can only customize one shirt at a time.... but now that I think about it I do plan to make a bulk section for people having family reuinions or buying company shirts..... so I guess it still would be a good idea to make them all drawn out like that.... ok... you win.. lol.... here's a link to what I'm working on so far with my site..

http://www.optiq-customtees.zxq.net/site04d.html


on the home page tab I have the options window visible jsut for the sake of beign able to work on it without clicking through everything else first... but normally it would go after the "enter name" section in the ordering process.... If you click Customize>QuickPick>Men you'll see the shirt gallery displaying the quick pick designs... if you click "Add to Cart" on the first shirt that pops up which says "Bobby" on the front you'll see the next window pop up with the different color options for that design, then when you click next it goes to the name entry field, then it stops there... but normally you'll be able to click next then it will go to the shirts window you see as soon as the page loads, and they select their size that way.

I made buttons to represent everything because most people's #1 available connection to the internet is their cell phone... so even if they have an actual computer at home... they got their phone at work... lol.. small drop down menus and text boxes are a pain to select on a phone, ESPECIALLY when they're close together so I want to eliminate that hassle... It's also a pain to have to scroll through long pages so I have everything set through javascript to pop up and disappear on command so they can stay right where they are without having to go back n forth sometimes accidentally selecting things.


Just thought I'd show you what I'm working on so you can understand the perspective I'm thinking from..... once I get the database figured out and built I'm going to use PHP to auto populate the different parts so I don't have to do all that coding for each thing... like the gallery with the designs... when I do a new design I want to be able to upload the images adding new rows to the relevant table(s) and after that's uploaded it'll automatically pop up in the gallery when someone goes to the page after that.... rather than copying/pasting the html and adding the new names to it... get what I mean?

You said it would be best to create a row for each size, which I can see as relevant, but would it still help to have an external price table because like I said, NB-1X adult is the same price, which doing each and every shirt in every size means I'd have 33 total Crewneck tees.... 25 of them the same price... if I'm going to do it this way that means I'm going to have over 150 different rows for all the different kinds of shirts I offer, will linking them to a price list as I originally intend to save on load time and memory? if I add the colored shirts to this table that'll be another 150 or so bringing it all up to 300+ total items half with anywhere from 4-30 different color options depending on the shirt and what's available that season... so if I add a colors section will that be worth all the empty space on the white shirts? Thanks again for your time..

Yes, however since I am the person who created the system, I do know the structure

Its late here, shall look at the rest of your post tomorrow.