Lesson 6	Attribute Domains
Objective	Describe attribute domains and domain types, and how to enforce them in SQL.

Attribute Domains in DBMS: Data Types and SQL Constraints

An attribute domain is the set of values permitted for an attribute, together with their intended meaning. For example, a percentage discount might allow values from 0 to 100, while a clothing size might allow only S, M, L, and XL. A database data type supplies the underlying representation; constraints narrow it to the values the application permits.

Defining a domain answers more than “Is this a number or text?” It explains what the value measures, which values are acceptable, and how the database will enforce those choices. This lesson connects that modeling decision to SQL types, CHECK constraints, reference tables, and reusable domain types.

Domain, Data Type, and Stored Value: Three Different Ideas

Consider an attribute named DiscountPercent. Its data type could be DECIMAL(5,2), its permitted business values could run from 0.00 through 100.00, and its value in one record could be 15.00. The type, domain, and particular value describe different parts of the design.

The domain includes values that are allowed even if no record currently contains them. If the only discounts currently stored are 5 and 10, that does not mean 15 is outside the domain. Conversely, seeing an erroneous value in imported data does not make it legitimate.

Two attributes can share a type without sharing a meaning. TemperatureCelsius and DiscountPercent might both use decimals, but their units and permitted ranges differ. Giving both the same storage type does not make comparisons or substitutions between them sensible.

Domain constraints restrict individual values. Other rules address combinations of values or relationships between records. For example, EndDate must follow StartDate is a rule involving two attributes; EmployeeID must be unique involves multiple rows. A complete design documents these rules alongside domains without calling them all the same thing.

The previous lesson's employee instances supplied concrete attribute values. Here the question changes: which other values could those attributes legitimately contain?

Examples of Attribute Domains

Illustrative domains and separate implementation rules
Attribute	Meaning and permitted values	Possible implementation
DiscountPercent	Percentage discount, 0 through 100	DECIMAL(5,2), range CHECK
SizeCode	A code in the maintained clothing-size list	VARCHAR(2), foreign key
PostalCode	Postal identifier under a defined country's addressing rules	Character type; country-specific validation
BirthDate	A person's recorded birth date	DATE; separate plausibility and missing-value policies
UnitPrice	Nonnegative price in a specified currency and unit	DECIMAL with chosen precision and scale; range rule
EmployeeID	An identifier allocated under the organization's rules	Integer or character type; primary key separately enforces identity

These are design examples, not universal column definitions. A retailer may need sizes beyond XL, and international postal codes do not fit a single numeric pattern. Start with the actual requirements, then choose a representation large enough for the legitimate values.

Document whether a value is required as well. Optional BirthDate and required BirthDate can use the same underlying type, but they have different missing-information policies. Do not fill unknown dates with invented values merely to satisfy a required field.

Choose a Type That Fits the Meaning

Use numeric types for quantities on which arithmetic makes sense. Use text for identifiers that merely contain digits. A postal code such as 02108 must retain its leading zero, and adding two postal codes is not a meaningful business operation. Phone numbers similarly require a text representation and an explicit policy for country prefixes and extensions.

Use date and time types for temporal facts rather than storing display strings such as “03/04/26.” The application can format a stored date for the reader's locale. For time-of-day or timestamp attributes, decide whether the fact refers to a local wall-clock time or an instant, and define the necessary time-zone handling.

For an exact decimal quantity, precision specifies the total digit capacity and scale specifies the fractional places in the ordinary DECIMAL(p,s) form. DECIMAL(5,2) provides three digits before and two after the decimal point, enough for 100.00. Its type alone also admits values outside the desired percentage range.

PostgreSQL rounds input to a declared numeric scale before checking whether it fits. Therefore, two decimal places in a declaration do not mean that every input with additional fractional digits is rejected. If the input itself must have a particular precision, validate it before that conversion. See PostgreSQL's numeric type documentation.

A money amount also needs a currency and a definition such as unit price, annual salary, or account balance. Choosing DECIMAL does not supply those meanings. Nor is a nonnegative rule appropriate for every monetary attribute: some balances and adjustments legitimately have negative values.

For text, choose length limits from requirements. PostgreSQL measures VARCHAR(n) in characters, whereas storage size also depends on encoding. Other DBMSs have their own length semantics. Avoid treating a number copied from another system as a universal limit; consult the target system's character type documentation.

Enforce a Percentage Domain with SQL

The following examples use PostgreSQL 18 syntax. They are illustrative definitions for a practice schema, not captured execution results. This first table stores discount rules, with a required percentage and a separate identifier.

CREATE TABLE DiscountRule (
    RuleID INTEGER PRIMARY KEY,
    DiscountPercent DECIMAL(5,2) NOT NULL,
    CONSTRAINT ck_discount_percent
        CHECK (DiscountPercent BETWEEN 0 AND 100)
);

INSERT INTO DiscountRule (RuleID, DiscountPercent)
VALUES (1, 15.00);

The expected outcome is one row with a 15 percent discount. The primary key identifies the rule; it does not define the percentage range. The range CHECK and required-value declaration address separate requirements.

Try each of the following statements separately in a disposable practice schema. Each is intended to fail for the reason shown. If your client groups statements into a transaction, an error may require a rollback before you continue.

-- Outside the permitted percentage range:
INSERT INTO DiscountRule VALUES (2, 125.00);

-- Missing a required percentage:
INSERT INTO DiscountRule VALUES (3, NULL);

-- Reuses an existing rule identifier:
INSERT INTO DiscountRule VALUES (1, 20.00);

These examples separate domain validity, missing values, and uniqueness. A value can be inside its domain while the row still violates another rule. The final statement illustrates that distinction: 20.00 is an allowed percentage, but RuleID 1 already exists.

CHECK, NOT NULL, and DEFAULT Have Different Jobs

In PostgreSQL, a CHECK passes when its expression is true or unknown. A comparison against NULL commonly produces unknown, so a range check alone does not require a value. Use NOT NULL when the column must be supplied. The official constraint documentation describes these separate mechanisms.

A default supplies a value when an insert omits the column or explicitly requests DEFAULT. It does not prove that a supplied value is valid. A default of zero for DiscountPercent would not prevent an explicit 125; the range rule remains necessary.

Decide whether a default is truthful. An omitted discount might legitimately mean no discount, but an omitted measurement usually does not mean a measured zero. Similarly, an unknown birth date should not silently become today's date.

An empty text string is another case to define deliberately. In PostgreSQL, NOT NULL does not reject an empty string or a string containing only spaces. If a required label must contain meaningful text, specify that rule and implement appropriate validation. Do not assume all DBMSs treat empty strings identically.

Missing, not applicable, withheld, and not yet collected can carry different business meanings. If those distinctions affect decisions, record them explicitly rather than relying on one placeholder such as “N/A” in an otherwise well-defined numeric or date attribute.

Use a Reference Table for a Maintained Set of Codes

A reference table is useful when allowed values need names, ordering, ownership, or other descriptive information. Here a clothing application maintains its permitted size codes separately from the products that use them.

CREATE TABLE ClothingSize (
    SizeCode VARCHAR(2) PRIMARY KEY,
    SizeLabel VARCHAR(30) NOT NULL
);

INSERT INTO ClothingSize (SizeCode, SizeLabel)
VALUES ('S', 'Small'), ('M', 'Medium'),
       ('L', 'Large'), ('XL', 'Extra large');

CREATE TABLE Product (
    ProductID INTEGER PRIMARY KEY,
    ProductName VARCHAR(200) NOT NULL,
    SizeCode VARCHAR(2) NOT NULL,
    CONSTRAINT fk_product_size
        FOREIGN KEY (SizeCode)
        REFERENCES ClothingSize (SizeCode)
);

This example records one size per product row. A design that offers multiple sizes per product would need a related structure. The foreign key restricts Product.SizeCode to an existing code; it does not establish that the correct size was selected for a particular physical item.

A CHECK containing a short fixed list can be enough for a simple, stable set. A reference table is more useful when the codes need independently maintained descriptions. A native enum is yet another implementation choice; it is not the same object as a reference table.

Retiring a code also needs a policy. Deleting it may conflict with historical references. Keeping the code and marking it inactive can preserve history, but a basic foreign key does not automatically prohibit new use of an inactive row. That additional rule needs deliberate enforcement.

Create a Reusable Domain Type Where Supported

PostgreSQL also provides a named SQL domain that can reuse a base type and a value constraint. The example below expresses the percentage rule once and uses it for two attributes.

CREATE DOMAIN percentage_value AS DECIMAL(5,2)
    CHECK (VALUE BETWEEN 0 AND 100);

CREATE TABLE Campaign (
    CampaignID INTEGER PRIMARY KEY,
    StandardDiscount percentage_value NOT NULL,
    OptionalBonus percentage_value
);

VALUE refers to the candidate domain value. This design leaves missing-value policy at the columns: StandardDiscount is required, while OptionalBonus may be NULL. PostgreSQL recommends allowing NULL at the domain level and applying required-value rules to columns where needed; see CREATE DOMAIN.

Reusing a definition reduces the risk that several independently written range checks drift apart. It also creates a shared dependency: changing the domain can affect every column that uses it. Reuse only when the attributes truly share the same value rules.

The conceptual idea of an attribute domain applies regardless of DBMS. A database object named DOMAIN and the syntax used to declare it depend on the product. Do not paste this PostgreSQL syntax into another system without checking that system's implementation.

Separate Domain Rules from Rules Between Attributes

Two individually valid dates can form an invalid pair. A shipment date earlier than its order date is an example. Here the table rule compares the dates, while the DATE type supplies each attribute's representation.

CREATE TABLE SalesOrder (
    OrderID INTEGER PRIMARY KEY,
    OrderDate DATE NOT NULL,
    ShipDate DATE,
    CONSTRAINT ck_shipping_sequence
        CHECK (ShipDate IS NULL OR ShipDate >= OrderDate)
);

This model permits an unshipped order and requires an order date. It does not verify the date against a carrier's records or define every possible order state. Constraints should be described by the precise guarantees they provide.

Similarly, an employee's age limit is a business eligibility rule, not a universal property of an integer. A fixed range of 18 through 65 is inappropriate as a general employee domain. If age at a particular event matters, define that event and calculate from an appropriate birth-date record rather than storing an age that becomes stale.

Document the Domain in a Data Dictionary

A data dictionary connects business meaning to implementation. For DiscountPercent, record that 15 means fifteen percent, not 0.15, and that the endpoints 0 and 100 are included. State the rounding policy, whether the value is required, and who owns changes to the rule.

Include valid and invalid examples. A description such as “numeric discount” is too vague to guide imports, user interfaces, or reports. A precise definition helps those components interpret the same value consistently. Database enforcement then protects declared rules across different writers, while application checks can provide clearer feedback before a write.

Useful dictionary fields include the attribute name, definition, unit, allowed values, implementation type, length or precision, null policy, default, validation mechanism, and rule owner. Keep cross-column and relationship rules linked but separately described.

Validate Meaning Without Overclaiming

A text pattern is not proof of real-world truth. Checking for an @ sign does not adequately validate an email address, and even a sophisticated syntax check does not prove that a mailbox exists or that the user controls it. Use appropriate application validation and a verification process when ownership matters.

Names also vary widely. Restricting them to letters and spaces can reject legitimate names, while a generous text field still cannot establish that the supplied name belongs to the person. Separate storage limits, normalization rules, and identity verification.

Do not confuse domain correctness with a complete relational design. Packing several phone numbers into one text field can satisfy a length limit while making individual numbers difficult to manage. The next lesson on multivalued attributes examines that structural issue.

Change Domains Carefully as Requirements Evolve

Before tightening a rule, inspect existing data for values that would fail it. Determine whether those values are errors, historical exceptions, or evidence that the proposed definition is incomplete. Cleanse or migrate them under an agreed policy rather than silently discarding information.

Coordinate changes with forms, imports, reports, and dependent tables. Expanding a code list is easy only if consumers also handle the new codes. Test boundary values, missing values, and representative imports, and retain a rollback plan for the migration. A domain is a maintained agreement about meaning, not a one-time column declaration.

Apply the Domain Definition

For each attribute, define what its values mean and which values are permitted. Then select a suitable type and assign each rule to the right enforcement mechanism. Required values, defaults, uniqueness, and relationships complement domain restrictions but answer different questions.

As a self-check, explain why DECIMAL(5,2) alone does not define a percentage domain, why a range CHECK does not replace NOT NULL, and why a foreign key cannot prove that a chosen code describes the real object correctly.

Attribute Domains Database - Exercise

Practice distinguishing domains, data types, and validation rules in the Attribute Domains Database - Exercise.

[1] Domain constraint: a restriction on the values permitted for an attribute. Rules involving several attributes or records should also be documented, with their scope made explicit.