Database design advice

Question

I'm designing a database for our sales team to use as a quick job quoting tool. I would like some feedback on a particular aspect of the design.

A quote is basically built up by selecting a list of predefined 'assemblies' each with an agreed price. A simplified view of the main form looks like this:

                                                  +------------ --- ---+
                                                  | Assembly options   |
+------------+------------+----------+------------+---+---+---+ --- +--+
| assembly ▼ | unit cost  | quantity | total cost | 1 | 2 | 3 |     |50|
+------------+------------+----------+------------+---+---+---+ --- +--+
| VSD55      | £10'000    | 2        | £25'500    | 1 | 1 |   |     |  | 
| RDOL2.2    | £2'000     | 1        |  £1'500    |   | 1 |   |     |  | 
| DOL5.0     | £1'000     | 1        |  £1'200    |   |   | 1 |     |  | 
+------------+------------+----------+------------+---+---+---+ --- +--+

The user selects a predefined assembly, enters the quantity and selects any 'options' required. Each assembly potentially has up to 50 available options. An option is also a predefined assembly (sub-assembly) with its own price. The 'total cost' for each line is calculated as (main assembly cost * quantity) + cost of any options.

When the user moves their cursor into an option box the name & price of that option is made known to them.

Now this is where it gets complicated. Each assembly has its own list of available options. i.e. option 1 for a 'VSD55' represents a different sub-assembly than option 1 for a DOL5.0.

As far as the assemblies go here are the simplified tables I'm using:

+-----------------+    +------------------------+    +-----------------------------+
| assembly        |    | assembly_option        |    | assembly_option_link        |
+-----------------+    +------------------------+    +-----------------------------+
| assembly_id (PK)|    | assembly_option_id (PK)|    | assembly_option_link_id (PK)|
| assembly_name   |    | assembly_option_name   |    | assembly_id (FK)            |
| unit_cost       |    | option_number          |    | assembly_option_id (FK)     |
+-----------------+    | unit_cost              |    +-----------------------------+
                       +------------------------+

The table 'assembly_option_link' basically defines which options are available for each assembly.

Now for the 'quote' tables:

 +-----------------+    +------------------------+    
 | quote           |    | quote_assembly         |    
 +-----------------+    +------------------------+    
 | quote_id (PK)   |    | quote_assembly_id (PK) |
 | quote_name      |    | assembly_id (FK)       |
 +-----------------+    | quantity               |
                        +------------------------+    

Now the tricky part is how to store any selected options. Should I expand the 'quote_assembly' table with all the 50 option fields even though this breaks normalisation rules. An assembly will never be selected with all 50 options so this seems very inefficient too. On the plus side, this solution allows the user entry form to map directly to the table making coding easy.

The 'normalised' solution I think would be to create another table like this:

+------------------------------+
| quote_assembly_option        |
+------------------------------+
| quote_assembly_option_id (PK)|
| quote_assembly_id (FK)       |
| assembly_option_id (FK)      |
| quantity                     |
+------------------------------+

This solution means only selected options are stored. Also, rather than storing the option_number, I can store the actual 'assembly_option_id'. This then makes calculating the total quote cost simpler as I don't need to convert between 'option_number' and 'assembly_option_id' to lookup the assembly option cost. The major drawback with this solution though is that it doesn't sit well with the user entry form. I think I will need to apply some fancy coding to interface the form with the tables.

Can anyone offer any design advice here please? I hope I've explained myself well enough.

MORE INFO
The is also a detailed quotation report that expands any selected options as separate line items under the main assembly. For example:

+---------------------------------+------------+----------+------------+
| assembly                        | unit cost  | quantity | total cost |
+---------------------------------+------------+----------+------------+
| VSD55                           | £10'000    | 2        |   £20'000  |
|   - Seal leak protection        | £ 5'000    | 1        |   £ 5'000  |   <-option 1
|   - Motor over temp protection  | £   500    | 1        |   £   500  |   <-option 2
+---------------------------------+------------+----------+------------+
|                                 |            |          |   £25'500  |
+---------------------------------+------------+----------+------------+

Answer 1

                                                  +------------ --- ---+
                                                  | Assembly options   |
+------------+------------+----------+------------+---+---+---+ --- +--+
| assembly ▼ | unit cost  | quantity | total cost | 1 | 2 | 3 |     |50|
+------------+------------+----------+------------+---+---+---+ --- +--+
| VSD55      | £10'000    | 2        | £20'000    | 1 | 1 |   |     |  | 

If somebody handed that quote to me, my first question would be "What's option 1 for the VSD55?" The answer would be "I don't know." That information isn't on the quote. In the unlikely event that person got to field a second question, that question would be "What does it cost?" Again, the answer would be "I don't know." A very disturbing silence would follow immediately, during which the person who handed me the quote would imagine how much better it might feel to be run over by a train.

Options must be line items on the quote, along with their unit price, quantity, and total price. Options must be named, not numbered. They should appear directly under their parent assembly, too, not scattered all over hell and half of Georgia.

If you want a shot at my money, you'd better make it crystal clear what I'm supposed to be getting for my money.

There's nothing (much) wrong with 50 check boxes on a user-interface form. That makes it easy to pick options. But the UI code should read the checkboxes and insert the right information into normalized tables.

Answer 2

The last option you give is the way I would go with it. And return two grouped tables, one for the "main row" and one for the collected "does it exist" rows for the 50 columns. Assuming you can map the option to it's appropriate column ID easily enough (it looks like you can without too much of a hard time).

That would be easy enough for iteration, assuming a language like C#, where you have LINQ available, etc. Those are easy enough to do, even if they involve a bit of looping (it's UI code, it has to be done at some point). Or you could do a pivot in the database before returning ... that would be faster. But it'll still maintain the complexity.

But your design does sound sound to me.

Answer 3

Adding your extra table seems quite quite sound to me as well.

When dealing with a similar issue on my end, I explored storing a tree in the order_lines table. In case you considered anything similar, I had:

• an extra parent_id field for order_lines, and a foreign key so that (parent_id, product_id) would reference (order_line_id, product_id)

• A check constraint so as to make having an option would imply a parent (in my case check((option_id is not null) = (parent_id is not null))).

In other words I let the UI have a word on how things should be stored:

+---------------------------------+------------+----------+------------+
| assembly                        | unit cost  | quantity | total cost |
+---------------------------------+------------+----------+------------+
| VSD55                           | £10'000    | 2        |   £20'000  |
|   - Seal leak protection        | £ 5'000    | 1        |   £ 5'000  |
|   - Motor over temp protection  | £   500    | 1        |   £   500  |
+---------------------------------+------------+----------+------------+

From a UI-coding standpoint it seemed right. But it quickly felt wrong from a business-rule standpoint, in that it introduced a variety of issues down the road. (I had to deal with all sorts of special cases in triggers.)

So not recommended... In so far as I've experienced similar cases, your current approach will be less problem-prone down the road.