>I am designing a web based system for consumer vehicle configuration. The options list for the various vehicle models includes combinations which exclude certain options and require others. Currently we just have the elementary system working with the full option table, full model table, and the options by model tables.
>
>I have some thoughts regarding the design and stored procedures for interactively returning a cursor of both the excluded and required options based on the currently selected options. However, I suspect that many of you have already dealt with this design challenge and have some good ideas for both design and stored procedure code. I openly welcome any suggestions and code examples.
>
>Thanks
>
>Glenn
Glenn,
I think that the proper logical structure for this sort of thing is probably a tree. Something like this diagram:
http://guitaristcentral.com/ModelTree.htmIn the diagram, components (what we normally think of as options) are organized into groups (e.g. Engine, Interior), packages (e.g. Sport package, Leather package), and stand-alone components. A node in the tree can be a group, a package, or a stand-alone component and inside the node the component is either optional (dashed line), required (solid line), or excluded (not shown). To build up a list of components, traverse the tree from top to bottom adding each component in the node to the list. When you encounter a node that contains a component that is already in the list, the node that is lower in the tree takes precedence.
Using the same method the user can navigate the component selections and a 'punch-list' report can be generated based on the nodes that the user selected.
You already have tables for the components, make, and model. You will have to add tables to implement the tree structure and seed it with data.