Should I use a Bridge Table?

[MikeGB]

Hi All

I am currently designing my company's first Data Warehouse, In the past they have just restored business databases and queried them. I have worked in a BI environment before but I have never used a bridge table and so I want your advice on this to see if I need one or not.

We currently collect all credit and debit card transactions from shops, supermarkets etc on a daily basis for Merchants that are our clients. In our business application the structure of the clients is as follows.

Account (Sometimes a parent account) --> Site --> Terminal (point of sale)

Each account can be linked to multiple sites and each site to multiple terminals.

So at the moment I have a Dimension for each account site and terminals, each account and site are unique but the terminal number can be used on a different site and account if there is a change of owner, so this is a slowly changing dimension.

The daily data that we get is at a terminal level. So I have two options:

1) Put the Account Id, Site id and terminal Id FK in the fact tables along side each other or
2) create a bridge table and use the keys from that.

Any suggestions would be appreciated.

Thanks
Mike

[BoxesAndLines]

You would only need a bridge table if a transaction applied to multiple terminals. I don't believe that is the case, so Option 1.

[MikeGB]

Your right, only at transaction can go through only 1 terminal. Thanks for the response on this. I shall design it without the bridge table and see how that works then.

Cheers
Mike