Loading lesson...
How tables talk to each other
How tables talk to each other
Topics covered: What Are Relationships?, Cardinality Explained, Required vs Optional, Self-Referential Tables, Complex Patterns
Why Cardinality Matters More Than You Think When you look at a schema, the first thing to understand is not the column names. It is the cardinality of the relationships between tables. Cardinality tells you whether a join will produce the same number of rows, more rows, or fewer rows than the input. Getting this wrong is the single most common source of incorrect query results in production systems. Relationships are implemented using foreign keys, but the foreign key is just the mechanism. The
Cardinality answers the question: for one row in table A, how many rows in table B can it relate to? Always read cardinality from both directions. The relationship between customers and orders is 1:N from the customer side, but 1:1 from the order side (each order belongs to exactly one customer). One-to-One (1:1) Each row in A maps to exactly one row in B. In practice, this usually means the two tables could be merged into one, but are kept separate for performance, security, or organizational r
Optionality: What Happens When the FK Is NULL? A required relationship means the FK cannot be NULL: every order MUST have a customer. An optional relationship means the FK can be NULL: an employee might not have a department yet. This distinction directly affects your queries. An INNER JOIN drops rows with NULL FKs. A LEFT JOIN preserves them but produces NULLs in the joined columns. In analytical models, the standard practice for optional FKs is to create an 'Unknown' member in the dimension ta
Modeling Hierarchies Within a Single Table Some relationships are between rows in the same table. An employee reports to a manager who is also an employee. A category has a parent category. A comment is a reply to another comment. These are self-referential relationships, and they model hierarchies. The most common approach is the adjacency list: a parent_id column that references the same table's primary key. It is simple to implement but hard to query beyond one level. Getting all descendants
The Many-to-Many Fan-Out Problem When you join through a many-to-many relationship, your row count explodes. If a product belongs to 3 categories and has 10 orders, joining products to categories to orders produces 30 rows instead of 10. This is the fan-out trap, and it silently inflates every SUM, COUNT, and AVG downstream. The fix is to pre-aggregate before joining, or to use a bridge table with weighting factors that sum to 1.0 per entity. This is covered in depth in the Bridge Tables lesson.