Database Design/Model suggestions?
Posted by: Chris Bassett
Date: October 27, 2011 11:48AM

DATABASE MODELING SYSTEM

I have a database that I need to model and build. For the most part, this database is fairly straight foward in its design. However, I have certain items that further will fall into various levels of "subcategories."

What I'm trying to do is create a product selection system that allows the user to "drill down" on products that we offer, starting from very basic broad range of products to very specific product classes by using a series of questions / dropdown lists in a web form.

An example of what I'm trying to accomplish can be similar to this basic example below:


Example 1:
Question #1: 2 or 4 doors
You select 4 door
Question #2: V6 or 4 cylinder



Example 2:
Question #1: 2 or 4 doors
You select 2 door
Your only option is a V6 (no option for 4 cyl. because a 2 door is considered a coupe in this example)


Now, how can I design something like this where not every item will have the same number of subcategories below it, so to speak. For example, in some products, there may only be one subcategory, but in other products, there may be 2 or 3 levels of subcategories that that specific product class can be further broken down into.

yet another example of how there are various levels of subcategories for certain items might be something like the truck/car example above, where (for example) if the car has 2 doors, it may be offered ONLY with a V6 engine and no other options, whereas on the other hand, if the car has 4 doors, it may be offered with a 4 cylinder or V6 engine.
(Hopefully this sort of helps you understand what I mean by varying levels of subcategories for certain product classes.)

For what it's worth, most of the products will only have (At most) 4 levels of subcategories. (I think anything more is probably too much simplification for what I'm trying to accomplish). I just want to try to some up with some sort of design scheme that promotes good design and easy of updating/maintenance.


From my programming experience, I want to think this would probably be best resembled as a "tree" design (not neccesarily a binary tree where there is only 2 children, but a general tree design where each where each node can have 1 or more child nodes). So I guess it's not so much of how it functions, but more on how to store it efficiently in MySQL using the appropriate design methodologies of good database design practice with tables, etc.


Suggestions or examples? I think this type of "wizard" is pretty commonplace on the web (another example of what I'm trying to get at might be on ebay where you have to select a category for an item you may sell.)



Edited 6 time(s). Last edit at 10/27/2011 12:11PM by Chris Bassett.

Options: ReplyQuote


Subject
Written By
Posted
Database Design/Model suggestions?
October 27, 2011 11:48AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.