design_database_schemalisted

# design_database_schema Database schema design decisions are load-bearing — they're expensive to change later and affect query performance, data integrity, and code complexity for the life of the system. Get them right at the start. ## Process: Design Before Migrating Before writing any migration, design the schema by: 1. Listing the entities and their core attributes. 2. Identifying relationships between entities. 3. Normalizing to remove redundancy. 4. Considering query patterns and adding appropriate indexes. 5. Planning for evolution (how will this change?). ## Core Concepts ### Normalization — How Much? **3NF (Third Normal Form)**: the practical target for most systems. - Each table represents one thing. - Non-key attributes depend only on the primary key. - No repeated groups or redundant data. ```sql -- Denormalized (bad — stores redundant data) orders(id, user_id, user_email, user_name, product_name, product_price, ...) -- Normalized (good — each table owns its data) users(id, email, name, ...) products(id, name, price, ...) orders(id, user_id, created_at, ...) order_items(id, order_id, product_id, quantity, unit_price) ``` **When to denormalize**: when you have a proven read performance problem and the redundancy is acceptable. Always normalize first, denormalize with measurement. ### Relationships **One-to-Many (most common)** ```sql -- User has many orders users(id PK, email, name) orders(id PK, user_id FK → users.id, status, total) ``` **Many-to-Many