Why Data Modeling Matters#

Code is transient. Frameworks come and go. APIs are deprecated and replaced. Data is forever.

If you build a poor data model, you will spend the entire lifespan of the application writing complex, inefficient SQL queries to work around the structural flaws. You will struggle to enforce data integrity, and eventually, business users will lose trust in the system because reports generate conflicting numbers.

Data modeling is the process of defining the structure, relationships, and constraints of your data. It generally falls into two distinct paradigms based on the workload:

1. OLTP (Online Transaction Processing): Applications that process day-to-day operations (e.g., e-commerce checkouts, inventory updates). The goal is to process thousands of small INSERT and UPDATE statements per second with zero data anomalies.
2. OLAP (Online Analytical Processing): Data warehouses that aggregate historical data (e.g., "What were total sales by region over the last 5 years?"). The goal is to process massive SELECT queries across millions of rows quickly.

You cannot design a single schema that is optimal for both OLTP and OLAP. Trying to do so results in a system that is mediocre at both.

OLTP Normalization (1NF, 2NF, 3NF)#

For OLTP systems, we use a process called Normalization. Normalization systematically removes data redundancy. If a piece of data is stored in only one place, you can update it instantly without worrying about keeping multiple copies in sync.

The rules of normalization are divided into "Normal Forms."

First Normal Form (1NF)

Rule: Every column must hold an atomic (indivisible) value, and there must be no repeating groups.

Imagine an ORDERS table where you store items as a comma-separated list:

This violates 1NF. You cannot easily query "How many mice did we sell?" without using expensive string-parsing functions (like LIKE '%Mouse%').

The Fix: Break the repeating group into a child table (ORDER_LINES), where each row represents a single atomic item.

Second Normal Form (2NF)

Rule: The schema must be in 1NF, and all non-key attributes must be fully dependent on the entire primary key.

This rule only applies when you have a composite primary key (a key made of multiple columns). Imagine an ORDER_LINES table with a composite key of (Order_ID, Item_ID):

Here, Quantity depends on the combination of Order_ID and Item_ID. However, Item_Name only depends on Item_ID. This violates 2NF. If you update the name of Item 100, you have to update it across millions of order lines.

The Fix: Move Item_Name to a dedicated ITEMS table.

Third Normal Form (3NF)

Rule: The schema must be in 2NF, and there must be no transitive dependencies. Non-key attributes must depend only on the primary key, and not on other non-key attributes.

Imagine a CUSTOMERS table:

While Zip_Code depends on the Customer_ID, City and State actually depend on the Zip_Code. This is a transitive dependency and violates 3NF. If the postal service renames a city, you must update thousands of customer records.

The Fix: Move City and State to a LOCATIONS table, and only store the Zip_Code in the CUSTOMERS table as a foreign key.

Keys, Constraints, and Data Integrity#

A normalized schema is useless if the database doesn't enforce the relationships. Data integrity must be enforced at the database level using constraints, not just relying on application logic. Application bugs happen; database constraints are ironclad.

Surrogate vs. Natural Keys

A Primary Key must uniquely identify a row. You have two choices:

1. Natural Key: A business attribute that is inherently unique (e.g., Social Security Number, Email Address, Product Barcode).
2. Surrogate Key: A system-generated, meaningless number (e.g., an auto-incrementing integer or a UUID).

Always use Surrogate Keys. Natural keys are dangerous because business rules change. If you use an Email Address as a primary key, and the user changes their email, you must cascade that update across hundreds of child tables. A surrogate key never changes.

Foreign Keys

Foreign keys enforce referential integrity. If an ORDER_LINE points to Item_ID = 5, the database guarantees that Item 5 exists in the ITEMS table.

ALTER TABLE order_lines
ADD CONSTRAINT fk_order_item
FOREIGN KEY (item_id) REFERENCES items(item_id);

Check Constraints

Use Check constraints to enforce domain logic at the storage level.

ALTER TABLE employees
ADD CONSTRAINT chk_salary_positive
CHECK (salary > 0);

ALTER TABLE orders
ADD CONSTRAINT chk_status
CHECK (status IN ('PENDING', 'SHIPPED', 'CANCELLED'));

The Entity-Attribute-Value (EAV) Anti-Pattern#

When building dynamic applications (like an e-commerce site where different product categories have entirely different specifications), junior developers often fall into the EAV (Entity-Attribute-Value) trap.

Instead of creating columns for color, weight, and size, they create a single metadata table:

Why EAV is a Nightmare:

1. No Data Types: Because Value is a single column, it must be stored as a VARCHAR. You cannot enforce that Weight is a number. You cannot easily run queries like WHERE Weight > 10 without casting strings to numbers, which destroys performance.
2. SQL Complexity: To retrieve a product with its color, weight, and size, you have to perform three separate self-joins on the EAV table.
3. No Constraints: You cannot easily enforce that a T-Shirt must have a size attribute, but a Laptop must not.

The Modern Solution: If you need dynamic, flexible schemas in a relational database, use JSON columns. Oracle, PostgreSQL, and MySQL all support native JSON data types. You can store dynamic attributes in a single JSON document column, and the database can even index the keys inside the JSON!

OLAP Dimensional Modeling (Star vs. Snowflake)#

If you try to run a massive analytical report against a highly normalized 3NF OLTP database, it will likely time out. 3NF requires too many joins.

For Data Warehouses (OLAP), we use Dimensional Modeling, created by Ralph Kimball. The primary design is the Star Schema.

The Star Schema

A Star Schema intentionally denormalizes data to optimize for heavy read queries. It consists of a central Fact table surrounded by Dimension tables.

1. Fact Tables: These hold the measurable, quantitative metrics (e.g., Sales_Amount, Discount, Quantity). Fact tables are massive (billions of rows) but narrow (only containing foreign keys and numbers).
2. Dimension Tables: These hold the descriptive attributes (the "Who, What, Where, When"). For example, a DIM_STORE table would contain the store name, region, manager, and district. Dimension tables are heavily denormalized.

SELECT 
  d.year,
  s.region,
  p.category,
  SUM(f.sales_amount) as total_sales
FROM fact_sales f
JOIN dim_date d    ON f.date_id = d.date_id
JOIN dim_store s   ON f.store_id = s.store_id
JOIN dim_product p ON f.product_id = p.product_id
GROUP BY d.year, s.region, p.category;

Star vs. Snowflake Schema

A Snowflake Schema is a Star Schema where the dimension tables are partially normalized. For example, instead of storing Region_Name directly in DIM_STORE, the DIM_STORE table joins to a DIM_REGION table.

While Snowflaking saves a small amount of disk space, it re-introduces the join penalties we were trying to avoid. In modern data warehousing (where disk space is cheap), Star Schemas are heavily preferred over Snowflake Schemas.

Slowly Changing Dimensions (SCD)#

In a data warehouse, you must track historical changes. If a customer lives in "New York" in 2025 and buys a laptop, and then moves to "California" in 2026 and buys a mouse, how do you report on 2025 regional sales?

If you just update the DIM_CUSTOMER table directly (overwriting New York with California), your 2025 regional sales report will retroactively shift the laptop sale to California. This is a disaster.

We handle this using Slowly Changing Dimensions (SCD).

SCD Type 1: Overwrite

The easiest approach. You simply UPDATE the dimension record. You lose all history. Only use this for corrections to typos or for attributes where history genuinely does not matter.

SCD Type 2: Add a New Row (The Industry Standard)

Instead of updating, you insert a brand new row for the customer, and use Effective_Date and Expiration_Date columns to track the timeline.

Notice that we use a surrogate key (Cust_Key), NOT the natural customer ID. When the laptop was sold in 2025, the Fact table recorded Cust_Key = 901. When the mouse was sold in 2026, the Fact table recorded Cust_Key = 902.

Now, your 2025 sales reports correctly tie back to New York, and 2026 reports tie to California!

Common Gotchas for Architects#

Key Takeaways#