What is an attribute's domain in the relational model, and what do you gain by giving an attribute a narrow domain instead of storing everything as free text?
answer
- domain = permitted values + operations
- same domain = comparable
- order_id vs customer_id: both INTEGER, different domains
- text sorting: '100' < '9'
- NULL belongs to no domain
basics
~20 sA domain is the set of values an attribute may take, plus the operations defined on them. A narrow domain rejects impossible values at write time, gives correct comparison and ordering semantics, and lets the engine store and compare values efficiently instead of every reader re-parsing text.
solid answer
~60 sAn **attribute** is a name plus a **domain**. The domain is the named set of values the attribute may take together with the operations that make sense on them - a date domain supports date arithmetic and chronological ordering, a text domain does not. Declaring a narrow domain buys four things. **Integrity**: values outside the domain are rejected at write time, so no reader has to defend against them. **Semantics**: comparison, ordering and arithmetic behave correctly, whereas text sorts '100' before '9' and a numeric string does not know it is a number. **Efficiency**: native representations are smaller, compare faster, and give the optimiser meaningful statistics. **Documentation**: the schema itself states what the value is. Strict domain thinking goes further than SQL types. Under Date and Darwen's reading, `order_id` and `customer_id` should be *different* domains even though both are integers, so comparing them is a type error. SQL will happily compare them, which is why domain discipline partly ends up in constraints, naming and review rather than in the type system.
code
sql · 9 linesCREATE TABLE orders (
order_id INTEGER NOT NULL,
customer_id INTEGER NOT NULL,
placed_at DATE NOT NULL,
quantity INTEGER NOT NULL CHECK (quantity > 0)
);
-- Type-legal, semantically nonsense: the model would call this a domain error
SELECT * FROM orders WHERE order_id = customer_id;go deeper
Say that the domain is the set of values an attribute is allowed to hold and give one concrete failure of storing dates or numbers as text.
Cover integrity, comparison semantics, efficiency and self-documentation, and note that NULL is outside every domain.
Add the comparability angle - domains say what may be compared, and SQL is looser than the model - plus the cost of over-tight domains in migration terms.
Frame domain choice as where you place invariants: enforced in the schema they are guaranteed once for all consumers; left open they are re-implemented, inconsistently, by every reader.
## Definition In the relational model each attribute is a pair: a **name** and a **domain**. A domain is a named set of legal values together with the operations defined over them - in modern terms, a type. `DATE` is the set of calendar dates and supports ordering and date arithmetic; `BOOLEAN` is the two-element set with logical operations; a user-defined `EMAIL` domain might be the set of strings satisfying a format rule. Every tuple in a relation's body must map each attribute to a value drawn from that attribute's domain. This is the model's most basic integrity rule, and it is enforced by the type system rather than by application code: a value outside the domain simply cannot appear. ## Domains are about comparability, not just storage The subtler purpose of domains is to say which values may be *compared*. Two attributes drawn from the same domain can meaningfully be equated, joined, or ordered against one another. Two attributes from different domains cannot, even if their physical representation is identical. This is where SQL is weaker than the model. In SQL, `order_id INTEGER` and `customer_id INTEGER` are the same type, so a predicate equating them is accepted without complaint - and produces silent nonsense. Under strict domain discipline they would be distinct domains and the comparison would be rejected outright. Engines offer partial recoveries (distinct user-defined types, wrapper types in the application layer), but in practice the discipline is maintained by naming conventions, referential constraints and review. Being able to articulate this gap is what separates a middle answer from a junior one. ## What a narrow domain actually buys you **Integrity at the boundary.** If a quantity is declared as a non-negative integer, no code path anywhere can store 'N/A' or -3. Every reader is spared a defensive branch. Compare the alternative: one text column, and now every consumer, in every language, re-implements parsing and re-decides what to do with junk. The bugs that come from that divergence are the classic argument for typed attributes. **Correct semantics for free.** Ordering, ranges and arithmetic follow the domain. Dates compare chronologically; text compares lexicographically, so '2026-1-9' sorts after '2026-10-01' as text and before it as a date. Numbers stored as text sort '100' before '9'. Money in binary floating point loses cents in ways an exact decimal domain does not. Choosing a domain is choosing a comparison semantics, and getting it wrong produces wrong results, not merely slow ones. **Efficiency and better plans.** Native representations are compact and compare with a machine instruction rather than a parse. Statistics gathered over a well-typed attribute - ranges, distinct counts, histograms - are meaningful, so the optimiser's estimates improve. A wide text attribute that secretly holds five different kinds of value defeats all of this. **Self-description.** The schema becomes documentation that cannot drift from reality, because the engine enforces it. ## The costs, so your answer is balanced Narrow domains are a commitment. Widening one later is a schema change over a possibly large existing body of data. Over-tight domains - an enumerated status set that needs a sixth value every month, a fixed-length code a partner later exceeds - turn every product change into a migration. The judgement call is to pick the narrowest domain that reflects a genuine invariant of the business and to leave genuinely open-ended values open. A useful test: if you can imagine a legitimate value being rejected next quarter, the domain is too tight. ## NULL, briefly NULL sits outside every domain: it is a marker meaning no value is present rather than a member of the value set. That is why permitting NULL weakens the guarantee a domain gives you - the attribute becomes 'a domain value, or nothing' - and why whether an attribute may be absent is a modelling decision rather than a formatting one.
- What concretely goes wrong if a date is stored in a text attribute?Comparison and ordering become lexicographic, so range filters and sorts return wrong rows whenever the textual form is not fixed-width and zero-padded. Invalid values such as '2026-02-31' or 'unknown' can be stored, so every reader must parse defensively. The engine also cannot use date-aware statistics, and expressions that convert the text on read tend to prevent effective use of any index on it.
- How would you keep two identifier attributes from being compared when both are integers?In pure relational terms you declare them over distinct domains so the comparison is a type error. In SQL you can approximate this with distinct user-defined types where the engine supports them; otherwise you rely on naming conventions, referential constraints pointing at the right relation, and review, and you push the distinction into the application's type system so nonsense comparisons fail before they reach the database.
saying these in an interview costs you the question
- Treating a domain as merely a storage size rather than a set of legal values plus operations
- Arguing everything should be text 'for flexibility' with no account of the integrity and comparison costs
- Believing SQL's type system already prevents comparing two unrelated integer identifiers
- Calling NULL a value in the attribute's domain
- Ignoring the migration cost of over-tightening a domain