Concept
Relational Modeling & Normalization
Relational databases (like PostgreSQL, MySQL, SQLite) store data in structured tables where rows represent records and columns represent attributes.
Normalization is the process of organizing data to reduce redundancy and prevent update anomalies:
- 1st Normal Form (1NF): Cell values must be atomic (no arrays/comma-separated lists in a single cell).
- 2nd Normal Form (2NF): Must satisfy 1NF, and all non-key attributes must depend on the primary key (no partial dependencies).
- 3rd Normal Form (3NF): Must satisfy 2NF, and no non-key columns can depend on other non-key columns (no transitive dependencies).
Primary Keys & Foreign Keys
- Primary Key (PK): A column that uniquely identifies a row (e.g.,
id). - Foreign Key (FK): A column pointing to the PK of another table, creating a relationship.
- Referential Integrity: Enforces that an FK value must exist in the target table. Configure deletion rules like
ON DELETE CASCADE(deletes children if parent is deleted) orON DELETE SET NULL.
SQL JOINS
Joins merge columns from multiple tables based on key matches:
-- Join users and their orders
SELECT users.name, orders.amount
FROM users
INNER JOIN orders ON users.id = orders.user_id;INNER JOIN Returns rows only when there is a match in BOTH tables.
LEFT JOIN Returns ALL rows from left table, plus matching rows from right (null if no match).
RIGHT JOIN Returns ALL rows from right table, plus matching rows from left (null if no match).
FULL JOIN Returns rows when there is a match in EITHER table.Common Mistakes
1. Storing array values as comma-separated strings
Writing strings like "math,science" in a subjects column violates 1NF. It prevents database indexing, makes searching slow, and complicates aggregation queries. Use a separate many-to-many join table instead.
2. Blindly using ON DELETE CASCADE
Using cascade deletes on critical tables can result in massive accidental data loss (e.g., deleting a tenant account automatically deletes all billing histories). Use ON DELETE RESTRICT or soft deletes for critical business records.
Best Practices
- Choose appropriate primary keys: Use UUIDs (v4 or v7) instead of auto-incrementing integers for distributed tables to prevent ID enumeration attacks and merge conflicts.
- Always index Foreign Keys: Relational databases do not automatically index foreign keys; build indexes on FK columns to speed up JOIN lookups.
- Apply constraints: Utilize constraints (
NOT NULL,UNIQUE,CHECK) at the database level to guarantee data integrity, rather than relying solely on server validation.
