CODEWITHSUNDEEP
sql-serverdatabasedatabase-designdata-warehousefact
GileBrt
GileBrt

Reputation: 1838

Is it ok to have multiple fact tables that are connected to the same dimension tables without using a link table?

Let's say that in my database model I have three fact tables. These fact tables have same dimension tables (so called conformed dimensions). I know that I shouldn't connect directly fact tables (since direct connection can cause double-counting of some facts), but only through the dimension tables. What I am interested in is can I connect every fact with every dimension table without problems? I looked for an answer a lot and the opinions are divided. Some say there is no problem, the others say that because of this fact tables can associate with each other and circular references can occur; and that in these cases so called link table should be used. Is this link table really necessary or can this work without it?

Upvotes: 1

Views: 2697

Answers (2)

GileBrt
GileBrt

Reputation: 1838

I implemented the model (in MS SQL), and I'm sharing here my experience in case anyone is interested in this in future.
In the end I created five fact tables (model turned out to be more complex), they are all connected to all existing dimension tables (six of them) directly. I didn't use the link table.
This model is in usage for almost five months now and so far no problems appeared.

Upvotes: 1

Wes H
Wes H

Reputation: 4439

If a dimension can describe an aspect of the fact event, you should connect it so it can be used in analytics.

However, you shouldn't force a relationship to connect a fact to a dimension that it does not need. That will make your model confusing and bloated.

You are correct that you should not connect facts directly. The model does not function that way. You'll want to read up on the purpose of facts and dimension to understand why.

You should be able to navigate between related events through the common dimensions, but that is not a circular reference. A circular reference prevents a value from being returned because there is not a bottom to the relationship.

If entities have a many to many relationship, you can use link/bridge tables to expand the relationship into multiple one to many/one to one relationships. That is complicated to model and too much to explain as part of this question.

If you want more, please post some of your model so we can focus on the specific needs of your question.

Upvotes: 2

Related Questions