Attributes, Domains, Optionality, and Derived Values

Learning outcomes

An entity type tells us what kind of thing exists. Attributes tell us which facts describe each instance. Good attribute design preserves meaning. Bad attribute design stores strings without defining what they mean, permits impossible values, confuses missing data with empty data, or duplicates values that should be derived.

An attribute is a fact with a domain

An attribute is a property used to describe an entity or relationship instance. For Asset, examples include manufacturer, serial number, model name, purchase date, and current condition. But an attribute is more than a label. It has a domain: the set of values considered meaningful and valid for that attribute.

For example, the domain of quantity_used is not “INTEGER.” That is an implementation type. The business domain may be “positive whole units from 1 through 10000.” If fractional quantities such as 0.25 liters are valid, the business domain is different.

Simple and composite attributes

A simple attribute is treated as one indivisible value for the model's purposes. A composite attribute has meaningful subparts.

Atomicity is contextual. A value can be logically atomic for one system and composite for another. The key is whether subcomponents have independent meaning or operations in this domain.

Single-valued versus multivalued attributes

If a customer can have several phone numbers, storing columns such as phone1, phone2, and phone3 embeds an arbitrary limit and makes searching awkward. A repeating concept often belongs in a separate child entity or relationship.

Customer  customer_id  full_nameCustomerContact  contact_id  customer_id  -> Customer  type         -> 'mobile' | 'office' | 'email'  value  is_primary  verified_at

CustomerContact is justified not merely because there can be many contacts, but because each contact may have type, priority, verification state, and lifecycle.

Optionality is a business decision

An optional attribute may be absent for a valid entity instance. In relational schemas, optionality is often implemented through nullable columns, but the modeling question comes first: Can a valid instance exist before this fact is known or applicable?

NULL means missing information, not every kind of absence

SQL NULL represents missing or unknown information, but business systems encounter several different meanings of absence:

• Unknown — the value exists but has not been learned.
• Not applicable — the concept does not apply.
• Not yet — the value will become known later.
• Withheld — the value is intentionally unavailable.
• Empty — an actual value is present and happens to be an empty collection/string.

A nullable column sometimes collapses these meanings. If the distinction matters, model it explicitly with status attributes or separate lifecycle structures.

closed_at = NULLstatus    = 'open'-- The pair communicates "not yet closed",-- not merely "we lost the closing timestamp".

Defaults do not replace missing facts

A default should represent a legitimate business default, not hide uncertainty. Setting unknown country to 'US', unknown quantity to 0, or unknown status to 'active' creates false facts.

Good defaults are values the business explicitly defines as the initial state, such as a new work order beginning with status 'open'. Even then, the rule should be documented.

Stored versus derived attributes

A derived attribute can be calculated from other facts. Examples include age from birth date, work-order duration from timestamps, line total from quantity × unit price, and current assignment from assignment history.

Derived values may later be cached or materialized for performance, but then they become redundant data with synchronization responsibilities. Chapter 13 covers that tradeoff.

Attribute domains prevent semantic drift

Two columns both typed TEXT may have completely different domains. A status code, email address, postal code, serial number, and technician role are not interchangeable simply because the DBMS stores them as text.

Document domains with:

• meaning and unit;
• allowed values or format;
• case sensitivity and normalization rules;
• minimum/maximum values;
• whether the value is required;
• whether it may change after creation;
• who or what is authoritative for the value.

Example: improving WorkOrder attributes

A weak first draft might be:

WorkOrder(  id,  description,  status,  date,  price)

The names hide business meaning. A stronger model asks:

• Is date opened time, scheduled time, completed time, or invoiced time?
• Is price estimate, labor charge, parts charge, invoice total, or tax-inclusive total?
• What statuses exist and which transitions are valid?
• Can description change, and must the original customer statement be preserved?

Precise attributes emerge from precise questions.

SQL prototype with domains expressed as constraints

CREATE TABLE part_usage (    work_order_id       INTEGER NOT NULL,    part_id             INTEGER NOT NULL,    quantity            INTEGER NOT NULL CHECK (quantity > 0),    charged_unit_price  NUMERIC NOT NULL CHECK (charged_unit_price >= 0),    currency_code       TEXT NOT NULL CHECK (length(currency_code) = 3),    used_at             TEXT NOT NULL,    FOREIGN KEY (work_order_id) REFERENCES work_order(work_order_id),    FOREIGN KEY (part_id) REFERENCES part(part_id));

The SQL types are only part of the design. CHECK, nullability, and foreign keys narrow the stored values toward the business domains.

Practice: attribute audit

Summary and next lesson

Attributes represent facts, and domains define what those facts are allowed to mean. Optionality, nullability, multivalued properties, defaults, and derived values are modeling decisions—not syntax details. The next lesson focuses on identity: how to recognize candidate keys and how business identifiers differ from convenient labels.

References

• C. J. Date, An Introduction to Database Systems.
• Ramez Elmasri and Shamkant B. Navathe, Fundamentals of Database Systems.
• ISO/IEC 9075 SQL standard family.
• SQLite and PostgreSQL documentation for constraints and NULL semantics.