SQL
Writing Portable SQL
How much portability is worth paying for, which constructs are safe everywhere, and how to isolate the parts that can never be portable.
#Database Design
Designing schemas, entities and relationships.
11 notes tagged
SQL
Hierarchical Data and Recursive Queries
Storing and querying trees: the adjacency list, walking it with a recursive CTE, and the materialised path and closure table alternatives.
SQL
Anomalies and Denormalisation
The three anomalies normalisation prevents, and the disciplined way to reintroduce redundancy when reads genuinely demand it.
SQL
BCNF and Functional Dependencies
The formal notation behind normalisation, and the stricter normal form that catches what 3NF allows through when candidate keys overlap.
SQL
First, Second and Third Normal Form
Normalisation worked through on one table: remove repeating groups, then partial dependencies, then transitive ones - with the SQL at each step.
SQL
Data Integrity, Redundancy and Consistency
The three kinds of integrity a relational database enforces, why redundancy causes anomalies, and where a derived value is worth its maintenance cost.
SQL
Schema Design: Entities, Attributes and Relationships
How to get from a description of a business to a set of tables: find the entities, give them attributes, connect them, and name everything consistentl...
SQL
Many to Many Relationships and Junction Tables
Two tables that both have many of each other need a third table. Learn the junction table pattern, its key design, and how to query and count across i...
SQL
One to One and One to Many Relationships
The two simplest relationships and the single rule that decides where the foreign key goes - plus when a one to one split is actually worth it.
SQL
Natural vs Surrogate Keys
Should the primary key come from the data or be generated? The trade offs, the failure modes of each, and the pattern most teams settle on.
SQL
Candidate, Primary, Alternate and Composite Keys
The vocabulary of keys, explained on one table: which columns could identify a row, which one you choose, and what happens to the rest.