Oh no, not Bridge tables again!!!

[bucko]

Hi Guys!

I am just getting into Data Warehouse and I am reading the books and searching the internet to get the information I need. Everything seems to be going OK except for Bridge tables. Now don't get me wrong I understand they are there to group dimensions to Fact tables. But it is on the technical side that I am having a problem.
I am using SQL Server 2008R2 and I have for example a fact Car table and a dimension Car Details table and I can have multiple cars per one entry in the fact table. I want to create a bridge between these two tables.
So lets say I have this structure

CarDetails table

CarDetailsID int identity(1, 1)
CarDescription varchar(100)
etc.

CarGroup table

CarGroupID int
CarDetailsID int
etc.

Car fact table

CarID int identity(1, 1)
CarGroupID int
etc.

Now in my CarGroup table I have these values

CarGroupID CarDetailsID

1 1
1 2
2 3
3 4
3 5

Now we get to the meat.

I can't link the CarGroup table to the fact table as a foreign key because I do not have a unique key with CarGroupID. If I include both the fields CarGroupID & CarDetailsID this gives me uniqueness but I still can't link it to the fact table because it will not use half of the primary/unique index (CarGroupID) it has to use all of it with both fields. So I would have to include the CarDetailsID field in the fact table, but this would defeat the purpose of the bridge table.
I could change my CarDetails table to include a CarGroupID field and just have the CarGroup table having one field entry of CarGroupID. This I think would work, but looking at all the documentation/references in books and the internet they don't do it that way.

So I am either

Doing it wrong and should be doing it this way ........
Being too anal in trying to link the CarGroup to the fact table.
The change I want to do is the correct way of doing it for this instance but in other cases it is not.

At the moment I am in Pattaya Thailand on holiday and this is irritating me so much that it could stop me from going out and having a beer for at least the next hour.

Hope you guys can help me out.

Cheers

Bucko

[ngalemmo]

Why does your fact table need to handle multiple cars on a row? Why not have car as part of the grain (one per row)? What industry is this for?

[bucko]

It is for the tourist industry. A client can phone in a booking for a flight,hotel or car. They can have multiple cars depending how many people are in their party. Or multiple cars due to the fact they are holidaying in multiple countries and have hired cars to get around.

I am trying to promote a data warehouse/data mart in the company. The easiest example for me to make is with cars. Flights and hotels will get interesting.

[ngalemmo]

So, it seems safe to treat each vehicle as a separate row, much like each product is treated separately as a line on an order. Just add a booking id to the fact should you need to group things. However, typical analysis would usually not be dealing with individual bookings, but rather aggregate views of business activity.

Then all you are left with is the details about the vehicle, giving you only one bridge.

[bucko]

Oh I like that. Simple and elegant. I think I was trying to make complication where there wasn't.

Thanks for your help.

[VTK]

How about using another table between fact and car group table where you have only distinct group numbers. I think this approach is also discussed in one of the tips...

[John Simon]

I've got a blog post on this, and how to easily populate your bridge table:
http://jsimonbi.wordpress.com/2011/02/01/populating-a-many-to-many-bridge-table-pt2/

[Sponsored content]