should I connect the dimensions or the bridge to fact table?

I have a kind of basic question that I am trying to get my head around..

I have to two dimension tables DimTeam (TeamID, TeamName, TeamStartdate, TeamEnddate) and DimWorktype (WorktypeID, WorkTypeName, WorktypeStartDate, WorktypeEnddate).

There is a many to many relation between teams and their worktypes which is resolved by a bridge table DimTeam_worktype --Team_WorktypeID, TeamID(FK to DimTeam), WorktypeID(FK to DimWorktype).

Facts are analyzed with respect to team separately, worktype separatley and also sometimes in a heirachichal fashion with respect to teams and worktypes (like a drill down on teams to get all the measures under different worktypes for a particular team)

I could just connect the dimensions directly to fact table without the bridge but I need the bridge for relating teams to their worktypes for teams that are not associated with any facts.

So the question is should I connect the dimensions separately to the fact table with two separate foreign keys or should I connect the dimensions to the fact table via the bridge table with a single foreign key.

I am just trying to find out whats the difference with respect to performance or any other advantages of one over the other...

Greatly appreciate any help!!

It is not a bridge, it is a factless fact table.

Here is why: a bridge is a table that sits between a fact and a dimension to resolve many to many relationships. A fact table is a table that relates dimensions. You are trying to relate dimensions.

You only need a bridge if there is a many-to-many relation to a fact. That is not the case, a fact relates directly to a work type.

@ ngalemmo..Thanks for the correction

I guess I should say factless fact table instead of the bridge term. But the question still remains for me if I should connect the two dimensions team and worktype directly to the actual fact table using two separate foreign keys or via the second factless fact table which results in a single foreign key in the fact table.

I hope I am making my question clear. I am trying to understand what difference is there between the two ways of connecting two related dimensions to fact table and if one is advantageous over the other?

Before calling the Team-WorkType a factless fact table, you need to make sure the relationship is true many to many at one point of time in dimensional sense, meaning a team may belong to multiple work types concurrently, not just across a period of time. Otherwise, I would still say it's one to many between work type and team and would model them into a single type 2 team dimension.

If it is many to many, periodic snapshot factless fact (coverage) is the way to go. The connection between Team-WorkType fact and other fact table is the team key or worktype key, but not both as one is multivalued dimension to the other in a fact table.

From you description, an activity has only one work type and one team doing the activity, so that fact table has a FK to both team and work type. There isn't a many to many relationship for an activity. You don't need a bridge.

If you want to track teams and work types, that is its own fact table.

@ hang... the team and worktype are infact many to many at any given point of time....dim team is a type 2 with start and end date for teams, dim worktype is type 2 with start and end date for worktype, the relation between team and worktype is many to many which should also be a type 2 with start and end date for the association of team and worktype

@ ngalemmo.. I think you got it right...

For an activity in the fact table, there is only 1 team and 1 worktype responsible for the activity. so, I joined the activity fact table with the two dimensions team and worktype separately. In order to see teams and their associated worktypes independent of activity, I had the second fact table (the one I called the bridge table by mistake) joining the two dimensions along with a start and end date for the association. I am good with this.

ngalemmo...but whats bothering me is that why cant I take the primary key from this second fact table and join this to the activity fact table instead of joining the two dimensions, team and worktype separately to the activity fact table. This way I bring the foreign keys in the activity fact table from 2 (teamIDkey, worktypeIDkey) to 1 (team_worktypeIDkey). Is there any advantages of one over the other...please help me understand this...

There are a lot of reasons: it violates the dimensional form, it complicates the model and it hurts performance.

A fundimental foundation of the dimensional approach is a fact table is independent of any other fact table. They can be combined through common dimensions, but you don't design them with direct dependencies as you would in an ER model. As each stands on its own, expansion to other subject areas is simple and each star schema is easy to understand.

@ngalemmo... thank you for the answer...

After much reading, one thing I understood is since fact tables are expected to grow faster to the relatively static dimension tables, it would obviously be much faster to analyze a fact table over a small dimension table directly joined to it instead of the join having to go through another large fact table coming in between those two.. Am I on the right track?

Yes.

I've got a related question about bridge tables, what naming convention would you recommend?

First I subscribe to Nick's definition of a bridge table, resolve many to many relationships between fact and dimension.
Normally in my dimensional models fact tables are prefixed with fact_ and dimension tables with dim_ or sometimes fact tables reside in fact schema and dimension tables in a dimension schema. Whatever way it's done there is a clear distinction between the two.

Factless fact tables clearly reside in the the fact collection and snowflakes in the dimension collection. I see the bridge table as a hybrid, does it warrant a separate collection or should it just go in one collection (dim or fact) or the other and then be consistent?

Anyone have strong thoughts one way or another?

A bridge is a third class of table.