BleepingComputer.com: Quoting System

Hi guys,

I've hit a wall again... I'm trying to create a quoting system (an ASP webpage) that backs on to an ODBC database (i can connect to it fine). Although this is still in the planning stages, i'm trying to think of the best way to accomplish this.

It will be used for quoting on chairs and their colours/fabric type/add-ons (such as arms etc..).

Now say each chair has a base model and specific add-ons and fabric types/colours. I am thinking of putting all this into a database that would look something like this:

chair name | option 1 ... | ... option 10 | add-on 1 ... | ... add-on 10 | colour ... | ... colour 10 |

then have the user select which options they want from drop down boxes/check boxes, have asp then query the database for a chair that has all the options selected, and print the name(s) of the chairs with the possible combinations.

This seems like it's a very long way of doing it, well to me, can anyone think of anything that would work better?

Cheers,

~ Kam

You should really have a table containing just the names (and a unique ID), and a table that holds all possible options. Right now, you may only have 6 options, but what happens if you get more options? You end up having to alter the database which you should really never do. If you keep the options separate, then you never have to worry about altering anything. With your proposed table, if you have 3 possible colors for each chair, and 3 leg heights (for example), you would need 9 rows in your database to represent each possible combination of values. At least, that is what it looks like to me.

that way won't each chair need it's own database with the possible combinations is it?

That's what it looks like you are trying to do. And I actually misspoke; you should have separate tables for each type of option. For example:

Table 1:

base_unit
id
name
price

Table 2:
color
id
color
price

Table 3:
material
id
material_type
price


When I create the query page, I first build a drop down menu containing names of the base unit (or a list of check-boxes, radio buttons, whatever). Then I create a drop down menu that contains the available colors by querying the database and getting all the available colors. Then I create a list of materials by querying the database for all of those. When the customer submits their request for a quote, the query then looks something like this:
SELECT base_unit.price, color.price, material.price FROM base_unit, color,material WHERE base_unit.name = 'foo' AND color = 'red' AND material = 'pleather'

Realistically, you would be wanting to use ajax to query your data source to pull back a price as each option is selected so that your clients have a running total on the screen. Once the customer finalizes the selection, send the entire options list back to the database so that the price can be calculated. NEVER put the price in the string that goes from the order form to the server. If I were naughty, I could build a false form request and make the amount say whatever I wanted it to say. What I could not do is get into the database to alter prices. Does that make sense?

Now, if certain options are only available on certain models, then you need to alter your database to hold information regarding which options go with which chairs.