One to many relationships

Hi,

I am new to dimensional modeling, would anybody be able to guide me on a design questions? An insurance policy has multiple insureds and multiple vehicles. If I create a fact table that lists the total premium for each policy, and make insureds and vehicles the dimensions; how do I link multiple insureds and multiple vehicles to the fact table? Would I use a bridge table? What would the structure be of that table and what keys would I store in the fact table?

Thanks so much for your help!!

Martijn Kamerbeek

Hi Martijn,

In my opinion you have two options. Create a fixed number of foreign key relations to a dimension or use the bridge table option. The first option depends on the needs of the user. In my practice of my current project i'm logging messages. And these messages can have errors. The question is whether you want to log all the errors (bridge table) or is a registration of 5 errors (?) good enough. The customer said that 5 is very extraordinary. So i decided to model a fixed number of 5 error possibilities. When this is not enough i will enter an another reference tot the error dimension. A practical approach. If you want to register all of the vehicles, etc than you need a bridge table in my opinion.

I haven't done much this in my daily job but i can imagine that you could model this like a normalized n:m relation between the dimension- and the fact table.

Good luck!

Geetz,
Hennie

I think if you look at the grain then the premium is to the policy, vehicle and insured information are attributes of the policy
so in my opinion you should have the followings

policy dimension
premium fact table (this should have only the policy dim key)

now one policy can have multiple drivers and multiple vehicles which is called multivalued dimensions so you are right about the bridge tables
you will have two other dimensions insured dimension and vehicle dimension
you will have two bridge tables between the fact to insured dimension and fact to vehicle dimension

policy dimension ====> premium fact<==== bridge (policy key, vehicle key) ===> vehicle dimension
<==== bridge (policy key, insured driver) ====> insured driver dimension
this will also help you roll up, slice and dice.

let me know
thanks
Ranjan

Thanks to both of you. It's given me a lot of food for thought. Ranjan, policies also have a price per vehicle. Would I create a vehicle premium fact table that has the policy dimension and also the vehicle dimension as the primary key? (one of the reasons I couldn't only have a vehicle premium fact table is that the sum of all the vehicle premiums may not be the total policy premium). Again, thanks so much!

Martijn

You may want to think about the grain. Rather than think about the total premium, as you have stated, there is pricing for all aspects of the policy. The vehicles, riders, discounts etc... Why not build a fact table at the lowest level of detail? You can always get a total from it, but without the detail you lose the ability to do a lot of analysis. Most certainly, vehicle should be a dimension, and it would not be multi-valued. Insured would be multi-valued (personally I prefer a bridge table for any multi-valued dimension as they are much more flexible) but you should also consider a primary driver dimension, which would not be multi-valued. Both the insured group dimension and primary driver dimension would ultimately point to the same customer dimension.

The coverage (collision, comprehensive, etc), riders, discounts and other pricing components of the policy can be represented as a product dimension. For rows that are not directly attributable to a vehicle, you could have the vehicle key point to a 'not applicable' row, or if they can be pro-rated to a vehicle (discounts, for example) then create fact rows with the amounts distributed appropriately.

Hi ngalemmo,

Thanks for your comments, they help a lot. I agree with you on the grain. Auto policies have vehicles and vehicles have coverages. So far I have created a coveragefact table, vehicle fact tabe and policy fact table. There are dollar amounts at each level (a policy has an underwritng cost, a vehicle has a report cost, and coverages have premium amounts). Is it the right things to do to create these three fact tables? If that is correct, and I have a policy dimension, I could use that (the same value of the key) at all three levels, right? I am finding that at the coverage level there are no one to many relationships anymore. In this case, would it be right to call the policy dimension a conformed dimension?

Thanks so much!

Martijn

Policy would be a conforming dimension across the facts. It may be degenerate if all aspects of the policy are covered in other dimensions.