Domains, Data Types, and Meaningful Value Spaces

Learning outcomes

A relational column has a DBMS data type, but a data model needs something richer: a domain. A domain describes the meaning and valid value space of an attribute. Two columns may both use TEXT while representing completely different domains such as email address, ISO country code, work-order status, serial number, or currency code.

A domain is more than a type

Suppose WorkshopHub stores these attributes:

status_code        TEXTcurrency_code      TEXTserial_number      TEXTproblem_description TEXT

Physically, all four may use text storage. Semantically, however, their domains are very different. currency_code may need exactly three uppercase letters from an approved reference set, while problem_description may be free-form human text.

Components of a useful domain definition

For each attribute, document:

• business meaning;
• valid values or range;
• unit of measure;
• precision and scale;
• format and normalization rules;
• case sensitivity;
• whether null is valid;
• whether the value may change;
• who or what is authoritative.

Numeric domains

An integer type does not automatically mean “any integer is valid.” For PartUsage:

quantity INTEGER NOT NULL CHECK (quantity > 0)

If fractional quantities are allowed, an integer is the wrong physical representation. If quantity can be negative only for reversal rows, the domain depends on transaction type and may require a richer rule.

Precision and scale are semantic

Money and measurements require deliberate precision. A price stored as floating-point binary may introduce rounding behavior unsuitable for accounting. A physical measurement may need more precision than a displayed value.

charged_unit_price NUMERIC(12,2)measurement_value  NUMERIC(18,6)

Do not choose precision simply because a framework generated it. Ask what maximum magnitude and smallest meaningful increment are valid.

Units belong to the domain

A value such as 25 is incomplete if it represents weight but the unit is unknown. Options include:

• store all values in one canonical unit, such as grams;
• store value + unit code;
• store original value/unit and a normalized value for computation.

The right choice depends on reporting, integration, and audit requirements.

Dates, times, and timestamps

Temporal values need semantics beyond “date.” Compare:

• opened_at — an instant;
• scheduled_date — perhaps a local calendar date;
• valid_from — beginning of an effective interval;
• birth_date — date without time-of-day;
• business_day — organization-specific calendar concept.

Choose date/time types that match the concept. A timestamp is not automatically better than a date.

Time zones are part of meaning

For system events, store a representation that identifies the instant unambiguously. For appointments, the local time zone may also matter because “09:00 Europe/Berlin” is a business meaning, not just an instant.

Document whether timestamps are stored in UTC, whether original zone is retained, and how daylight-saving transitions are handled.

Identifiers that look numeric are often text

Phone numbers, postal codes, account numbers, and serial numbers may contain leading zeros, letters, separators, or country-specific formatting. They are often identifiers, not quantities.

postal_code TEXTphone_number TEXTserial_number TEXT

You do not add two phone numbers or calculate the average postal code. Numeric storage may destroy meaningful formatting.

Controlled vocabularies

A status domain can be enforced with a check:

CHECK (status_code IN  ('open','scheduled','in_progress','closed','cancelled'))

or with a reference table:

FOREIGN KEY (status_code)  REFERENCES work_order_status(status_code)

Use a reference entity when values have metadata, localization, lifecycle, or independent administration. Use a check when the set is small and application-defined.

Boolean domains

A boolean is appropriate only when exactly two meaningful states exist. If a field can be yes, no, and unknown/not reviewed, a nullable boolean may work but often hides semantics. A status code such as review_status may communicate better.

Text length is not the main domain rule

VARCHAR(255) is a storage limit, not a definition of what the value means. For an ISO country code, a two-character constraint plus reference validation is meaningful. For a person's legal name, an arbitrary 50-character limit may reject legitimate data.

Normalization of textual values

Decide whether values are stored exactly as entered or normalized. Email addresses, phone numbers, serial numbers, and codes may have case or formatting rules. For example, if SKUs are case-insensitive, enforce uniqueness on the normalized representation rather than assuming users always type identical casing.

WorkshopHub domain catalogue

Practice: domain audit

age INTEGERprice FLOATcountry TEXTdate TEXTphone INTEGERactive TEXT

Summary and next lesson

Domains define meaningful value spaces; SQL types are implementation mechanisms. Precision, units, temporal semantics, identifiers, controlled vocabularies, and normalization all belong to domain design. The next lesson turns to entity integrity: keeping each row identifiable through keys and uniqueness constraints.

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.