chaseivey
asked on
Database schema for Product Subcategories, Colors, & Sizes
Hello,
I have a general working knowledge of mySQL and I'm developing an e-commerce application using Coldfusion.
Before I dive into the client side, I want to make sure my database schema is properly in place.
Can anyone show me the most efficient way to set up my database to allow for product Subcategories, colors, and sizes? (I'm selling T-shirts).
A few things to know:
>>Subcategories - I want my users to be able to find items under multiple subcategory searches (i.e. Long-sleeve Pocket T-shirts can be found under "T-shirts>>Longsleeve", "T-shirts>>Pocket", "T-shirts>>Longsleeve>>Poc
>>Colors & Sizes - My items don't come in all colors. most of my items have a range of colors unique to that item number, so I need to only show colors on that item that are actually available in that item. the same goes for sizes, not all products come in every size.
Any help on how the schema should look for this portion of my database would be appreciated, including an Entity Relationship diagram would help, too.
Thank you for your help. :)
I have a general working knowledge of mySQL and I'm developing an e-commerce application using Coldfusion.
Before I dive into the client side, I want to make sure my database schema is properly in place.
Can anyone show me the most efficient way to set up my database to allow for product Subcategories, colors, and sizes? (I'm selling T-shirts).
A few things to know:
>>Subcategories - I want my users to be able to find items under multiple subcategory searches (i.e. Long-sleeve Pocket T-shirts can be found under "T-shirts>>Longsleeve", "T-shirts>>Pocket", "T-shirts>>Longsleeve>>Poc
>>Colors & Sizes - My items don't come in all colors. most of my items have a range of colors unique to that item number, so I need to only show colors on that item that are actually available in that item. the same goes for sizes, not all products come in every size.
Any help on how the schema should look for this portion of my database would be appreciated, including an Entity Relationship diagram would help, too.
Thank you for your help. :)
ASKER
Thanks for the info.
My apologies, but it's still a little vague to me. :(
Could you be more specific concerning exactly how these tables work with each other? Also, I don't think I need the UOM(unit of measure) stuff (do I?). My items are all sold individually and they are just blank T-shirts.
All that really changes is (available colors) (available sizes) (price changes based on color/size)
I figured that i only really need 3 subcategories beneath the main category(T-shirts), but i want any given item to be searchable under ANY of its subcategories. That way i can narrow down the searches on certain pages with conditional SQL statements.
A live example of what I'm after can be found at www.cmcasuals.com. For instance, item# PC61LSP is found under "T-shirts>>Longsleeve" OR under "T-Shirts>>Pocket" ... hmmm maybe I only need 1 subcategory? See what you think. :)
I may can find a simple way around this "subcategory" part, but my MAIN concern is how to accomodate the unique sizing/colors/prices changes that happen here. You can see this when you click on any item on this site. (www.cmcasuals.com)
Thanks again for your assistance.
My apologies, but it's still a little vague to me. :(
Could you be more specific concerning exactly how these tables work with each other? Also, I don't think I need the UOM(unit of measure) stuff (do I?). My items are all sold individually and they are just blank T-shirts.
All that really changes is (available colors) (available sizes) (price changes based on color/size)
I figured that i only really need 3 subcategories beneath the main category(T-shirts), but i want any given item to be searchable under ANY of its subcategories. That way i can narrow down the searches on certain pages with conditional SQL statements.
A live example of what I'm after can be found at www.cmcasuals.com. For instance, item# PC61LSP is found under "T-shirts>>Longsleeve" OR under "T-Shirts>>Pocket" ... hmmm maybe I only need 1 subcategory? See what you think. :)
I may can find a simple way around this "subcategory" part, but my MAIN concern is how to accomodate the unique sizing/colors/prices changes that happen here. You can see this when you click on any item on this site. (www.cmcasuals.com)
Thanks again for your assistance.
Tables work with each other using their primary and foreign keys. Each key column name is ended by ID and his name is beginning by the related table name. It should be sufficient enough to have everything under control. You should look for some books about data modelling (e.g. http://eu.wiley.com/WileyCDA/WileyTitle/productCd-0470178450.html)
UoM is maybe not necessary for the beginning because the only UoM is stick but you could handle boxes and palettes later :-).
What you surely need is some Pricelist table which is connected to Item (and UoM).
Above "data model" or its rough idea should fit almost all requirements from the mentioned web site. The main page is a picture of MasterItem table, all subpages are just results of different queries made from your tables.
If you need to display one item on several pages then you just need sufficient number of subcategories defined. You could also define "webview" subcategories and relation between Items and webview subcategory can be M:N (each item can be assigned to one or more subcategories).
So, you should first define what subcategories to distinguish on the web (and how) and then you may design data model for your data which will fullfil all your needs.
When some size/color/price change happens then you simply create new query and based on its results you have to create HTML contents. This principle is common for all data driven webs independently on the database and platform used. Its realization is, of course, different.
UoM is maybe not necessary for the beginning because the only UoM is stick but you could handle boxes and palettes later :-).
What you surely need is some Pricelist table which is connected to Item (and UoM).
Above "data model" or its rough idea should fit almost all requirements from the mentioned web site. The main page is a picture of MasterItem table, all subpages are just results of different queries made from your tables.
If you need to display one item on several pages then you just need sufficient number of subcategories defined. You could also define "webview" subcategories and relation between Items and webview subcategory can be M:N (each item can be assigned to one or more subcategories).
So, you should first define what subcategories to distinguish on the web (and how) and then you may design data model for your data which will fullfil all your needs.
When some size/color/price change happens then you simply create new query and based on its results you have to create HTML contents. This principle is common for all data driven webs independently on the database and platform used. Its realization is, of course, different.
I think you should really look at the subcategories and see if you have a lot of variation. Make a list of all the subcategories you think you will have and see if they just fall into a few types. Instead of dealing with hierarchical subcategories, which are a royal pain, you can just have a few attributes on your table such as "sleeve Type" and "pocket type" ... life will be much easier for you that way..
Also, I assume that you want one table for your each unique T-shirt type and then a child table of that which has a list of the available sizes and colors that you sell for that shirt. You can have the price at either the product level or the options level (if all the sizes and colors are the same price, put the price in the products table)
Products
productID
Name
Description
sleeve
pocket
price
ProductOptions
prodOptionID
productID
size
color
quantityInStock
(price?)
Also, I assume that you want one table for your each unique T-shirt type and then a child table of that which has a list of the available sizes and colors that you sell for that shirt. You can have the price at either the product level or the options level (if all the sizes and colors are the same price, put the price in the products table)
Products
productID
Name
Description
sleeve
price
ProductOptions
prodOptionID
productID
size
color
quantityInStock
(price?)
well, i like my answer :)
ID:24823760
ID:24823760
I like gdemaria's answer also but I would suggest point split if the question author does not decide else :-)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
MasterItem - MasterItemId, MasterItemCode, Descr, ... (will contain T-shirt, shoe, car, space shuttle, ...)
Subcategory - SubcategoryId, SubcategoryCode, Descr, ...
Color - ColorId, ColorCode, Descr, ...
Size - SizeId, SizeCode, Descr, Metric (Y/N), ...
Item - ItemId, ItemCode, Descr, MasterItemId, SubcategoryId, ColorId, SizeId, ValidityRange, ...
UoM - UoMId, UoMCode, Descr, ...
UoMConversion - UoMcID, MasterItemId, UoMID1, UoMID2, koef
Stock - ItemId, Quantity, UoM, LocationId, ...
You can define more tables for Item categorization, e.g. SubCat1, SubCat2, SubCat3 and use them for unspecified Item categorization.
The most efficient way depends on your needs. You have to decide how many subcategories you need, you have to decide if you need validity ranges etc.
Based on your Stock quantities you may create lists of available items. Just simple SQL Select commands... Your general knowledge should be sufficient for it.