Modeling Multi-Valued Attributes With Bridge Tables

Use bridge tables to represent scenarios where a single entity possesses multiple active attributes, such as multiple phone numbers or product categories.

In this lesson, you will learn how to use bridge tables to cleanly associate multiple attribute values with a single dimension record.

The Problem: Multi-Valued Attributes

In data modeling, we often assume a one-to-one relationship between an entity and an attribute. For example, a Customer has one Region. However, what happens when a Customer belongs to multiple Market Segments simultaneously, or a Product has multiple Tags?

If you store these as a comma-separated string (e.g., "Retail, Wholesale, Online"), your analytical queries become slow, complex, and nearly impossible to aggregate. You cannot easily count how many customers are in the "Retail" segment without performing expensive string parsing.

The Solution: The Bridge Table

Instead of flattening the data, we create a Bridge Table. This acts as a junction that sits between your main Dimension table and a new Attribute lookup table.

Suppose you have a Product that belongs to several Categories. Instead of forcing these into the Dim_Product table, you create three tables:

Dim_Product: Contains the product details.
Dim_Category: Contains the unique list of all possible categories.
Bridge_Product_Category: A table that links product keys to category keys.

-- The Bridge Table structure
CREATE TABLE Bridge_Product_Category (
    product_key INT,
    category_key INT
);

-- Example data: Product 101 belongs to both 'Electronics' (ID 1) and 'Sale' (ID 5)
INSERT INTO Bridge_Product_Category VALUES (101, 1);
INSERT INTO Bridge_Product_Category VALUES (101, 5);

Why Use This Approach?

By decoupling the attributes from the dimension record, you gain two major benefits:

Granular Filtering: You can join through the bridge to filter by a specific category without impacting other product attributes.
Scalability: If a product gains a new category, you simply insert a new row into the bridge table. You don’t need to update your main Dim_Product table or create columns like category1, category2, and category3, which are difficult to maintain.

When building reports, your BI tool will join the Fact table to the Bridge table, and then to the Dimension table, ensuring accurate slicing and dicing of multi-valued data.

Key Takeaway: Bridge tables decouple multi-valued attributes from core dimension entities, enabling efficient filtering and clean maintenance of complex relationships.