Insured item

[DavidO]

I support multiple insurance products e.g. home and motor.

I have a policy holder dimension and an insured item dimension. A fact table records

The insured item could be a house or a car. What's best practice for storing textual attributes?
Should all the posisble attributes be held on the insured item dimension e.g.
HasSprinklers, VehicleCode...which is a mix of home and motor attributes

or does the insured item dimension have a HouseKey, VehicleKey..if the insured item is a house VehicleKey points to unknown etc?

[ngalemmo]

Break the unique attributes out into sub-dimension tables. You would have your insured item dimension, which would contain a type code and attrbutes common to all types, and sub-dimension tables for different types of items (house, car, etc). The number of sub-dimension tables would depend on the nature of the attributes. For example, a house sub-dimension may also include apartments if the number of attributes unique to a house or apartment is not too great.

These sub-dimensions would carry the same primary key value as the main insured item dimension, so there is no need for additional foreign keys in the fact table.