bcamp1973 Posted March 6, 2007 Share Posted March 6, 2007 i'm building a modest ecommerce platform and I'm having trouble deciding how best to store product information. The site will sell various types of product so it needs to be flexible. My problem is this. There will be various product categories and subcategories. For examples "Shoes" will be a parent, and its children might be "Sandals", "Dress Shoes", "Sneakers" etc. Then another category might be "Shirts" etc. However, based on the category the products will have different data. For shoes, sizes will be something like 7-16, Shirts will be S,M,L,XL etc. Shoes will also have widths. Some products will have different material composition to choose from (Leather/canvas etc.). Has anyone successfully modeled this in such a way that isn't a sloppy performance hog or a rat's nest of tables? I just want to do this right the first time and my googling isn't getting me any good examples Quote Link to comment Share on other sites More sharing options...
artacus Posted March 6, 2007 Share Posted March 6, 2007 My tables would probably look something like this: - categories (id, parent_id, description, sort_order) - items (id, cat_id, description, size_id, material_id, color_id, ...) - sizes - inventory Using that approach you can comfortably nest categories 3 levels deep. (Shirts->Dress->Long Sleeve). If you feel you need to go more than 3 levels deep, then I'd suggest a different approach. But I kinda doubt you'll need to. Quote Link to comment Share on other sites More sharing options...
bcamp1973 Posted March 6, 2007 Author Share Posted March 6, 2007 Thanks artacus! I think that clarifies a couple things for me. Looking at it this morning after a nights sleep doesn't hurt either Your reply raises a couple questions for me tho. For one, inventory...my initial thought was to have a "quantity" file in the item table. Your suggestion makes me think i'm not quite thinking it through enough. Also, if i wanted to go more than three levels deep (possible but not sure at this point) how might you approach that? Quote Link to comment Share on other sites More sharing options...
artacus Posted March 6, 2007 Share Posted March 6, 2007 Well the queries for going much deeper than 3 levels gets unruly. So then you switch to the nested tree approach. There was a great tutorial for doing this in PHP at phpriot.com. The link isn't working for me at the moment. Maybe the server is just having problems... http://www.phpriot.com/d/articles/php/application-design/nested-trees-2/index.html Quote Link to comment Share on other sites More sharing options...
bcamp1973 Posted March 6, 2007 Author Share Posted March 6, 2007 Thanks artacus! I'll take a look once their site is back up... Quote Link to comment Share on other sites More sharing options...
artacus Posted March 6, 2007 Share Posted March 6, 2007 Its pretty complicated stuff. So I'd suggest going the route I just showed you if you can. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.