1

I am designing a star schema and come across this problem. I have two dimension table - a Product (Key = Product Id) and a Customer (Key = Customer Id). In another table there are three fields - Business Unit, Product Id and Customer Id.In this table one Product id refers to many Business Units and many Customer Ids. My question is do i need to place the third table as a bridge table between Product Dim and Customer Dim? Or what should be a better way to solve this?

Product Dim

Product ID Product Name .... ....etc

Customer_Dim

Customer_Id Customer Name Customer Type .....etc

Third Table

Business Unit Product ID Customer ID .....etc

Right now i created the third table as Bridge table and joined with Product and Customer Dim. The third table will have Product_id and Customer_id as FKs and Business Unit as the attribute. Is this the right approach?

Thanks, Arun

Arun.K
  • 103
  • 2
  • 4
  • 21
  • Yes, you have to use the third table as a bridge table and to create two foreign keys toward the other tables. – Polux2 Sep 02 '15 at 21:58
  • So does the bridge table contains only FKs? In my case the bridge table has two FKs (product id, customer id) and couple of other attributes like Business unit etc. Is that a correct way or should i not include attributes in bridge table? – Arun.K Sep 02 '15 at 22:06

1 Answers1

3

Yes, this is what's called a many-to-many relation. A product can be connected to many companies, and a company can be connection to many products.

Guffa
  • 687,336
  • 108
  • 737
  • 1,005
  • Thanks Guffa. So in fact table do i need to bring both the FKs or only one? – Arun.K Sep 02 '15 at 22:07
  • @Arun.K: You need both foreign keys, otherwise you only have a connection between two tables and a table that is unrelated. – Guffa Sep 02 '15 at 22:13
  • Can i have attributes in the bridge table apart from the FKs. Since i am using the third table as Bridge it has attributes like Business Unit etc – Arun.K Sep 02 '15 at 22:15
  • @Arun.K: Yes, you can. That would be data that applies to the relation. – Guffa Sep 02 '15 at 22:37