Re: Database Design/Model suggestions?
Posted by:
Rick James
Date: November 01, 2011 12:41AM
How many different products? If there are under, say, 1000, then 'brute force' might be practical and efficient enough.
After the user picks the number of doors, you build a query that filters on doors, and discovers the number of choices in cylinders. If only one choice, then announce that he has to have a V6, and move on to the next option.
If you have a million products, that would probably be too slow.
Do you necessarily want to start with doors, then move on to cylinders? If so, then a compound index:
INDEX(doors, cylinders, ...)
might help make it efficient.
Or do your subcategories change as one drills down the tree?
Q1: Plant or animal?
Q2 for animal: Mammal or not?
Q2 for plant: ...
In this case, you need to build a hierarchical system. The table would have an id and a parent_id, which JOINs back into the same table with some other id. (Think of a genealogy tree of persons.)
In any case, think of MySQL as the datastore, and leave much of the traversing of the categories to your application code. Do not expect the database (MySQL, or other) to magically have exactly what you need.