Columns Represent Entity Attributes

The LINE ITEM table records: 1) Order number, 2) CDNo, 3) Quantity, 4) Selling Price, 5) Discount, 6) LineCost, and 7) whether the item was Shipped.

What Is a Column Domain?

A column domain specifies the permissible set of values that a column can hold. It acts as a constraint on the type of data that can be entered into a column, ensuring that every value stored in that column is valid, meaningful, and consistent with the attribute it represents.

A column domain is more than just a data type. It is the combination of:

• Data type - the broad technical category of values the column can store (integer, decimal, text, date, boolean).
• Constraints - additional rules that further restrict the values beyond what the data type alone permits (NOT NULL, CHECK, UNIQUE, DEFAULT, foreign key references).
• Range or value set - in some cases, a specific enumerated set of allowable values. A DayOfWeek column, for example, might restrict values to "Monday", "Tuesday", "Wednesday", and so on.

Column domains serve three purposes:

1. Data integrity - domains ensure that only valid data types and values are accepted, preventing anomalies and inconsistencies from entering the database.
2. Consistency - by defining the domain of each column, you guarantee that data across all rows in a table - and across related tables - remains consistent and reliable.
3. Validation - domains validate data at the point of entry, before it is stored, reducing errors and improving overall data quality.

How ER Diagram Attributes Map to Column Domains

The connection between ER diagram attributes and column domains is the bridge from conceptual database design to physical implementation. At the conceptual level, an ER diagram defines entities and their attributes. Each attribute has a domain - the set of all legal values that attribute can take. Domains are documented as part of the conceptual model but are not usually shown visually on the ER diagram itself (only attribute names appear in ovals).

When the ER diagram is translated into a relational schema, each entity becomes a table, each attribute becomes a column, and the attribute's domain becomes the column's domain - enforced through the data type and constraint definitions in the CREATE TABLE statement. The following table summarizes this mapping:

The CD entity from the Stories on CD course project illustrates this mapping concretely. In the ER diagram, the CD entity has attributes including CDNo (positive integers), CDTitle (text strings), RetailPrice (positive decimal numbers), and AgeGroup (a specific set of strings). The corresponding relational table enforces each of those domains through data types and constraints:

CREATE TABLE CD (
    CDNo        INT            PRIMARY KEY,
    CDTitle     VARCHAR(200)   NOT NULL,
    DistID      INT            NOT NULL,
    RetailPrice DECIMAL(10,2)  CHECK (RetailPrice > 0),
    AgeGroup    VARCHAR(20)    CHECK (AgeGroup IN ('Child', 'Teen', 'Adult', 'All')),
    Description TEXT,
    FOREIGN KEY (DistID) REFERENCES DISTRIBUTOR(DistID)
);

Each column's domain directly implements the attribute's domain from the ER diagram. The RetailPrice CHECK constraint enforces the "positive decimal numbers" domain. The AgeGroup CHECK constraint enforces the enumerated set of valid age group values. The NOT NULL on CDTitle enforces that every CD must have a title. The foreign key on DistID enforces that every CD must reference a valid distributor.

Column Domains in Data Modeling

In data modeling, column domains play a role beyond individual table design. They provide a vocabulary for expressing business rules in database terms and a mechanism for applying consistent constraints across the schema.

Consider a date column that stores order dates. The domain for this column might specify that only dates on or after the company's founding date are valid, and that the date must be stored in ISO 8601 format (YYYY-MM-DD). A CHECK constraint enforces the date range; the DATE data type enforces the format. Together they implement the domain.

One of the practical advantages of thinking in terms of domains during data modeling is maintainability. When the same kind of attribute appears in multiple tables - for example, a price attribute in both a Products table and an OrderItems table - both columns should share the same domain definition (DECIMAL(10,2) with a CHECK constraint requiring a non-negative value). If the business rule changes - for example, prices may now be zero for free items - the change is made consistently across all columns that share the domain.

Column domains also serve as documentation. A column defined as VARCHAR(20) CHECK (AgeGroup IN ('Child', 'Teen', 'Adult', 'All')) communicates its valid values directly in the schema definition. Anyone examining the schema can see immediately what values are permitted without consulting external documentation.

SQL Implementation Examples

The following examples show how column domains are implemented in SQL through data types and constraints. Each column definition expresses a specific domain that the RDBMS will enforce on every INSERT and UPDATE operation:

CREATE TABLE Employees (
    EmployeeID INT            NOT NULL,
    FirstName  VARCHAR(50)    NOT NULL,
    LastName   VARCHAR(50)    NOT NULL,
    BirthDate  DATE,
    Salary     DECIMAL(10, 2) CHECK (Salary > 0),
    Email      VARCHAR(100)   UNIQUE
);

In this table: EmployeeID, FirstName, and LastName are NOT NULL - these attributes must always have values. Salary has a CHECK constraint requiring a positive value. Email has a UNIQUE constraint preventing duplicate email addresses. BirthDate uses the DATE type, preventing storage of non-date values.

CREATE TABLE Products (
    ProductID   INT            PRIMARY KEY,
    ProductName VARCHAR(100)   NOT NULL,
    Price       DECIMAL(10, 2) CHECK (Price >= 0),
    Stock       INT            CHECK (Stock >= 0)
);

Here, Price and Stock have CHECK constraints requiring non-negative values. A product price cannot be negative, and a stock level cannot be negative - these are business rules expressed as column domain constraints in the schema.

Why Data Types Matter: Use the Right Domain for Each Field

Available Data Domains in an SQL-Based RDBMS