Describe Attribute Domains

In a well-designed database, every attribute has a domain—the allowed set of values and rules for that column. A domain combines data type, format, range, nullability, and any business rules that make values valid and meaningful. Clear domains protect integrity, keep data consistent, and simplify downstream querying and analytics.

• Type & format: e.g., INTEGER, VARCHAR(100), DATE, plus formatting rules (patterns, precision, scale).
• Value rules: ranges, sets, enumerations, and cross-field dependencies (e.g., EndDate ≥ StartDate).
• Business constraints: codes must exist in a reference list; percentages between 0 and 100; ISO country codes; etc.
• Nullability & defaults: whether the attribute may be missing and what default to apply if omitted.

Attribute	Domain (concise spec)
EmployeeID	BIGINT, NOT NULL, UNIQUE
FullName	VARCHAR(200), NOT NULL, trimmed, no control chars
Age	INTEGER, CHECK (Age BETWEEN 18 AND 65)
Email	VARCHAR(254), CHECK (Email LIKE '%@%._%')
Salary	DECIMAL(12,2), CHECK (Salary >= 0.00)
SizeCode	ENUM via reference table (S, M, L, XL)

• Integrity: Invalid data is rejected at write time.
• Consistency: Same attribute means the same thing everywhere.
• Optimization: Proper types enable better indexing and plans.
• Governance: Domains make rules explicit and auditable.

Column-level constraints keep rules close to the data:

CREATE TABLE Employees (
  EmployeeID   BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  FullName     VARCHAR(200) NOT NULL,
  Age          INTEGER CHECK (Age BETWEEN 18 AND 65),
  Email        VARCHAR(254) CHECK (Email LIKE '%@%._%'),
  Salary       DECIMAL(12,2) CHECK (Salary >= 0.00),
  SizeCode     CHAR(2)  -- domain enforced via FK below
);

-- Reference (code) table = domain as a set
CREATE TABLE Sizes (
  SizeCode CHAR(2) PRIMARY KEY
);
INSERT INTO Sizes(SizeCode) VALUES ('S'),('M'),('L'),('XL');

ALTER TABLE Employees
  ADD CONSTRAINT fk_employees_size
  FOREIGN KEY (SizeCode) REFERENCES Sizes(SizeCode);

Check constraints for business rules:

ALTER TABLE Orders
ADD CONSTRAINT chk_order_dates
CHECK (ShipDate IS NULL OR ShipDate >= OrderDate);

Reusable domain types (where supported): some systems let you define a domain once and reuse it.

-- PostgreSQL example
CREATE DOMAIN email_address AS VARCHAR(254)
  CHECK (VALUE LIKE '%@%._%');

ALTER TABLE Contacts
  ADD COLUMN WorkEmail email_address NOT NULL;

• Fit the meaning, not the look: US ZIP codes look numeric, but use CHAR(5)/CHAR(10) to preserve leading zeros.
• Money: Prefer DECIMAL(p,s) (or integer cents) over floating types.
• Dates & times: Store as DATE/TIMESTAMP with a clear timezone policy; avoid free-text dates.
• Phone numbers: Store as VARCHAR with a canonical format (e.g., E.164) and a CHECK pattern if feasible.
• Booleans: Use native BOOLEAN rather than “Y/N” strings when available.
• Large objects: Use BLOB/bytea for binaries; consider storing only links plus metadata if the DB shouldn’t hold files.

Prefer reference (code) tables for small, curated sets (statuses, country codes). They provide validation via FKs and make changes auditable. Use free text only when the set is open-ended.

• Document domains in a data dictionary (name, type, length, semantics, rules, examples).
• When rules change, apply a migration (ALTER constraints, backfill/cleanse data, reindex if needed).
• Guard downstream uses (views, ETL, reports) with tests to catch domain drift early.

• Using generic types (TEXT everywhere) and relying on apps to validate.
• Storing lists in one column; use child tables for one-to-many facts.
• Encoding flags as magic numbers without constraints or documentation.

Each attribute’s domain defines what values are valid and why. Express domains with precise types, CHECKs, FKs to reference lists, and (where available) reusable domain types. Strong domains keep data clean today and resilient as rules evolve. The next lesson explains the problem with multi-valued attributes.

Before moving on to the next lesson, test your understanding of domain attribute types.
Attribute Domains Database - Exercise

[1] domain constraints: Rules that require values of attributes to come from specific domains (e.g., text, numbers, date, etc.).