Bridge Tables (Factless Fact Tables)

Scenario: Users and roles in an enterprise application

Each user can have multiple roles (e.g., admin, editor), and each role can be assigned to multiple users. A bridge table (user_role_bridge) connects users and roles, enabling efficient querying of these complex relationships.

The bridge table handles the many-to-many relationship, making it easy to retrieve all roles assigned to a user. This design simplifies the query and avoids the need for multiple joins or complex subqueries.

SQL
-- Find all roles assigned to a specific user
SELECT r.role_name 
FROM users u
JOIN user_role_bridge urb ON u.user_id = urb.user_id
JOIN roles r ON urb.role_id = r.role_id
WHERE u.user_name = 'Alice';

Scenario: Customers and orders in an e-commerce platform

Each customer can place multiple orders, but each order is placed by only one customer. Here, a direct join between customers and orders is sufficient.

Since this is a one-to-many relationship, the following query efficiently retrieves all orders placed by a specific customer using a simple join, without the need for a bridge table.

SQL
-- Find all orders placed by a specific customer
SELECT o.order_id, o.order_date 
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
WHERE c.customer_name = 'Bob';

Scenario: Products and promotions in e-commerce

Products can be part of multiple promotions, and promotions can apply to multiple products. A product_promotion_bridge table helps manage these relationships efficiently.

Bridge tables help manage overlapping promotions efficiently, which is crucial in dynamic pricing and personalized marketing strategies.

SQL
-- Find all products in a specific promotion
SELECT p.product_name 
FROM promotions pr
JOIN product_promotion_bridge ppb ON pr.promotion_id = ppb.promotion_id
JOIN products p ON ppb.product_id = p.product_id
WHERE pr.promotion_name = 'Summer Sale';

Scenario: ML model deployment in cloud environments

Models can be deployed in multiple environments, and each environment can host multiple models. A model_environment_bridge table helps manage these deployments, ensuring that only the correct versions are active in production.

In cloud-based AI/ML platforms, managing model versions and deployment environments is critical for ensuring correct versions are active in production. The bridge table makes this process more manageable.

SQL
-- Find all active models in a specific environment
SELECT m.model_name, m.version 
FROM environments e
JOIN model_environment_bridge meb ON e.environment_id = meb.environment_id
JOIN models m ON meb.model_id = m.model_id
WHERE e.environment_name = 'Production' AND meb.active_flag = True;