Bridge table for INSURANCE

HI,
I am creating the Property & casualty Insurance DW for Autopolicy
I have PolicyTransactionFact,
DIM_covered_item_vehicle, Policyholder_dim these are FK's in the fact table.
and I created driver dimension table which has driver details.


How do I get
1) the number of Active drivers on the policy.
2) how many vehicles are associated for each driver or viceversa.

1)Shall I go with the option Bridge table between policyholder and driver or
2) Shall I go with option Bridge table between Vehicle dimension and Driver dimension.

Thanks in advance

Adding the DRIVER attribute to the fact table is definitely not a good option as we never know for sure how many drivers can be added to a policy. And adding a new Record in the fact for every driver added to the policy is not suggested.

I will create a XREF table between the Fact and the Driver dimension with the
PK = (Surrogate Key from the FACT + Surrogate Key from Driver Dim).

But the kind of questions you wanna answer will require a set of date attributes in the XREF table
like EFF_BEG_DT and EFF_END_DT

Each intersection of DRIVER and Policy will have a start and end date since this is a kind of Contractual table which should always have an end date.

Your queries will be answered as below
1) the number of Active drivers on the policy.

Select DRIV_ID
From XREF Table
Where CURRENT_DATE Between EFF_BEG_DT and EFF_END_DT

2) how many vehicles are associated for each driver or viceversa.

Just take all the Active drivers from this XREF table and take the Vehicle Info from the fact table.

This is the way we have resolved all the M:M relationships in our company.

Thanks

An option is to have one Fact Table with an Auto Dim and Driver Dim. The Fact Table has 3 measures - Drivers Per Auto, Drivers, and Autos. The value for the Drivers Per Auto is always 1. The value for the Drivers is 1/#Auto of associated with each Driver. The values in the Auto Measure would be the 1/# of drivers of each auto. This would enable the user to sum the measures to come up with the # of active Drivers or the # of autos.

For Example:
There is 1 household with 2 drivers, A and B
Driver A is associated with Cars 1, 2 and 3.
Driver B is associated with Cars 1 and.

Driver A, Auto 1, Driver Value 1/3, auto value 1/2
Driver A, Auto 2, Driver Value 1/3, auto value 1/2
Driver A, Auto 3, Driver Value 1/3, auto value 1
Driver B, Auto 1, Driver Value 1/2, auto value 1/2
Driver B, Auto 2, Driver Value 1/2, auto value 1/2

# of drivers, sum the Driver Value, which is 2.
# of Autos, sum the Auto Value, with is 3

Let's say the Auto Dimension had the Make, Model, and year of the Car. You could sum the Auto Value for each one.

Let's say the Driver Dimension has the Driver's Gender and age. You can sum the Driver value to count the number of drivers by gender or age.

It can break down when you start trying to ask questions from both dimensions. For example, let's say that Auto 1 and 3 were foriegn and Auto 2 was domestic and Drive A is Female and Driver B is Male. Answering a question such as the how many women drive Foriegn Cars would require a Count Distinct on the Driver where the Auto is Foreign and the Driver is a woman. Trying to sum the measures would give you a value of either 2/3 or 1.5, when real answer is 1.