A well-designed database schema is the foundation of a maintainable, performant application. Poor schema design causes performance problems, complex queries, data integrity issues, and painful migrations months or years later. This guide covers normalisation, relationships, indexing strategy, naming conventions, and schema migration workflows.
On this page
Normalisation Rules Reference
| Normal Form | Rule | Violation Example | Correct Approach |
|---|---|---|---|
| 1NF - First | Atomic values; no repeating groups; each table has a PK | tags: "javascript,react,node" (multiple values in one column) | Separate tags table with tag_id FK |
| 2NF - Second | 1NF + no partial dependencies (non-key attributes depend on whole PK) | order_items: customer_name depends on customer_id not on (order_id, product_id) | Move customer data to a customers table |
| 3NF - Third | 2NF + no transitive dependencies (non-key attr depends on another non-key) | employees: department_name stored alongside department_id | Separate departments table; reference by department_id FK |
| BCNF - Boyce-Codd | Stricter 3NF: every determinant must be a candidate key | Rare in practice - applies to tables with multiple overlapping candidate keys | Decompose the table based on functional dependencies |
1NF - First
- Rule
- Atomic values; no repeating groups; each table has a PK
- Violation Example
- tags: "javascript,react,node" (multiple values in one column)
- Correct Approach
- Separate tags table with tag_id FK
2NF - Second
- Rule
- 1NF + no partial dependencies (non-key attributes depend on whole PK)
- Violation Example
- order_items: customer_name depends on customer_id not on (order_id, product_id)
- Correct Approach
- Move customer data to a customers table
3NF - Third
- Rule
- 2NF + no transitive dependencies (non-key attr depends on another non-key)
- Violation Example
- employees: department_name stored alongside department_id
- Correct Approach
- Separate departments table; reference by department_id FK
BCNF - Boyce-Codd
- Rule
- Stricter 3NF: every determinant must be a candidate key
- Violation Example
- Rare in practice - applies to tables with multiple overlapping candidate keys
- Correct Approach
- Decompose the table based on functional dependencies
When to denormalise
Normalisation is the default. Denormalise purposefully for performance - e.g. storing a pre-computed order_total on the orders table to avoid summing order_items on every read. Always document why you denormalised.
Relationship Patterns
| Relationship | Implementation | Join Table Needed? | Example |
|---|---|---|---|
| One-to-One (1:1) | FK in either table (usually the "extension" table) | No | users → user_profiles (profile extends user with extra details) |
| One-to-Many (1:N) | FK on the "many" side pointing to the "one" | No | customers → orders (customer can have many orders; order has one customer) |
| Many-to-Many (M:N) | Join/junction table with two FKs | Yes | posts ↔ tags via post_tags(post_id, tag_id) |
| Self-referential | FK pointing to the same table | No (or yes for M:N self) | employees.manager_id → employees.id; categories.parent_id → categories.id |
One-to-One (1:1)
- Implementation
- FK in either table (usually the "extension" table)
- Join Table Needed?
- No
- Example
- users → user_profiles (profile extends user with extra details)
One-to-Many (1:N)
- Implementation
- FK on the "many" side pointing to the "one"
- Join Table Needed?
- No
- Example
- customers → orders (customer can have many orders; order has one customer)
Many-to-Many (M:N)
- Implementation
- Join/junction table with two FKs
- Join Table Needed?
- Yes
- Example
- posts ↔ tags via post_tags(post_id, tag_id)
Self-referential
- Implementation
- FK pointing to the same table
- Join Table Needed?
- No (or yes for M:N self)
- Example
- employees.manager_id → employees.id; categories.parent_id → categories.id
Indexing Strategy
Naming Conventions
DATABASE NAMING CONVENTIONS - [Project Name] TABLES - Plural, snake_case: users, blog_posts, order_items - Join tables: alphabetical order of the two tables: post_tags (not tag_posts) - No abbreviations: customer_addresses not cust_addr - Avoid reserved words: do not name tables: user, order, group COLUMNS - Singular, snake_case: user_id, created_at, is_active - Primary Key: always named 'id' (or table_name_id in join tables: post_id, tag_id) - Foreign Keys: referenced_table_singular_id (e.g. user_id, category_id, parent_id) - Timestamps: created_at, updated_at, deleted_at (soft deletes) - Booleans: is_active, has_subscription, is_verified (prefix with is_/has_) - Avoid: data, info, flag, misc as column names INDEXES - idx_tablename_columnname: idx_users_email, idx_posts_user_id - Composite: idx_orders_status_created_at - Unique: uq_users_email CONSTRAINTS - PK: pk_tablename - FK: fk_tablename_referencedtable: fk_orders_users - Unique: uq_tablename_column: uq_users_email - Check: ck_tablename_column: ck_products_price_positive
Schema Migration Workflow
- 1
Write migrations as code (never modify the database directly in production)
Use a migration tool: Flyway, Liquibase, Prisma Migrate, Alembic (Python), or Knex.js. Migrations are version-controlled SQL scripts.
- 2
Always write the down-migration (rollback)
For every addColumn, create a corresponding dropColumn in the rollback. Test the rollback before running in production.
- 3
Make migrations backward compatible (expand-contract pattern)
Step 1 (expand): add new column as nullable. Step 2: deploy app that writes to both old and new. Step 3 (contract): backfill old data, make column non-null, remove old column.
- 4
Test migration on production-like data volume
Adding an index to a 1M-row table without CONCURRENTLY will lock the table. Test migration timing on a database clone with real data volume.
- 5
Run migrations in a CI pipeline before deployment
Apply migrations automatically in your deployment pipeline. For zero-downtime: run migration before deploying new app code.
What’s next?