Bridge table vs 1 to 1 relationship

[MagicMind]

Fact Table 1 to 1 to Dim Entity where the join column between the 2 tables is the concat of the Transaction ID,EntityID and Snapshot :
Transaction ID EntityID Snapshot ColumnForJoin
1                     12          01012024 11201012024
2                     13          01012024 21301012024
3                     15          01012024 31501012024
1                     10          01022024 11001012024
5                     16          01022024 51601012024
7                     18          01022024 71801012024

and Dim Entity :

Entity ID Entity Label ColumnForJoin
12          A                   11201012024
13          B                    21301012024
15         C                     31501012024
10         D                     11001012024
16         E                      51601012024
18         F                      71801012024

or use a bridge table Bridge Entity where :

Transaction ID EntityID Snapshot
1                     12          01012024
2                     13          01012024
3                     15          01012024
1                     10          01022024
5                     16          01022024
7                     18          01022024

Fact Table 1 to many to Bridge Entity based on the Transaction ID for both tables
Dim Entity 1 to many to Bridge Entity based on the Entity ID for both tables
Dim Date 1 to many to Bridge Entity based on the snapshot for both tables

what is the best way ?

[lbendlin · Accepted solution]

You should always aim for a Star Schema. Dimension tables controlling fact tables in 1:* single direction relationships.

1:1 relationships are usually an indication that the two tables should rather be combined.

[AmiraBedh · Accepted solution]

I agree with @lbendlin  about his advice.

In your case for if you want a bridge table there is a small correction in the relationship between Fact Table and Bridge Entity.

Even the Fact Table 1 to many to Bridge Entity based on the Transaction ID for both tables  won't be able to assure the unicity in this case.

[lbendlin · Accepted solution]

You should always aim for a Star Schema. Dimension tables controlling fact tables in 1:* single direction relationships.

1:1 relationships are usually an indication that the two tables should rather be combined.