Skip to content

16-schema-design-normalization.md

Latest commit

 

History

History
72 lines (52 loc) · 3.17 KB

16-schema-design-normalization.md

File metadata and controls

72 lines (52 loc) · 3.17 KB
Raw

Chapter 16: Schema Design & Normalization

Schema design is the blueprint of your database. A well-designed schema ensures data integrity, minimizes redundancy, and optimizes for either fast writes (OLTP) or fast reads (OLAP).

16.1 Normalization (3NF)

Normalization is the process of organizing data to reduce redundancy and improve data integrity. Most application databases use Third Normal Form (3NF).

  • 1NF: Atomic values, unique rows, primary keys.
  • 2NF: 1NF + no partial dependencies (every non-key column depends on the entire primary key).
  • 3NF: 2NF + no transitive dependencies (non-key columns don't depend on other non-key columns).

Note

The Olist database is a great example of a normalized schema, separating customers, orders, and products into distinct tables to avoid repeating descriptive data.

16.2 Star Schema (OLAP Design)

While 3NF is great for applications, it's often slow for complex analytics because of the many joins required. Analytical Data Warehouses prefer a Star Schema.

  • Fact Tables: Central tables containing quantitative metrics (e.g., revenue, quantity) and foreign keys.
  • Dimension Tables: Surrounding tables containing descriptive attributes (e.g., product_name, customer_city, date_info).
-- Building a Date Dimension
CREATE TABLE dim_date AS
SELECT
    datum AS date_key,
    EXTRACT(YEAR FROM datum) AS year,
    TO_CHAR(datum, 'Month') AS month_name
FROM GENERATE_SERIES('2017-01-01'::DATE, '2018-12-31'::DATE, '1 day') AS datum;

16.3 Denormalization

Denormalization is the intentional introduction of redundancy. By "flattening" tables into a single wide table, you can sometimes achieve much faster read performance for specific analytical dashboards at the cost of storage and update complexity.

-- Creating a flattened table for high-speed analysis
CREATE TABLE flat_orders_dump AS
SELECT o.order_id, c.customer_city, p.product_category_name, oi.price
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.product_id;

Exercises

  1. Create a dim_seller table containing the seller_id, city, and state.
  2. Using the Fact and Dimension tables, write a query to find the total revenue generated by sellers in "sao paulo" during 2018.
  3. Identify a table in the current schema that might benefit from further normalization (if any).
  4. Why might you choose a Star Schema over a 3NF schema for a BI dashboard?
Solutions 
-- Exercise 1
CREATE TABLE dim_seller AS SELECT seller_id AS seller_key, seller_city, seller_state FROM sellers;

-- Exercise 2
SELECT SUM(f.revenue_amount) FROM fact_order_sales f JOIN dim_seller s ON f.seller_key = s.seller_key JOIN dim_date d ON f.date_key = d.date_key WHERE d.year = 2018 AND s.seller_city = 'sao paulo';

-- Exercise 3
-- Discussion: The orders table is quite wide; some might move status history to a separate table if tracking every state change is important.

-- Exercise 4
-- Star Schemas simplify queries (fewer joins) and are optimized for the "heavy aggregation" patterns typical of BI tools.