RDBMS Schemas

The list above names the schema patterns. The sections below show how they relate, which workload each suits, and example SQL for the main dimensional shapes.

The following are common database schemas used in relational database management systems (RDBMS), including the Star Schema, from which the Snowflake Schema is derived:

1. Star Schema:
   • The foundational schema that involves a central fact table connected directly to several dimension tables.
   • Simpler structure with denormalized dimension tables.
   • Offers faster query performance due to fewer joins.
2. Snowflake Schema:
   • Derived from the Star Schema.
   • Normalizes dimensions into multiple related tables.
   • Reduces redundancy but can make queries more complex.
3. Galaxy Schema (Fact Constellation Schema):
   • Contains multiple fact tables that share dimension tables.
   • Used for more complex data warehouses with multiple data marts.
4. Hierarchical Schema:
   • Organizes data in a tree-like structure.
   • Used for databases that require a clear parent-child relationship, like organizational charts.
5. Network Schema:
   • Similar to the Hierarchical Schema but allows more complex relationships with many-to-many connections.
   • Often used for more complex, non-hierarchical relationships.
6. Flat Schema:
   • A single table or a simple structure without hierarchical relationships.
   • Typically used for small, simple databases.

These schemas are utilized based on the specific needs of the database structure, data integrity, and query complexity.

1. How the Patterns Relate

Schema patterns fall into two families. Transactional systems use normalized designs, usually third normal form, so that each fact is stored once and inserts and updates touch few rows. Analytical systems use dimensional designs, where data is shaped around business processes and the questions people ask. The star schema is the base dimensional pattern: one fact table joined to denormalized dimensions. A snowflake schema normalizes dimension hierarchies into separate tables. A galaxy, or fact constellation, schema has several fact tables that share conformed dimensions such as date and customer, so sales and inventory can be compared on the same keys.

2. Where Each Pattern Is Used

A single organization usually uses several of these at once. Source applications stay in third normal form. A warehouse may land raw data in staging tables, integrate it in a normalized or Data Vault layer that tracks history and lineage, and then publish star schemas as data marts for reporting. Wide denormalized tables are sometimes built on top of marts for a single dashboard or machine learning feature set.

The choice between the patterns comes down to the workload. Normalization optimizes for correctness under frequent small writes: an address changes in one row, and every order sees the new value through a join. Dimensional modeling optimizes for reads by many people asking aggregate questions: data is loaded in batches by controlled pipelines, so redundancy is managed by the load process rather than by constraints. Columnar warehouses have narrowed the performance gap for joins, which is why snowflaked dimensions and even wide tables are more acceptable today than in row-oriented systems, but the clarity a star schema gives to business users remains its main advantage.

3. When to Use Which

4. Worked Example: Galaxy Query Across Two Facts

-- Two fact tables sharing conformed dim_date and dim_product
WITH sales AS (
  SELECT d.month_key, f.product_key, SUM(f.quantity) AS units_sold
  FROM fact_sales f JOIN dim_date d ON f.date_key = d.date_key
  GROUP BY d.month_key, f.product_key
),
stock AS (
  SELECT d.month_key, i.product_key, AVG(i.on_hand) AS avg_on_hand
  FROM fact_inventory i JOIN dim_date d ON i.date_key = d.date_key
  GROUP BY d.month_key, i.product_key
)
SELECT p.category, s.month_key,
       SUM(s.units_sold)  AS units_sold,
       SUM(k.avg_on_hand) AS avg_on_hand
FROM sales s
JOIN stock k        ON k.month_key = s.month_key AND k.product_key = s.product_key
JOIN dim_product p  ON p.product_key = s.product_key
GROUP BY p.category, s.month_key;

Each fact is aggregated to a common grain before joining. Joining two fact tables directly at their native grain multiplies rows and inflates the sums, a mistake often called a fan trap.

5. Pitfalls

• Choosing a schema before declaring the grain of each fact table.
• Non-conformed dimensions (two different customer tables) make cross-process queries unreliable.
• Over-snowflaking small dimensions adds joins without saving meaningful space.
• Using an analytical schema for a write-heavy application, or the reverse.

Whatever pattern you pick, document the grain and keys of every table; that documentation prevents most of the problems listed above.

6. Related Topics

• Star Schema, Snowflake Schema
• Kimball Architecture, Database Schema Design

Featured Videos