>
>...it would be far better to have a table of code per characteristic, so that the combination isn't positional...>Can you please provide a simple example of this?
Sure. Rather than a single table of CODES, you have a table of codes per characteristic:
Color: (Code, descript) eg 1 = blue, 2 = red, 3 = green, etc.
Trans: (Code, descript) eg 1 = standard, 2 = automatic
VType: (Code, descript) eg 1 = car, 2 = van, 3 = fully armed & operational deathstar
Each vehicle has these characteristics, each characteristic must take one of the values associated with the characteristic. We can always build the description on the fly in a view by concatenating the description. Moreover, there is no ccommon element of the combined description that provides a unique piece of information; from your example, the bad choice in the COMBINED table was associating a price with a combined code, where the price is an attribute of the vehicle with those characteristics, not the set of characteristics. IOW, one particular blue, automatic transmission, fully armed & operational deathstar has a price associated with it; the class of all blue, automatic transmission, fully operational deathstars do not chare a common price (face it, it's the mileage on those deathstars that really figures the value anyway.)
The only thing gained is a single menu point to maintain 'codes', but in order to distinguish which codes go with which characteristics, we need to add a column of code type. While it looks like it's easier to make just one screen to maintain codes, we can do this by creating a common form class and selecting the type of code we want to maintain. This makes assigning codes to characteristics easier, and eliminates the need for a combined rtable which is nothing more than a coincatenation of descriptions, which can be built on the fly with inner joins.
>
>
>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.>Actually, I'm only using the automobile metaphor as an example to make things easier to understand (so I though). The actual characteristics are related to direct media publication. Let's assume ALL combination of characteristics are possible.
>
>Costs are actually not defined at COMBINED table level. I'm currently using the COMBINED table as a "master" lookup table only. Under my original auto metaphor, each dealer would select only those combination they keep in stock and assign dealer specific cost. These, "selections" are kept in a separate table (let's say INVENTORY) that actually holds the costs.
>
>
>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.>Again, excuse me for trying to use the automobile metaphor.
>
>
>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.)>Yes this is what I'm trying to get at! How can I create a flexible database scheme to do this (reflect back to the original data source)?
By associating columns of characteristics into the table. When the nature of your thing changes, you add a column to the table. Another alternative is to have a table of characteristics as a child, with columns (PKID, ParentID, CharacteristicType, CharacteristicValue), with a candidate key of (ParentID, CharacteristicType). This is a design I've used in other projects, which I refer to as 'Tagged Data'; each thing hs some variable set of characteristics, not all of which occur for all things, so we have a separate table of those characteristics taken on by the parent object. It carries a tremendous amount of baggage in the front-end and in reporting; it is a useful way to model certain types of OLE collections in a relational database, where adding columns (member properties) to a thing makes for an inconsistent interface. Ordinarily, adding a column to a table and describing that in metadata is more efficient; it's only where the concern over changes in properties of objects make older objects unusable that this type of data model is worth the investment.
Where all things of a class have a common set of characteristics, simply add a column to the table describing those things, and data drive your reporting 9what appears to be your primary issue here.) At some point, because characteristics are multually exclusive and need to be independently selectable, the combined model breaks down quickly, and you save nothing in terms of table integrity, since the definition of the combined code column changes, and you can no longer search the base table directly for things only described through the combined code table.
