180: Relational Thinking, Tables, Rows, Columns, Domains, and Data Types

Learning outcomes

By the end of this lesson, you can:

explain and apply relations and tuples in a realistic implementation;
explain and apply domains and data types in a realistic implementation;
explain and apply keys in a realistic implementation;
explain and apply null in a realistic implementation;
explain and apply set-based thinking in a realistic implementation.

Prerequisites and retrieval

This lesson assumes the earlier 01–06 foundation and the preceding lessons in this module. Before you read, retrieve one concrete example from a previous project where one of these concerns showed up. Perhaps a field had an ambiguous type, a retry created a duplicate, or a query behaved differently from the way you expected. The aim is not to memorize vocabulary. It is to make a defensible decision in a PostgreSQL-backed transactional application, where schema design, correctness, query plans, and concurrency all affect one another.

Terminology

Relations and tuples: A table is the practical database representation of a relation. Its rows represent tuples, and its columns represent attributes.
Domains and data types: Choose types that model the domain: integers for counts and identities when appropriate, exact numeric types for money-like values that require exact decimals, timestamps with explicit timezone semantics, booleans for genuinely two-state facts, and text with meaningful validation.
Keys: Candidate keys uniquely identify rows according to business rules. A primary key is the identifier chosen for the table; alternate keys remain useful uniqueness constraints.
NULL: NULL means that information is unknown or missing. It does not mean zero and it does not mean an empty string.
Set-based thinking: SQL describes which rows satisfy a condition and lets the optimizer select a physical execution plan.
Dialect awareness: SQL is standardized, but production systems differ in types, syntax, indexes, JSON support, locking behavior, and DDL. A portable concept and a portable statement are not always the same thing.

Mental model

Treat Relational Thinking, Tables, Rows, Columns, Domains, and Data Types as a design problem with observable inputs, outputs, invariants, and failure modes. In the relational model, data is represented as relations with explicit domains and constraints. A query describes the result you want rather than prescribing every iteration the engine must perform. That separation is what allows the database to choose among physical plans while preserving the logical result.

A strong implementation makes assumptions visible, reduces uncertainty at the boundaries, and leaves evidence behind: tests, types, constraints, metrics, or diagrams that show why the design is safe. The useful sequence in both an interview and a production change is:

requirement -> constraints -> model -> implementation -> failure analysis -> verification

Do not jump from a requirement straight to a library call or an ORM declaration. First state what must remain true. Then choose the database and application mechanisms that enforce those truths. That habit makes it much easier to tell whether a failure belongs to parsing, domain logic, persistence, or presentation.

Deep dive

1. Relations and tuples

When an application needs to represent customers and their orders, it needs more than a convenient list of objects. It needs a structure whose rows can be related, constrained, filtered, and combined. A table is a practical database representation of a relation; rows represent tuples and columns represent attributes. Query results are relations conceptually, even though the engine exposes a meaningful order only after an explicit ORDER BY.

For now, keep the model simple: a tuple is one row-shaped value, an attribute is one named component of that tuple, and a relation is a set of tuples sharing the same attributes. The database's physical storage is an implementation detail, not a reason to treat a table as an ordered array.

Decision rule: Use relations and tuples deliberately when they make the contract or invariant easier to prove. If a relational abstraction only reduces typing while hiding an assumption, prefer the more explicit design. This matters when a developer assumes that insertion order is query order, or when an object-shaped API hides the fact that a relationship needs a foreign key and a constraint.

2. Domains and data types

A column type is part of the contract, not merely a storage preference. If a value represents a count, an integer may be appropriate. If it represents an exact monetary amount, an exact numeric type is usually a better fit than a floating-point type. A timestamp needs clear timezone semantics, a boolean should represent a genuine two-state fact, and text should have validation that reflects the domain rather than accepting every possible string.

The domain is the meaning of the value; the data type is the database mechanism that describes some of the values it can hold. A type alone cannot express every business rule, so pair it with NOT NULL, a CHECK, a foreign key, or application validation when the invariant requires it. This distinction prevents a broad type from becoming a silent substitute for a well-defined contract.

Decision rule: Use domains and data types deliberately when they make the contract or invariant easier to prove. If a type only reduces typing while hiding an assumption, prefer the more explicit design. Ask what values are valid, what precision is required, and what should happen when the value is absent or malformed.

3. Keys

The application may have a generated ID, but that does not automatically identify the business object uniquely. A candidate key is a set of attributes that uniquely identifies a row according to the business rules. The primary key is the candidate key selected as the table's main identifier. Other candidate keys do not become irrelevant: they remain alternate keys and often need UNIQUE constraints.

Surrogate keys can make joins and references convenient, but they do not remove the need to protect business uniqueness. For example, if an account can have only one active membership for a product, a generated membership ID does not stop two concurrent requests from creating two memberships. The relevant business uniqueness must be represented and enforced at the owning persistence boundary.

Decision rule: Use keys deliberately when they make the contract or invariant easier to prove. If a generated key only reduces typing while hiding an assumption, prefer the more explicit design. Identify both the technical identifier and the business keys, then consider retries, concurrent inserts, and the behavior of uniqueness constraints when values are NULL.

4. NULL

The confusing case is not an empty field on a form; it is the difference between “the value is empty” and “the value is not known or does not exist.” In SQL, NULL represents unknown or missing information, not zero or an empty string. It participates in three-valued logic, so comparisons, uniqueness checks, aggregates, and joins need deliberate reasoning.

For example, price = NULL is not the test for a missing price. Use IS NULL or IS NOT NULL. Likewise, a count generally ignores NULL values for the expression being counted, while COUNT(*) counts rows. Those differences become visible in reports and left joins, where a related row may be absent rather than present with an empty value.

Decision rule: Use null deliberately when it makes the contract or invariant easier to prove. If it only reduces typing while hiding an assumption, prefer the more explicit design. Document what NULL means for each nullable column: unknown, not yet collected, not applicable, or another precise state. If those states have different behavior, model them separately instead of making one NULL carry several meanings.

5. Set-based thinking

The application developer often starts with a loop: load rows, inspect each one, and issue another statement for every row. SQL offers a different model. SQL describes what rows satisfy a condition and lets the optimizer choose a physical plan. When an operation can be expressed declaratively, set operations usually make the intended work, transaction boundary, and indexing opportunities clearer than a row-by-row procedural loop.

Set-based thinking does not mean that every query is automatically fast or that loops are never appropriate. It means starting with the relation as a whole, then inspecting the actual plan and resource behavior. A single query can still scan too much data, sort expensively, or create contention. The answer is measurement and an appropriate index or query shape, not an assumption that one statement is always optimal.

Decision rule: Use set-based thinking deliberately when it makes the contract or invariant easier to prove. If it only reduces typing while hiding an assumption, prefer the more explicit design. Check the transaction and concurrency semantics as well as the syntax: a loop and a set-based update may observe and lock data differently.

6. Dialect awareness

SQL knowledge transfers well at the relational-concept level, but the statement you write still runs in a particular database. Real systems have dialect differences in types, syntax, indexes, JSON, locking, and DDL. This course uses PostgreSQL for concrete examples while teaching portable relational concepts.

There is one subtle detail worth keeping in view: “standard SQL” does not guarantee identical behavior or identical operational tooling across database products. When portability matters, verify the feature in the target database. When PostgreSQL-specific behavior is useful, name it clearly and consult the PostgreSQL documentation rather than relying on a memory of another dialect.

Decision rule: Use dialect awareness deliberately when it makes the contract or invariant easier to prove. If a dialect-specific feature only reduces typing while hiding an assumption, prefer the more explicit design. Be especially careful around types, index behavior, JSON operators, locking clauses, and schema changes.

Worked example

Consider a PostgreSQL-backed transactional application where schema design, correctness, query plans, and concurrency all matter. Start by writing the requirement in one sentence. Then list the input and output contracts and identify which concept owns each possible failure mode. The key move is separation: parsing and boundary validation belong at the boundary; domain rules belong in the domain or service layer; persistence rules belong in the database or repository; presentation rules belong in the client. Combining all of them in one place can make a happy-path demo look shorter, but it makes edge cases much harder to reason about.

Suppose the requirement is: “Return every active customer with the number of orders associated with that customer, including active customers who have no orders.” One relational expression of that requirement is:

SELECT c.id, c.name, COUNT(o.id) AS order_count
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.id
WHERE c.status = 'active'
GROUP BY c.id, c.name
ORDER BY order_count DESC;

The LEFT JOIN is doing important work. It keeps an active customer even when no matching order exists, and COUNT(o.id) produces zero for that customer because the joined order ID is NULL. The WHERE clause filters customers before the grouped result is returned, while the GROUP BY makes the selected non-aggregate customer attributes explicit. The final order is not implied by the table or index; it is requested by ORDER BY.

Walk the example through at least four cases: the normal path, an empty or missing value, a duplicate, retry, or concurrent path where relevant, and a dependency failure. For each case, state which layer detects the problem and what the caller observes. For instance, a missing customer name might be a schema contract issue, a duplicate order might require a business key and a database constraint, and a database connection failure should not be disguised as “zero orders.” This is the level of explanation expected in a senior code review or technical interview.

Production perspective

Production correctness is broader than “the code works on my machine.” Ask how the design behaves during deploys, retries, partial failure, stale clients, concurrent requests, malformed data, schema changes, and high cardinality. Prefer explicit contracts, bounded resource usage, structured errors, and measurable behavior. Optimize only after you can identify the bottleneck or risk with evidence, such as an execution plan, query timing, lock observations, or resource metrics.

When the topic involves an external dependency, define a timeout and cancellation strategy. When it involves persistence, define transaction and consistency expectations. When it involves user-visible state, define loading, empty, error, stale, and success states. When it involves security, assume the client can be modified and all network input is untrusted. Database constraints are not a replacement for authorization, and a client-side validation message is not a persistence guarantee.

Guided lab

Design a small schema for customers, products, orders, and order items. Justify every data type, identify candidate and business keys, and write down where NULL is allowed and what it means. Include the relationships and the constraints that protect them; do not stop at a set of column names.

Complete the lab with this discipline:

Write the requirement and two non-requirements.
List input, output, and error contracts before implementation.
Implement the smallest correct vertical slice.
Add at least one invalid-input test and one edge-case test.
Instrument or inspect the behavior instead of guessing.
Refactor one hidden assumption into an explicit type, constraint, function, or configuration.
Explain one alternative design and why you did not choose it.
Record a short “what would break at 10× scale?” note.

As you work, inspect the generated schema and the relevant query plan. Check the cases where an order has no items, where an item refers to a product that is no longer available, where a retry repeats an insert, and where an optional value is genuinely unknown. The point of the lab is not to produce the largest schema; it is to make each invariant visible and testable.

Edge cases and failure modes

Relations and tuples: test absence, malformed input, duplicates, ordering and concurrency where applicable, and behavior at the smallest and largest credible sizes.
Domains and data types: test absence, malformed input, duplicates, ordering and concurrency where applicable, and behavior at the smallest and largest credible sizes.
Keys: test absence, malformed input, duplicates, ordering and concurrency where applicable, and behavior at the smallest and largest credible sizes.
NULL: test absence, malformed input, duplicates, ordering and concurrency where applicable, and behavior at the smallest and largest credible sizes.
Set-based thinking: test absence, malformed input, duplicates, ordering and concurrency where applicable, and behavior at the smallest and largest credible sizes.

For each test, record the expected database result and the expected application result separately. A query can return an empty relation legitimately, while a failed dependency or a violated constraint should normally become a structured error rather than being silently converted into an empty result.

Common mistakes and debugging

Solving the example instead of the requirement: a copied pattern can be syntactically correct but architecturally wrong.
Hiding uncertainty with assertions, broad exception handlers, permissive schemas, or “temporary” any values.
Testing only the happy path and therefore discovering contracts only after integration.
Optimizing before measuring, or selecting a scalable mechanism without a scale requirement.
Letting client-side behavior stand in for server-side authorization, validation, or persistence guarantees.

For debugging, reproduce the smallest failing case, inspect the actual value or execution plan, trace the boundary where the invariant first becomes false, and fix the owning layer instead of adding a downstream patch. For a relational problem, inspect the schema and constraints first, then the generated SQL and bound values, then the transaction and lock behavior, and finally the query plan and runtime metrics. Check whether the observed result is a legitimate empty set, a NULL caused by a missing relationship, a constraint failure, or a dependency failure. Those cases may look similar at the UI but require different fixes.

Interview questions

What problem does Relations and tuples solve, and what trade-off or failure mode would make you choose a different approach?
What problem does Domains and data types solve, and what trade-off or failure mode would make you choose a different approach?
What problem does Keys solve, and what trade-off or failure mode would make you choose a different approach?
What problem does NULL solve, and what trade-off or failure mode would make you choose a different approach?
What problem does Set-based thinking solve, and what trade-off or failure mode would make you choose a different approach?

Answer each question with one concrete schema or query decision, one invariant, and one failure case. A definition without the consequence is not enough: the interviewer is testing whether you can recognize the concept in a real system and choose where to enforce it.

Checkpoint

Without notes, explain Relational Thinking, Tables, Rows, Columns, Domains, and Data Types to another developer in five minutes. Your explanation must include one invariant, one edge case, one production failure mode, and one alternative design. Then implement a small example without copying the lesson code.

Mastery checklist

I can define the core terms precisely.
I can choose a design from requirements instead of from habit.
I can implement and test the normal path and edge cases.
I can explain the runtime, storage, or complexity cost.
I can identify which layer owns validation, errors, and recovery.
I can compare at least two reasonable alternatives.
I can explain how the design changes at larger scale or stricter reliability.