Practical Database Design Checklist

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.

[ ] 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).

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

• 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