Database design advice

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_o

+------------ --- ---+
                                                  | 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.

StackExchange Database Administrators Q#2995, answer score: 3