MySQL

 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me



Go Back   Tutorialized ForumsDatabasesMySQL

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Tutorialized Forums Sponsor:
  #1  
Old November 27th, 2012, 01:27 AM
optiq optiq is offline
Registered User
Tutorialized Newbie (0 - 499 posts)
 
Join Date: Nov 2012
Posts: 1 optiq User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 25 m 45 sec
Reputation Power: 0
I need an expert opinion on my DB structure

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

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



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


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

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
Code:
<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
Code:
SELECT*FROM blah(blah,blah,whatever)
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
Code:
WHERE blah=code
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



The Conclusion

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

Last edited by optiq : November 27th, 2012 at 01:32 AM. Reason: link won't show up....

Reply With Quote
Reply

Viewing: Tutorialized ForumsDatabasesMySQL > I need an expert opinion on my DB structure


Developer Shed Advertisers and Affiliates


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


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 On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 

Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.

© 2003-2014 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap