Schema Design Problems

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

-- WRONG: Using FLOAT for money
CREATE TABLE BadOrder (
    total FLOAT  -- NEVER do this for money!
);
 
INSERT INTO BadOrder VALUES (0.1 + 0.2);
SELECT * FROM BadOrder;
-- Returns: 0.30000000000000004 (floating-point error!)
 
-- CORRECT: Using DECIMAL for money
CREATE TABLE GoodOrder (
    total DECIMAL(12, 2)  -- 12 total digits, 2 after decimal
);
 
INSERT INTO GoodOrder VALUES (0.1 + 0.2);
SELECT * FROM GoodOrder;
-- Returns: 0.30 (exact)
 
-- Choosing DECIMAL precision:
-- DECIMAL(10, 2) → max $99,999,999.99 (8 digits before decimal)
-- DECIMAL(12, 2) → max $9,999,999,999.99 (10 digits before decimal)
-- DECIMAL(15, 2) → max $9,999,999,999,999.99 (13 digits before decimal)
 
-- For currencies with more decimal places (crypto):
-- DECIMAL(20, 8) → supports Bitcoin's 8 decimal places

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

-- PRACTICAL LENGTH GUIDELINES (based on real-world data)
 
CREATE TABLE Customer (
    -- Email: RFC 5321 allows 254 characters max
    email VARCHAR(254) NOT NULL,
    
    -- Names: 100 is usually sufficient, 200 for safety with international names
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    
    -- Phone: E.164 format max 15 digits + formatting
    phone VARCHAR(20),
    
    -- Addresses vary by country; 255 is a reasonable max
    street_address VARCHAR(255),
    city VARCHAR(100),
    
    -- Country/region codes: ISO standards
    country_code CHAR(2),        -- ISO 3166-1 alpha-2 (US, UK, IN)
    postal_code VARCHAR(20),     -- Varies wildly by country
    
    -- URLs: 2048 is practical limit for most use cases
    website VARCHAR(2048),
    
    -- For truly unbounded content:
    bio TEXT,
    notes TEXT
);
 
-- Common mistakes:
-- ❌ VARCHAR(255) for everything (arbitrary, not based on data)
-- ❌ VARCHAR(50) for email (too short!)
-- ❌ TEXT for all strings (loses validation benefit)

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

-- FOREIGN KEY REFERENTIAL ACTIONS
 
CREATE TABLE OrderItems (
    order_item_id   SERIAL PRIMARY KEY,
    order_id        INT NOT NULL,
    product_id      INT NOT NULL,
    quantity        INT NOT NULL CHECK (quantity > 0),
    
    -- ON DELETE CASCADE: Delete items when order is deleted
    -- Good for: dependent entities, log entries, shopping cart items
    FOREIGN KEY (order_id) REFERENCES Orders(order_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    
    -- ON DELETE RESTRICT: Prevent deletion if items exist
    -- Good for: master data (categories, products in use)
    FOREIGN KEY (product_id) REFERENCES Products(product_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);
 
-- REFERENTIAL ACTION OPTIONS:
-- CASCADE    → Propagate the operation (delete/update children)
-- RESTRICT   → Prevent the operation if children exist
-- SET NULL   → Set FK to NULL (column must be nullable)
-- SET DEFAULT→ Set FK to default value
-- NO ACTION  → Like RESTRICT but checked at transaction end
 
-- CHOOSING THE RIGHT ACTION:
-- Parent deletion should delete children? → CASCADE
-- Parent deletion should be prevented?    → RESTRICT
-- Children can exist without parent?      → SET NULL
-- Need to preserve children with default? → SET DEFAULT

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

CREATE TABLE Product (
    product_id       SERIAL PRIMARY KEY,
    name             VARCHAR(200) NOT NULL,
    price            DECIMAL(10, 2) NOT NULL,
    sale_price       DECIMAL(10, 2),
    stock_quantity   INT NOT NULL DEFAULT 0,
    min_order_qty    INT NOT NULL DEFAULT 1,
    max_order_qty    INT,
    status           VARCHAR(20) NOT NULL DEFAULT 'active',
    
    -- Price must be positive
    CONSTRAINT chk_positive_price 
        CHECK (price > 0),
    
    -- Sale price must be less than regular price
    CONSTRAINT chk_sale_price_valid 
        CHECK (sale_price IS NULL OR (sale_price > 0 AND sale_price < price)),
    
    -- Stock cannot be negative
    CONSTRAINT chk_nonnegative_stock 
        CHECK (stock_quantity >= 0),
    
    -- Min order must be positive; max must exceed min
    CONSTRAINT chk_order_qty_range 
        CHECK (min_order_qty > 0 AND (max_order_qty IS NULL OR max_order_qty >= min_order_qty)),
    
    -- Status must be from allowed list
    CONSTRAINT chk_valid_status 
        CHECK (status IN ('active', 'inactive', 'discontinued', 'coming_soon'))
);
 
-- Cross-column CHECK (PostgreSQL):
CREATE TABLE Reservation (
    reservation_id   SERIAL PRIMARY KEY,
    check_in_date    DATE NOT NULL,
    check_out_date   DATE NOT NULL,
    
    -- Check-out must be after check-in
    CONSTRAINT chk_valid_dates 
        CHECK (check_out_date > check_in_date),
    
    -- Booking can't be too far in advance
    CONSTRAINT chk_advance_booking 
        CHECK (check_in_date <= CURRENT_DATE + INTERVAL '1 year')
);

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

-- BASIC INDEX TYPES
 
-- B-tree (default, most common): equality and range queries
CREATE INDEX idx_orders_customer_id ON Orders(customer_id);
CREATE INDEX idx_orders_order_date ON Orders(order_date);
 
-- Composite index: for multi-column queries
-- Column order matters! Most selective column first, or leftmost columns in WHERE
CREATE INDEX idx_orders_customer_date 
    ON Orders(customer_id, order_date DESC);
-- Supports: WHERE customer_id = ?
-- Supports: WHERE customer_id = ? AND order_date > ?
-- Does NOT support: WHERE order_date > ? (without customer_id)
 
-- Covering index: includes all columns needed by query
CREATE INDEX idx_orders_covering 
    ON Orders(customer_id) INCLUDE (order_date, total_amount, status);
-- Query can be answered entirely from index without table access
 
-- Partial index: index only rows matching condition
CREATE INDEX idx_orders_pending 
    ON Orders(order_date) WHERE status = 'pending';
-- Smaller, faster index for common query pattern
 
-- Hash index: equality only, faster for exact matches
CREATE INDEX idx_products_sku ON Products USING hash(sku);
 
-- GiST/GIN: for complex types (arrays, JSON, full-text)
CREATE INDEX idx_products_tags ON Products USING gin(tags);
CREATE INDEX idx_documents_content ON Documents USING gin(to_tsvector('english', content));

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

-- WELL-NAMED SCHEMA
 
CREATE TABLE customer (
    customer_id         SERIAL PRIMARY KEY,
    email               VARCHAR(254) NOT NULL,
    first_name          VARCHAR(100) NOT NULL,
    last_name           VARCHAR(100) NOT NULL,
    phone_number        VARCHAR(20),
    created_at          TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at          TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    is_active           BOOLEAN DEFAULT true,
    
    CONSTRAINT uq_customer_email UNIQUE (email)
);
 
CREATE TABLE product_category (
    category_id         SERIAL PRIMARY KEY,
    category_name       VARCHAR(100) NOT NULL,
    parent_category_id  INT,
    display_order       INT NOT NULL DEFAULT 0,
    
    CONSTRAINT fk_category_parent 
        FOREIGN KEY (parent_category_id) REFERENCES product_category(category_id),
    CONSTRAINT chk_category_not_self_parent 
        CHECK (parent_category_id <> category_id)
);
 
CREATE TABLE "order" (  -- Quoted because ORDER is reserved
    order_id            SERIAL PRIMARY KEY,
    customer_id         INT NOT NULL,
    order_date          TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    status              VARCHAR(20) NOT NULL DEFAULT 'pending',
    shipping_address_id INT,
    total_amount        DECIMAL(12, 2),
    
    CONSTRAINT fk_order_customer 
        FOREIGN KEY (customer_id) REFERENCES customer(customer_id),
    CONSTRAINT chk_order_valid_status 
        CHECK (status IN ('pending', 'confirmed', 'shipped', 'delivered', 'cancelled'))
);
 
-- Indexes with clear names
CREATE INDEX idx_order_customer_id ON "order"(customer_id);
CREATE INDEX idx_order_date ON "order"(order_date DESC);
CREATE INDEX idx_order_status ON "order"(status) WHERE status NOT IN ('delivered', 'cancelled');

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

-- ALWAYS use TIMESTAMP WITH TIME ZONE for points in time
CREATE TABLE audit_log (
    log_id          SERIAL PRIMARY KEY,
    event_type      VARCHAR(50) NOT NULL,
    event_data      JSONB,
    
    -- Good: Timezone-aware timestamp
    created_at      TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    
    -- Bad: Ambiguous - what timezone?
    -- created_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
 
-- Use DATE for calendar dates (no time component)
CREATE TABLE employee (
    employee_id     SERIAL PRIMARY KEY,
    
    -- Date of birth is a calendar date, not a point in time
    birth_date      DATE,
    
    -- Hire date is also a calendar date
    hire_date       DATE NOT NULL,
    
    -- But last_login is a point in time
    last_login      TIMESTAMP WITH TIME ZONE
);
 
-- Use TIME for time-of-day without date
CREATE TABLE business_hours (
    location_id     INT REFERENCES location(location_id),
    day_of_week     INT CHECK (day_of_week BETWEEN 0 AND 6),
    open_time       TIME NOT NULL,
    close_time      TIME NOT NULL,
    PRIMARY KEY (location_id, day_of_week)
);

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

-- STANDARD AUDIT COLUMNS (add to most tables)
CREATE TABLE product (
    product_id      SERIAL PRIMARY KEY,
    name            VARCHAR(200) NOT NULL,
    price           DECIMAL(10, 2) NOT NULL,
    
    -- Audit columns
    created_at      TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL,
    created_by      INT REFERENCES user_account(user_id),
    updated_at      TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_by      INT REFERENCES user_account(user_id)
);
 
-- Auto-update updated_at with trigger
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER trigger_product_updated_at
    BEFORE UPDATE ON product
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();
 
-- SOFT DELETE PATTERN (don't physically delete)
CREATE TABLE customer (
    customer_id     SERIAL PRIMARY KEY,
    email           VARCHAR(254) NOT NULL,
    
    -- Soft delete columns
    is_deleted      BOOLEAN DEFAULT false NOT NULL,
    deleted_at      TIMESTAMP WITH TIME ZONE,
    deleted_by      INT REFERENCES user_account(user_id),
    
    -- Only non-deleted emails must be unique
    CONSTRAINT uq_customer_email_active 
        UNIQUE NULLS NOT DISTINCT (email) WHERE (NOT is_deleted)
);
 
-- Partial index to exclude deleted rows from most queries
CREATE INDEX idx_customer_active ON customer(customer_id) WHERE NOT is_deleted;

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
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215

-- ==============================================
-- E-COMMERCE ORDER SYSTEM - PRODUCTION SCHEMA
-- ==============================================
 
-- Customer (with soft delete and audit)
CREATE TABLE customer (
    customer_id         BIGSERIAL PRIMARY KEY,
    email               VARCHAR(254) NOT NULL,
    password_hash       VARCHAR(255) NOT NULL,
    first_name          VARCHAR(100) NOT NULL,
    last_name           VARCHAR(100) NOT NULL,
    phone               VARCHAR(20),
    
    -- Audit columns
    created_at          TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at          TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL,
    
    -- Soft delete
    is_active           BOOLEAN DEFAULT true NOT NULL,
    deactivated_at      TIMESTAMP WITH TIME ZONE,
    
    -- Constraints
    CONSTRAINT uq_customer_email_active 
        UNIQUE (email) -- Could add WHERE is_active for soft delete
);
 
-- Customer address (one-to-many)
CREATE TABLE customer_address (
    address_id          BIGSERIAL PRIMARY KEY,
    customer_id         BIGINT NOT NULL,
    address_type        VARCHAR(20) NOT NULL DEFAULT 'shipping',
    is_default          BOOLEAN DEFAULT false NOT NULL,
    recipient_name      VARCHAR(200) NOT NULL,
    street_line_1       VARCHAR(255) NOT NULL,
    street_line_2       VARCHAR(255),
    city                VARCHAR(100) NOT NULL,
    state_province      VARCHAR(100),
    postal_code         VARCHAR(20) NOT NULL,
    country_code        CHAR(2) NOT NULL,
    phone               VARCHAR(20),
    
    -- Audit
    created_at          TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at          TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL,
    
    -- Constraints
    CONSTRAINT fk_address_customer 
        FOREIGN KEY (customer_id) REFERENCES customer(customer_id) ON DELETE CASCADE,
    CONSTRAINT chk_address_type 
        CHECK (address_type IN ('shipping', 'billing'))
);
 
-- Product category (self-referencing hierarchy)
CREATE TABLE product_category (
    category_id         SERIAL PRIMARY KEY,
    parent_category_id  INT,
    name                VARCHAR(100) NOT NULL,
    slug                VARCHAR(100) NOT NULL UNIQUE,
    description         TEXT,
    display_order       INT DEFAULT 0 NOT NULL,
    is_active           BOOLEAN DEFAULT true NOT NULL,
    
    CONSTRAINT fk_category_parent 
        FOREIGN KEY (parent_category_id) REFERENCES product_category(category_id),
    CONSTRAINT chk_category_not_self_parent 
        CHECK (parent_category_id IS NULL OR parent_category_id <> category_id)
);
 
-- Product
CREATE TABLE product (
    product_id          BIGSERIAL PRIMARY KEY,
    sku                 VARCHAR(50) NOT NULL UNIQUE,
    name                VARCHAR(200) NOT NULL,
    slug                VARCHAR(200) NOT NULL UNIQUE,
    description         TEXT,
    category_id         INT NOT NULL,
    
    -- Pricing
    base_price          DECIMAL(12, 2) NOT NULL,
    sale_price          DECIMAL(12, 2),
    cost_price          DECIMAL(12, 2), -- For margin calculation
    
    -- Inventory
    stock_quantity      INT NOT NULL DEFAULT 0,
    low_stock_threshold INT DEFAULT 10,
    
    -- Status
    status              VARCHAR(20) NOT NULL DEFAULT 'draft',
    is_featured         BOOLEAN DEFAULT false NOT NULL,
    
    -- Metadata
    weight_kg           DECIMAL(8, 3),
    
    -- Audit
    created_at          TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL,
    updated_at          TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL,
    published_at        TIMESTAMP WITH TIME ZONE,
    
    -- Constraints
    CONSTRAINT fk_product_category 
        FOREIGN KEY (category_id) REFERENCES product_category(category_id),
    CONSTRAINT chk_product_base_price 
        CHECK (base_price > 0),
    CONSTRAINT chk_product_sale_price 
        CHECK (sale_price IS NULL OR (sale_price > 0 AND sale_price < base_price)),
    CONSTRAINT chk_product_status 
        CHECK (status IN ('draft', 'active', 'inactive', 'discontinued')),
    CONSTRAINT chk_product_stock 
        CHECK (stock_quantity >= 0)
);
 
-- Order
CREATE TABLE "order" (
    order_id            BIGSERIAL PRIMARY KEY,
    order_number        VARCHAR(20) NOT NULL UNIQUE, -- Human-readable order number
    customer_id         BIGINT NOT NULL,
    
    -- Status tracking
    status              VARCHAR(20) NOT NULL DEFAULT 'pending',
    
    -- Address snapshot (copied from customer_address at order time)
    shipping_address    JSONB NOT NULL,
    billing_address     JSONB NOT NULL,
    
    -- Totals
    subtotal            DECIMAL(12, 2) NOT NULL,
    shipping_cost       DECIMAL(10, 2) NOT NULL DEFAULT 0,
    tax_amount          DECIMAL(10, 2) NOT NULL DEFAULT 0,
    discount_amount     DECIMAL(10, 2) NOT NULL DEFAULT 0,
    total_amount        DECIMAL(12, 2) NOT NULL,
    
    -- Payment
    currency_code       CHAR(3) NOT NULL DEFAULT 'USD',
    payment_method      VARCHAR(50),
    payment_reference   VARCHAR(255),
    
    -- Timestamps
    ordered_at          TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP NOT NULL,
    confirmed_at        TIMESTAMP WITH TIME ZONE,
    shipped_at          TIMESTAMP WITH TIME ZONE,
    delivered_at        TIMESTAMP WITH TIME ZONE,
    cancelled_at        TIMESTAMP WITH TIME ZONE,
    
    -- Notes
    customer_notes      TEXT,
    internal_notes      TEXT,
    
    -- Constraints
    CONSTRAINT fk_order_customer 
        FOREIGN KEY (customer_id) REFERENCES customer(customer_id),
    CONSTRAINT chk_order_status 
        CHECK (status IN ('pending', 'confirmed', 'processing', 'shipped', 
                          'delivered', 'cancelled', 'refunded')),
    CONSTRAINT chk_order_totals 
        CHECK (total_amount = subtotal + shipping_cost + tax_amount - discount_amount),
    CONSTRAINT chk_order_amounts_positive 
        CHECK (subtotal >= 0 AND shipping_cost >= 0 AND tax_amount >= 0 AND total_amount >= 0)
);
 
-- Order items (weak entity dependent on order)
CREATE TABLE order_item (
    order_id            BIGINT NOT NULL,
    product_id          BIGINT NOT NULL,
    
    -- Quantity and pricing at time of order
    quantity            INT NOT NULL,
    unit_price          DECIMAL(12, 2) NOT NULL, -- Price at order time
    discount_percent    DECIMAL(5, 2) DEFAULT 0 NOT NULL,
    line_total          DECIMAL(12, 2) NOT NULL,
    
    -- Product snapshot (in case product changes/deleted)
    product_name        VARCHAR(200) NOT NULL,
    product_sku         VARCHAR(50) NOT NULL,
    
    -- Constraints
    PRIMARY KEY (order_id, product_id),
    CONSTRAINT fk_orderitem_order 
        FOREIGN KEY (order_id) REFERENCES "order"(order_id) ON DELETE CASCADE,
    CONSTRAINT fk_orderitem_product 
        FOREIGN KEY (product_id) REFERENCES product(product_id) ON DELETE RESTRICT,
    CONSTRAINT chk_orderitem_quantity 
        CHECK (quantity > 0),
    CONSTRAINT chk_orderitem_price 
        CHECK (unit_price >= 0),
    CONSTRAINT chk_orderitem_discount 
        CHECK (discount_percent >= 0 AND discount_percent <= 100),
    CONSTRAINT chk_orderitem_linetotal 
        CHECK (line_total = quantity * unit_price * (1 - discount_percent / 100))
);
 
-- ==============================================
-- INDEXES
-- ==============================================
 
-- Customer indexes
CREATE INDEX idx_customer_email ON customer(email);
CREATE INDEX idx_customer_active ON customer(customer_id) WHERE is_active;
 
-- Address indexes
CREATE INDEX idx_address_customer ON customer_address(customer_id);
 
-- Product indexes
CREATE INDEX idx_product_category ON product(category_id);
CREATE INDEX idx_product_status ON product(status) WHERE status = 'active';
CREATE INDEX idx_product_featured ON product(is_featured, created_at DESC) WHERE is_featured;
 
-- Order indexes
CREATE INDEX idx_order_customer ON "order"(customer_id);
CREATE INDEX idx_order_status ON "order"(status);
CREATE INDEX idx_order_date ON "order"(ordered_at DESC);
CREATE INDEX idx_order_customer_status ON "order"(customer_id, status, ordered_at DESC);
 
-- Order item indexes (PK already covers order_id, product_id)
-- Additional index if querying by product across orders
CREATE INDEX idx_orderitem_product ON order_item(product_id);