Attributes and Domains
An attribute is a property plus a set of legal values. Most design defects that surface much later as data-quality problems are born here, in a domain that was left too wide, too narrow or undefined.
Beginner
Learning objectives
- Define an attribute as a property with an associated domain
- Distinguish the conceptual domain from the SQL data type that will implement it
- Recognise domains that are too wide, too narrow or inconsistent across the schema
- Express optionality and allowed values without committing to implementation
- Name attributes so that their domain is obvious from the name
Prerequisites
- Module 2: Requirements Analysis
Introduction
Attributes look like the easy part. An entity is a rectangle, a relationship is a diamond, and an attribute is a name on an ellipse. What could go wrong?
What goes wrong is the part you did not write on the ellipse: the domain. An attribute is not just a name; it is a property together with the set of values that property may take. birth_date is not "a date-shaped string", it is "a calendar date, not in the future, after the person's plausible birth year, and mandatory for members but optional for guests". Every one of those clauses is a design decision, and each one is cheap now and expensive later.
This is also where the course's insistence on separating conceptual from physical pays off. At the conceptual level the domain is a set of values with constraints. At the physical level it becomes a DATE NOT NULL CHECK (birth_date < CURRENT_DATE). Those are two statements of the same rule at two levels of abstraction, and confusing them is how schemas end up with VARCHAR(255) for a field that can only ever hold one of four values.
What an attribute is
Attribute
An attribute is a property of an entity or of a relationship that, for each instance, takes exactly one value from a domain. The domain is the set of legal values for that property, together with any constraints on them.
Three parts of that definition deserve attention.
"Of an entity or of a relationship." Attributes belong to something. An attribute of Loan describes a loan; an attribute of Enrols describes the connection between a student and a course. There is no such thing as a free-standing attribute.
"Exactly one value." This is the clause that separates an attribute from a multivalued attribute, and it is the reason phone1, phone2, phone3 is a defect rather than a solution: the attribute phone takes three values for one instance, so it is not a simple attribute at all. Two lessons from now this will be formalised; here it is enough to notice that the definition is doing work.
"From a domain." Not "any value that fits in the column". The domain is a commitment, and it is what makes the attribute verifiable.
The domain is not a data type
At the conceptual level, a domain is characterised by what values are legal, not by how they are stored.
| Attribute | Conceptual domain | Possible physical types | What the physical type does not capture |
|---|---|---|---|
member.surname |
Non-empty string of letters, spaces, apostrophes and hyphens; up to ~40 characters | VARCHAR(60) |
the "letters only" rule, the non-empty rule |
loan.due_date |
A calendar date, never before the loan date | DATE |
the "never before" rule |
copy.condition |
One of a closed set: new, good, worn, damaged |
ENUM or VARCHAR(10) + CHECK |
— (here the type can carry it, if chosen well) |
book.isbn |
10 or 13 characters, digits and possibly X, with a check digit |
CHAR(13) |
the check digit |
member.email |
A syntactically valid address, optional | VARCHAR(254) |
validity |
Read the last column: in most rows the physical type captures less than the domain. That is the normal situation, and it is why the domain must be written down at conceptual level. If the only artefact of your design is a set of CREATE TABLE statements, the rules in that column are lost, and the application re-implements them — differently in each screen.
Domains that are too wide, too narrow, or inconsistent
Three failure modes, each with a recognisable symptom.
Too wide. The domain admits values the organisation would never accept. rating VARCHAR(10) admits "excellent", "3.5" and the empty string. The cost is not theoretical: reports must handle values that should not exist, and every consumer of the data must re-implement the validation. Symptom: the requirements say "a rating from 1 to 5" and the schema says VARCHAR(10).
Too narrow. The domain excludes values the organisation will certainly need. surname VARCHAR(20) is a design decision that will reject real members. postcode CHAR(5) works in Italy and fails in the Netherlands. Symptom: someone in the acceptance meeting says "what about…?" and the answer is "we'll deal with it later". The asymmetry matters: a domain that is too wide costs discipline, a domain that is too narrow costs a migration.
Inconsistent across the schema. The same concept has different domains in different places. member.city VARCHAR(30) and branch.city VARCHAR(50); loan_date DATE and return_date VARCHAR(10). The cost appears the moment the two must be compared or joined. Symptom: the same word, two different definitions, in the same schema.
Atomicity, before implementation
An attribute is atomic at the conceptual level when it is not composed of parts that the organisation treats separately. Note the clause: the organisation treats separately, not "could be split".
birth_date is atomic: nobody ever asks for "the year part of the birth date" as a stored fact — they compute it. full_name is not atomic in a library that sorts members by surname and greets them by first name, because the organisation uses the two parts separately. address is atomic in a schema where addresses are only ever printed, and not atomic in a schema where deliveries are routed by postcode.
This matters because atomicity decides what can be constrained and what can be indexed. An attribute that hides two facts under one name cannot have a domain for either of them, and cannot be searched efficiently by either.
Optionality and allowed values
Two facets of the domain appear in almost every requirement, and both are expressible conceptually without any SQL.
Optionality. Is the attribute mandatory for every instance, or may it be absent? member.email is often optional; member.surname is not; loan.return_date is absent precisely while the loan is open — and that absence is meaningful, not an error. At conceptual level this is a property of the attribute: mandatory or optional. In Chen notation it is often written as a note on the attribute; in ERMap it is a flag on the attribute.
Note the trap: an optional attribute whose absence carries meaning is doing the work of a state. return_date being absent is how the model says "not yet returned". That is legitimate, but it must be recognised, because it means the attribute is part of a constraint that spans two states of the same instance.
Allowed values. When the domain is a closed set, list it. condition ∈ {new, good, worn, damaged} is a complete, checkable statement. When the domain is an open set with rules, state the rules: "a fiscal code is 16 characters, alphanumeric, and the last two are the code of the municipality of birth". When the domain is genuinely open — notes, description — say so explicitly, because "open" and "forgotten" look identical in a schema.
Defaults. A default is not a domain constraint; it is a decision about what to do when the value is not supplied. loan.due_date defaulting to "loan date + 30 days" is a business rule that belongs in the requirements register. Writing it as a default at conceptual level is acceptable if it is recorded as a rule, but it must never be used to hide optionality: a default value on a mandatory attribute is not a way of making it mandatory.
Naming attributes
Attribute names are read far more often than they are written, and a good name removes the need to look up the domain.
State the property, not the format. birth_date, not date1; isbn, not code.
No type suffixes. name_string, amount_num, date_dt encode the implementation in the name, and they become wrong when the type changes.
No entity prefix inside an entity. Inside Member, the attribute is surname, not member_surname. The prefix is only useful where the name would otherwise be ambiguous — and if it is ambiguous, the name is the problem.
Consistent vocabulary across the schema. If you call it date_of_birth in one entity and birth_date in another, you have created two concepts where the organisation has one. Pick one and apply it everywhere; the same applies to surname/last_name, postcode/zip/cap.
Units and currencies in the name when there is any doubt. price is ambiguous; price_eur is not. This is one of the few places where a suffix earns its place, because it prevents a whole class of expensive errors.
A worked example
Take one requirement and derive the attribute table for it.
R9. Each book copy has a barcode, a condition (new, good, worn or damaged), the date it was acquired, and optional notes. A copy is either available or on loan, but that is derived from the loans, not stored.
| Attribute | Domain | Optional? | Notes |
|---|---|---|---|
barcode |
13 digits, unique in the library | no | this is the identifier, not a plain attribute |
condition |
one of new, good, worn, damaged |
no | closed set; default good on acquisition |
acquisition_date |
calendar date, not in the future | no | |
notes |
free text, up to a few hundred characters | yes | deliberately open — say so |
| (availability) | — | — | not an attribute: derived from Loan, see the lesson on derived attributes |
Three things to notice.
The identifier is an attribute with a domain, and its domain is tighter than the rest: unique. That constraint is what makes it an identifier, and it is covered in its own lesson.
condition is a closed set, so the design can state the four values. If the library later adds "for restoration", the domain changes — a one-line change now, a data migration later, which is why closed sets are worth stating even when they might grow.
availability is explicitly excluded. It is derivable from the loans, so storing it would duplicate a fact and violate minimality. The requirement itself says so ("derived from the loans, not stored"), which is the mark of a well-written requirement: it closes the door on the wrong design.
Common mistakes
| Mistake | Symptom | Correct approach |
|---|---|---|
| No domain at all | Every attribute is "text" | Write the domain sentence for each attribute |
| Domain taken from the spreadsheet | VARCHAR(255) everywhere because Excel had no types |
Derive the domain from the requirements |
| Domain too narrow | surname VARCHAR(20), postcode CHAR(5) |
Ask for the longest real value; prefer generous |
| Same concept, different domains | city VARCHAR(30) and city VARCHAR(50) |
One domain per concept, applied everywhere |
| Non-atomic attributes the organisation splits | full_name while the library sorts by surname |
Split it; each part gets its own domain |
| Over-splitting | street, number, stair, floor, door when addresses are only printed |
Keep atomicity relative to use |
| Hiding optionality in defaults | due_date defaulted so that "not set" looks set |
State optionality explicitly; defaults are rules |
| Type suffixes in names | amount_num, name_str |
Name the property, not the type |
Summary
- An attribute is a property plus a domain; the domain is the part that makes it checkable, and it is the part most often omitted.
- The conceptual domain is not a SQL data type: the type usually captures less, so the rules must be written at conceptual level or they are lost.
- Domains fail by being too wide (admits impossible values), too narrow (rejects real ones) or inconsistent (same concept, different domains).
- Atomicity is relative to the requirements: an attribute is atomic when the organisation does not treat its parts separately.
- Optionality and allowed values are domain facets expressible without implementation; a default is a business rule, never a way of hiding optionality.
- Names state the property, carry no type suffixes, and use one vocabulary consistently across the schema.
Verification questions
Check your understanding
- Why is "the domain of
surnameisVARCHAR(40)" not a statement about the domain? - Give an attribute whose domain is too wide and one whose domain is too narrow, and state the cost of each.
- Is
addressatomic? Justify your answer in terms of the requirements rather than of the string. - Why is
phone1,phone2,phone3evidence that the attribute is not simple, rather than a solution to a multivalued property? - A requirement says "the due date is the loan date plus 30 days". Is that a domain constraint, a default, or a rule? What follows for the design?
Show the answers
- Because
VARCHAR(40)constrains only length and storage. The domain also includes "non-empty", "letters, spaces, apostrophes and hyphens only", and possibly a minimum length. A column of typeVARCHAR(40)happily stores"12345"and"", both of which are outside the domain. The type is an implementation of part of the domain, never the whole of it. - Too wide:
rating VARCHAR(10)in a system where ratings are 1 to 5 — cost: impossible values enter the database and every report must defend against them. Too narrow:postcode CHAR(5)— cost: real values are rejected, and widening a column after data exists is a migration plus an application release. The asymmetry is that the wide domain costs discipline while the narrow one costs a change. - It depends on the requirements, and that is the correct answer. If addresses are only ever printed on a label,
addressis atomic: the organisation never uses a part on its own. If deliveries are routed by postcode, or members are grouped by city, the organisation does treat the parts separately, soaddressis not atomic and must be decomposed into at least street, postcode and city. - Because the definition of a simple attribute is "exactly one value per instance".
phone1..3does not satisfy that: one member has three values of the same property, and the schema encodes the count in the shape of the entity. Adding a fourth number requires a schema change, and querying "all numbers of this member" requires a three-wayUNION. The correct treatment is the multivalued attribute (next lessons), not three columns. - It is a rule — a derivation, not a constraint on a single value and not a default. It means
due_dateis derivable fromloan_date, which makes it a candidate for a derived attribute, and it must be checked against the requirements: if the library can extend a loan, the derivation no longer holds anddue_datebecomes an independent attribute. This is exactly the kind of question the next lessons formalise.
Exercise
Domain table for a gym
A gym's requirements register contains the following. Produce a domain table — attribute, domain, optional?, notes — for the entity Member and the entity Subscription. Then mark the three attributes whose domain is most at risk of being left undefined, and say what question you would ask.
R4. Each member has a membership number, a first name, a last name, a date of birth and a tax code. The e-mail address is optional; the mobile number is required for members under 18. R5. A subscription has a start date, an end date and a type (monthly, quarterly, annual). The price depends on the type and on any discount applied. R6. Discounts are one of: none, student, senior, family. R7. The gym must be able to print a member's full name and the remaining days of the subscription.
Show the commented solution
Domain table:
| Attribute | Entity | Domain | Optional? | Notes |
|---|---|---|---|---|
membership_number |
Member | sequence of digits, unique in the gym | no | the identifier; see the identifiers lesson |
first_name |
Member | non-empty, letters/spaces/apostrophes/hyphens, up to ~40 | no | |
last_name |
Member | same domain as first_name |
no | one vocabulary, two attributes |
birth_date |
Member | calendar date, not in the future, not more than ~110 years ago | no | the upper bound matters for data quality |
tax_code |
Member | 16 alphanumeric characters, format checkable | no | |
email |
Member | syntactically valid address | yes | |
mobile |
Member | digits, international prefix optional | conditionally | required only for under-18s — see below |
start_date |
Subscription | calendar date | no | |
end_date |
Subscription | calendar date, after start_date |
no | an inter-attribute constraint |
type |
Subscription | one of monthly, quarterly, annual |
no | closed set |
discount |
Subscription | one of none, student, senior, family |
no | closed set; default none |
price |
Subscription | non-negative amount, currency fixed | no | name it price_eur if any doubt exists |
The three attributes most at risk of being left undefined:
-
tax_code. The tempting design isVARCHAR(20)"because it is a code". The domain is precise — 16 alphanumeric characters, with a check character — and stating it costs one line. Question to ask: "Is the tax code always present, and is it validated at registration or merely recorded?" The answer decides whether the check belongs to the domain or to a later validation step. -
mobile. This is the interesting one, because the requirement makes it conditionally mandatory: required for members under 18, optional otherwise. Conditional mandatory is not expressible as a simple optionality flag — it is a constraint that spans two attributes of the same entity. Question to ask: "What should happen for an adult member with no mobile number — is the record acceptable?" If yes, the design needsmobileoptional plus a rule; if no, the requirement is really "mobile is mandatory", and the under-18 clause is a red herring. -
price. Amounts have three hidden domain decisions: currency, precision, and whether negative values are legal. Question to ask: "Are prices always in euro, and can a discount ever produce a negative amount?" The second half is not pedantry: a 100% family discount plus a promotion could, and the domain must decide.
Two further observations the exercise is designed to provoke.
R7 mentions "the remaining days of the subscription" and "full name". Neither is an attribute. Full name is a derived attribute (computed from first and last name); remaining days is derived from end_date and today's date. Storing either would duplicate a fact. This is the subject of the lesson after next.
end_date after start_date is an inter-attribute constraint, not a domain constraint on either attribute alone. It is perfectly expressible conceptually and must be recorded, because no amount of per-column typing will enforce it.
Where to go next
- The previous lesson, on what carries attributes: Entities and Instances.
- The vocabulary of attributes as it appears in the requirements text: Identifying Attributes and Relationships.
- Why undefined domains become application bugs rather than database errors: Traditional File Archives and Their Limits.
- Domains in use in a complete schema you can open and edit: Student and Course.