>I need some help in correctly setting up my tables. I've never had these kinds of requirements before so here it goes.
>
>Let's say I need to setup a system to store different characteristics of an automobile. Let assume I want to store info. on all possible combinations of TYPE (car, van, truck), COLOR (blue, black, etc.), TRANSMISSION (auto, 5-speed, etc.) and DOORS (4-door, 2-door, etc.).
>
>The way I originally though is to create 3 tables as follows:
>

>TABLE: CLASS
>CLASSID   N-1O Primary key
>CLASS     C-10
>
>This table will hold one record for each type of characteristic (TYPE, COLOR, TRANSMITTION, DOORS.
>
>TABLE: CODES
>CODEID   N-10 Primary key
>CLASSID  N-10 Foreigh key
>CODE     C-3  (Code assigned to characteristic - e.g. A)
>DESCR    C-20 (Description of code - e.g. VAN)
>
>This table will hold one record for each "CLASS" and characteristic.

This is the first flaw in the design;  the elements of CODES don't all belong in the same table, because from an informational point of view, they aren't independent.  it would be far better to have a table of code per characteristic, so that the combination isn't positional, and you can deal with null values for some or all codes independently.

>
>TABLE: COMBINED
>COMBID     N-10 PK
>COMBCODE   C-10   (Combined code of each different classes)
>DESCRIPT   MEMO   (Combined description of different classes)
>COST       N-10-2 (Cost of each particular kind of car)
>
>This table will hold one record for combination of each "class" and characteric.

This worsens the design, in that cost is not really an element of the combination of codes that go into the table - if you have two red, 2 door, standard transmission cars, they don't need to have the same price.

Vehicles have a set of independently varying characteristics;  the COMBINED code is a serious mistake, in that you have to define a new COMBINED code if you paint a car or drop its price. Every vehicle has each of these characterisitcs, and the set of characterisitics do not determine the price of the car. You can build the Descript field by concatentation of the Descript fields in a View at runtime.  You've got to store the independent chartacteristics with the vehicle in any case (otherwise, you can't easily ask for a list of all red cars, or all vans costing $10,000.)

>
>Example: COMBCODE  DESCRIPT                     PRICE
>         A1A1      Car, Blue, Auto, 4-door      $10,000
>         A1A2      Car, Blue, Auto, 2-door      $9,500
>         A1B1      Car, Blue, 5-speed, 4-door   $9,750
>         A1B2      Car, Blue, 5-speed, 2-door   $9,250
>

>Now, I need to "update" the COMBINED table data everytime a user changes (add,delete,edit) codes in the CODES table. How can I set this up so that I can easily do this? Relationally speaking, is my table setup adequate to handle this (I don't think so)?