Relational Data Modeling - Association Table (Bridge, Cross)

1 - 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:

3 - Modeling

3.1 - 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.

3.2 - 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

3.2.1 - 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.


Data Science
Data Analysis
Statistics
Data Science
Linear Algebra Mathematics
Trigonometry

Powered by ComboStrap