About
An association table (bridge table) is a table that permits to implement:
Because most of database only support one-to-many relationships, it is necessary to implement other relationships physically via a third junction table.
An association table is also known as:
- cross-reference
- associative table
- Referencing table
- factless fact table
Articles Related
Modeling
One-to-One
To model a one-to-one relationship:
- the association table would store the link between the 2 tables
- and a unique constraint would be defined on each foreign key to ensure the one to one multiplicity.
Many-to-many
A Many-to-many relation is implemented via two one-to-many relationships:
- A → AB
- and B → AB.
In this case the logical primary key for AB is formed from the two foreign keys (i.e. copies of the primary keys of A and B).
The third table can be a fact table
Weighing Factor
The weighing factor is there mainly to distribute the dollar amounts evenly across the categories, where the sum of the parts cannot be greater than the total
Example:
For example, in a sport bet application:
- Bob has interests in Football and Baseball and spends 20,
- Alice has interests in Football, Soccer and Tennis and spends 30.
A revenue by breakdown cannot be…
Football: $50
Baseball: $20
Soccer:$30
Tennis: $30
because the total would 130 when in reality it's only 50.
The weighing factor will correct this logic. Ie the amount would be divide by the sports of interest for each person
The result would then be:
Football: 20/2 (Bob) + 30/3 (Alice) = $20
Baseball: 20/2 (Bob) = $10
Soccer: 30/3 (Alice) = $10
Tennis: 30/3 (Alice) = $10
with a total of 50
The problem with the weigh factor is that the user needs to know what the factor is for.