Relational Databases and SQL Foundations: Model the Invariant First

Start with entities and invariants

Relational design begins with the facts the application must preserve. An order belongs to a customer; an order line refers to a product; a payment request should not be applied twice. Tables represent sets of rows with defined columns, while keys identify rows and constraints express rules that should hold regardless of which application wrote them.

Choose a primary key that remains stable for the lifetime of a row. A surrogate identifier is common, but it does not replace domain constraints. If a username or external payment reference must be unique, encode that rule with a unique constraint. A foreign key makes a relationship explicit and prevents dangling references. A check constraint can enforce a local invariant such as a non-negative quantity.

Application validation is still valuable for helpful error messages, but it is not a concurrency guarantee. Two application instances can both validate that an email is unused and then race to insert it. A database unique constraint is the final authority; handle the conflict and return a useful domain response.

Normalize, then denormalize deliberately

Normalization reduces duplicated facts and update anomalies. If a product name is copied into every order row, changing the canonical product name can leave inconsistent copies. Store each fact in an intentional home and connect rows with keys. Some duplication is useful for historical snapshots or read performance, but it should have an explicit update policy.

Query with set operations

SQL works primarily with sets of rows. A join combines related sets according to a predicate; filtering, grouping, and projection narrow the result. Be explicit about join conditions and selected columns. Avoid relying on row order unless the query includes ORDER BY, and avoid SELECT * in long-lived application contracts where schema changes can silently alter payload size.

Parameterize values instead of concatenating user input into SQL. Parameters protect query structure from injection and usually allow prepared statement reuse. Table names and sort directions are identifiers rather than values, so select them from a strict allow-list when they must vary dynamically.

Transactions and isolation

A transaction groups related changes into one commit boundary. Atomicity ensures all its writes commit together or roll back together, but isolation controls what concurrent transactions can observe. The default isolation level differs by engine, and stronger isolation may require retries after serialization conflicts. Keep transactions short and avoid wrapping slow remote calls inside them.

Across engines

PostgreSQL, MySQL, and Oracle all implement relational SQL, but differ in types, locking, indexing, DDL behavior, syntax, and optimizer details. Learn portable relational concepts first, then study engine-specific behavior before assuming identical-looking queries have identical plans or concurrency semantics. This course follows that progression: modeling and SQL first, then PostgreSQL, MySQL, and Oracle practice.