Normalization reduces redundancy by splitting data into tables and linking with foreign keys. First normal form (1NF): atomic values, no repeating groups. 2NF: no partial dependency (non-key attributes depend on the whole key). 3NF: no transitive dependency (non-key attributes depend only on the key). Normalized design avoids update anomalies (e.g. changing an address in one place).

For OLTP (transactional) systems, 3NF is a good default: one place for each fact, clear relationships.

Denormalization stores redundant data to speed up reads or simplify queries. Example: store user_name on orders so you do not JOIN users every time you display an order. Trade-off: when the user’s name changes, you must update all their orders (or accept stale data). Use when: read-heavy, write-rare; or when the source is slow or hard to join (e.g. across services).

Common in document stores (embed related data) and in read-optimized views or caches.

One-to-many: one parent, many children (e.g. user → orders). Store foreign key on the child (orders.user_id). Many-to-many: (e.g. users ↔ roles). Use a junction table (user_roles with user_id and role_id). In document DBs you might embed the many side (one-to-few) or store IDs and resolve separately.

Choose based on query patterns: how often you need the related data and whether you update it independently.

Soft delete: add deleted_at; "delete" by setting it; filter WHERE deleted_at IS NULL on reads. Enables undelete and audit. Audit columns: created_at, updated_at, created_by for traceability. UUID vs auto-increment: UUIDs are globally unique and safe for distributed systems; auto-increment is smaller and ordered. Polymorphic association: when one table references multiple entity types (e.g. commentable_id + commentable_type); use sparingly, consider separate tables per type.