# Practical Database Design Checklist — Database Fundamentals

Source: https://www.skillbyai.com/en/database-fundamentals/b-design

> Apply the fundamentals to design and review a real schema.

## From theory to a working schema

Good database design combines the theory with practical habits. Start from **requirements and queries**: what must be stored, and which questions must be answered quickly? Draw an **ER model**, then map it to tables and normalise to **3NF or BCNF**, denormalising only for measured performance needs. Choose **keys** carefully: a stable **surrogate key** (an auto-increment integer or UUID) is often simpler than a natural key that may change, but keep natural keys **unique** with constraints. Use precise **data types** (DECIMAL for money, timestamps with time zones, appropriate text lengths), **NOT NULL** wherever a value is required, **CHECK** constraints for valid ranges, **foreign keys** for relationships and **unique** constraints for business rules. Design **indexes** for real query patterns and verify with EXPLAIN. Plan **transactions** and isolation levels for concurrent updates. Decide **backups**, **retention** and **migrations** from day one, and document the schema. Constraints in the database protect data from every application and script, not just the one you are writing now.

## Design review checklist

Use it before a schema goes to production.

```text
[ ] every table has a primary key; natural unique identifiers have UNIQUE constraints
[ ] relationships enforced with FOREIGN KEYs and deliberate ON DELETE actions
[ ] no repeating groups or comma-separated lists in columns (1NF)
[ ] non-key facts depend on the key, the whole key, nothing but the key (3NF)
[ ] money as DECIMAL/NUMERIC; times as timestamps with time zone (or UTC)
[ ] NOT NULL and CHECK constraints for required and bounded values
[ ] indexes for the top queries, verified with EXPLAIN; no unused indexes
[ ] transactions around multi-statement changes; isolation level chosen
[ ] backups automated and restore tested; migrations version-controlled
[ ] personal data identified, access restricted, retention defined
```

## The key, the whole key, and nothing but the key

A classic memory aid for 3NF: every non-key attribute must provide a fact about the key (1NF), the whole key (2NF) and nothing but the key (3NF).

**Quiz:** Why enforce rules such as foreign keys and CHECK constraints in the database rather than only in application code?

- [x] Constraints protect the data from every application, script and manual change, not just one code path
- [ ] Databases run faster without constraints
- [ ] Application code cannot validate data
- [ ] Constraints replace the need for transactions

*Answer:* Constraints protect the data from every application, script and manual change, not just one code path. Database constraints apply universally, so no client can bypass them.