Attributes and Domains in the Relational Model

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42

-- PostgreSQL CREATE DOMAIN creates custom domains with constraints
 
-- Domain: A valid age (non-negative, reasonable maximum)
CREATE DOMAIN age_years AS INTEGER
    CHECK (VALUE >= 0 AND VALUE < 150);
 
-- Domain: A valid email address (pattern-based constraint)
CREATE DOMAIN email_address AS VARCHAR(255)
    CHECK (VALUE ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');
 
-- Domain: A monetary amount (precise decimal, non-negative)
CREATE DOMAIN monetary_amount AS DECIMAL(15, 2)
    CHECK (VALUE >= 0);
 
-- Domain: A US ZIP code (5 digits or ZIP+4 format)
CREATE DOMAIN us_zip_code AS VARCHAR(10)
    CHECK (VALUE ~ '^[0-9]{5}(-[0-9]{4})?$');
 
-- Domain: An ISO country code (exactly 2 uppercase letters)
CREATE DOMAIN country_code AS CHAR(2)
    CHECK (VALUE ~ '^[A-Z]{2}$');
 
-- Domain: A percentage (0 to 100 inclusive)
CREATE DOMAIN percentage AS DECIMAL(5, 2)
    CHECK (VALUE >= 0 AND VALUE <= 100);
 
-- Using domains in table definitions
CREATE TABLE Customer (
    customer_id     SERIAL PRIMARY KEY,
    email           email_address       NOT NULL UNIQUE,
    age             age_years,
    country         country_code        NOT NULL DEFAULT 'US'
);
 
CREATE TABLE Order_Line (
    order_id        INT             NOT NULL,
    product_id      INT             NOT NULL,
    quantity        INTEGER         NOT NULL CHECK (quantity > 0),
    unit_price      monetary_amount NOT NULL,
    discount_pct    percentage      DEFAULT 0,
    PRIMARY KEY (order_id, product_id)
);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

-- APPROACH 1: Using generic data types (common but problematic)
CREATE TABLE Employee_v1 (
    employee_id     INT PRIMARY KEY,
    employee_age    INT,
    department_id   INT
);
 
CREATE TABLE Order_v1 (
    order_id        INT PRIMARY KEY,
    quantity        INT,
    product_id      INT
);
 
-- This NONSENSICAL query is allowed because everything is INT:
SELECT * FROM Employee_v1 e, Order_v1 o
WHERE e.employee_age = o.quantity;  -- Comparing age to quantity?!
 
-- APPROACH 2: Using explicit domains (PostgreSQL)
CREATE DOMAIN person_age AS SMALLINT
    CHECK (VALUE >= 0 AND VALUE < 150);
 
CREATE DOMAIN item_quantity AS INTEGER
    CHECK (VALUE > 0);
 
CREATE DOMAIN employee_identifier AS INTEGER;
CREATE DOMAIN department_identifier AS INTEGER;
CREATE DOMAIN order_identifier AS INTEGER;
CREATE DOMAIN product_identifier AS INTEGER;
 
CREATE TABLE Employee_v2 (
    employee_id     employee_identifier PRIMARY KEY,
    employee_age    person_age,
    department_id   department_identifier
);
 
CREATE TABLE Order_v2 (
    order_id        order_identifier PRIMARY KEY,
    quantity        item_quantity,
    product_id      product_identifier
);
 
-- Now domains provide (limited) semantic meaning
-- Though SQL still allows cross-domain comparison (a limitation)
-- At minimum, CHECK constraints prevent invalid values

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40

-- PostgreSQL ENUM creates a finite ordered domain
CREATE TYPE order_status AS ENUM (
    'pending',      -- Ordered first
    'confirmed',    
    'processing',   
    'shipped',      
    'delivered',    
    'cancelled'     -- Ordered last
);
 
-- The enum has natural ordering for comparison
CREATE TABLE orders (
    order_id        SERIAL PRIMARY KEY,
    status          order_status NOT NULL DEFAULT 'pending'
);
 
-- Enum ordering enables meaningful comparison
SELECT * FROM orders 
WHERE status >= 'processing';  -- Returns processing, shipped, delivered
 
SELECT * FROM orders
WHERE status BETWEEN 'confirmed' AND 'shipped';
 
-- Boolean domain: exactly two values
CREATE TABLE feature_flags (
    flag_name       VARCHAR(100) PRIMARY KEY,
    is_enabled      BOOLEAN NOT NULL DEFAULT false
);
 
-- Integer domain with constraints (simulating bounded cardinality)
CREATE DOMAIN day_of_month AS SMALLINT
    CHECK (VALUE >= 1 AND VALUE <= 31);
 
CREATE DOMAIN rating_score AS SMALLINT
    CHECK (VALUE >= 1 AND VALUE <= 5);
 
-- String domain with pattern constraint
CREATE DOMAIN product_sku AS VARCHAR(20)
    CHECK (VALUE ~ '^[A-Z]{3}-[0-9]{4}-[A-Z0-9]{4}$');
    -- Format: ABC-1234-XY99

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59

-- Layer 1: SYNTACTIC domain validation in database
 
-- Email must match pattern (syntactic)
CREATE DOMAIN email_address AS VARCHAR(255)
    CHECK (VALUE ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');
 
-- Phone must match pattern (syntactic)
CREATE DOMAIN phone_number AS VARCHAR(20)
    CHECK (VALUE ~ '^\+?[0-9]{10,15}$');
 
-- Layer 2: REFERENCE domain validation via foreign keys
 
-- Country codes must exist in reference table (semantic via reference)
CREATE TABLE ref_country (
    country_code    CHAR(2) PRIMARY KEY,
    country_name    VARCHAR(100) NOT NULL,
    is_active       BOOLEAN DEFAULT true
);
 
-- Insert reference data
INSERT INTO ref_country (country_code, country_name) VALUES
    ('US', 'United States'),
    ('GB', 'United Kingdom'),
    ('DE', 'Germany'),
    ('FR', 'France');
 
-- Address table with semantic validation via FK
CREATE TABLE address (
    address_id      SERIAL PRIMARY KEY,
    street_line     VARCHAR(200) NOT NULL,
    city            VARCHAR(100) NOT NULL,
    country_code    CHAR(2) NOT NULL REFERENCES ref_country(country_code)
    -- FK ensures country_code is semantically valid (exists in reference)
);
 
-- Layer 3: SEMANTIC validation requiring business logic
 
-- Currency codes with exchange rate validation
CREATE TABLE ref_currency (
    currency_code   CHAR(3) PRIMARY KEY,
    currency_name   VARCHAR(50) NOT NULL,
    is_tradeable    BOOLEAN NOT NULL DEFAULT true,
    last_verified   TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
 
-- A trigger could verify currency is still tradeable before insert
CREATE OR REPLACE FUNCTION check_currency_active()
RETURNS TRIGGER AS $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM ref_currency 
        WHERE currency_code = NEW.currency_code 
        AND is_tradeable = true
    ) THEN
        RAISE EXCEPTION 'Currency % is not currently tradeable', NEW.currency_code;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31

-- Domain-compatible operations (CORRECT)
 
-- Two attributes from the same domain: department_identifier
SELECT e.employee_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;  -- Same domain: ✓
 
-- Two attributes from the same domain: customer_identifier
SELECT o.order_id, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;  -- Same domain: ✓
 
-- Domain-INCOMPATIBLE operations (BUGS that SQL allows)
 
-- BUG: Comparing employee ID with product ID (different entity namespaces)
SELECT * FROM employees e, products p
WHERE e.employee_id = p.product_id;  -- Syntactically valid, semantically meaningless
 
-- BUG: Joining on semantically unrelated attributes
SELECT * FROM orders o, inventory i
WHERE o.quantity = i.quantity;  -- Both INT, but unrelated measurements
 
-- MITIGATION: Use naming conventions to make domain membership obvious
-- All identifiers: entity_id pattern
-- All amounts: entity_amount pattern
-- All timestamps: action_at pattern
 
-- With consistent naming, code review can catch cross-domain bugs
SELECT e.customer_id, p.product_id  -- Different domains obvious from names
FROM ...
WHERE e.customer_id = p.product_id;  -- Reviewer should flag this

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57

-- User-defined domains for a business application
 
-- Monetary values: precise decimal, always non-negative
CREATE DOMAIN monetary_amount AS DECIMAL(15, 2)
    CHECK (VALUE >= 0)
    CONSTRAINT monetary_amount_positive
    NOT NULL;
 
-- Tax rates: percentage with bounds
CREATE DOMAIN tax_rate AS DECIMAL(5, 4)
    CHECK (VALUE >= 0 AND VALUE <= 0.5)  -- Up to 50%
    DEFAULT 0;
 
-- Product SKU: specific format enforced
CREATE DOMAIN product_sku AS VARCHAR(20)
    CHECK (VALUE ~ '^[A-Z]{2,4}-[0-9]{3,6}$')
    NOT NULL;
 
-- Email addresses: pattern validation
CREATE DOMAIN email_address AS VARCHAR(255)
    CHECK (VALUE ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');
 
-- US phone numbers: standardized format
CREATE DOMAIN us_phone AS CHAR(12)
    CHECK (VALUE ~ '^[0-9]{3}-[0-9]{3}-[0-9]{4}$');
 
-- URL format: basic pattern validation
CREATE DOMAIN url AS VARCHAR(2048)
    CHECK (VALUE ~* '^https?://[a-z0-9.-]+\.[a-z]{2,}(/.*)?$');
 
-- UUID: for distributed ID generation
CREATE DOMAIN entity_uuid AS UUID
    DEFAULT gen_random_uuid();
 
-- Use domains in table definitions
CREATE TABLE product (
    product_id      entity_uuid     PRIMARY KEY,
    sku             product_sku     UNIQUE,
    name            VARCHAR(200)    NOT NULL,
    unit_price      monetary_amount,
    cost            monetary_amount,
    tax_rate_code   tax_rate
);
 
CREATE TABLE customer (
    customer_id     entity_uuid     PRIMARY KEY,
    email           email_address   UNIQUE,
    phone           us_phone,
    website         url
);
 
-- Domain changes propagate automatically
-- If tax policy changes, update the domain:
ALTER DOMAIN tax_rate 
    DROP CONSTRAINT tax_rate_check,
    ADD CONSTRAINT tax_rate_check CHECK (VALUE >= 0 AND VALUE <= 0.6);
-- All tables using tax_rate now accept up to 60%

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59

-- Simulating domain hierarchy with layered domains
 
-- Level 1: Base numeric domain
CREATE DOMAIN positive_decimal AS DECIMAL(15, 2)
    CHECK (VALUE > 0);
 
-- Level 2: Specialized monetary domain (inherits implied positivity)
CREATE DOMAIN monetary_amount AS DECIMAL(15, 2)
    CHECK (VALUE >= 0);  -- Monetary can be zero (unlike positive_decimal)
 
-- Level 3: Further specialization for specific purposes
CREATE DOMAIN sale_price AS DECIMAL(15, 2)
    CHECK (VALUE > 0);  -- Prices must be positive
 
CREATE DOMAIN discount_amount AS DECIMAL(15, 2)
    CHECK (VALUE >= 0);  -- Discounts are non-negative
 
-- Base string domain for identifiers
CREATE DOMAIN identifier AS VARCHAR(255)
    CHECK (LENGTH(TRIM(VALUE)) > 0);  -- Not empty or whitespace
 
-- Specialized identifier domains
CREATE DOMAIN email_address AS VARCHAR(255)
    CHECK (VALUE ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');
 
CREATE DOMAIN url_address AS VARCHAR(2048)
    CHECK (VALUE ~* '^https?://');
 
-- Code domains with specific formats
CREATE DOMAIN country_code AS CHAR(2)
    CHECK (VALUE ~ '^[A-Z]{2}$');
 
CREATE DOMAIN currency_code AS CHAR(3)
    CHECK (VALUE ~ '^[A-Z]{3}$');
 
CREATE DOMAIN language_code AS VARCHAR(5)
    CHECK (VALUE ~ '^[a-z]{2}(-[A-Z]{2})?$');  -- en, en-US, fr-CA
 
-- Using hierarchical domains
CREATE TABLE product (
    product_id      SERIAL PRIMARY KEY,
    name            VARCHAR(200) NOT NULL,
    
    -- All monetary fields share domain semantics
    list_price      sale_price      NOT NULL,
    cost_price      monetary_amount NOT NULL,
    discount        discount_amount DEFAULT 0,
    
    -- Reference domains
    country_origin  country_code,
    base_currency   currency_code   NOT NULL DEFAULT 'USD'
);
 
CREATE TABLE customer (
    customer_id     SERIAL PRIMARY KEY,
    email           email_address   NOT NULL UNIQUE,
    website         url_address,
    locale          language_code   DEFAULT 'en-US'
);