SQLschema_design.sql
Copy
1
-- E-commerce schema (SQLite compatible)
2
 
3
CREATE TABLE users (
4
    user_id    TEXT PRIMARY KEY DEFAULT (lower(hex(randomblob(16)))),
5
    email      TEXT NOT NULL UNIQUE,
6
    name       TEXT NOT NULL,
7
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
8
    CHECK (length(email) > 3 AND email LIKE '%@%')
9
);
10
 
11
CREATE TABLE categories (
12
    category_id INTEGER PRIMARY KEY AUTOINCREMENT,
13
    name        TEXT NOT NULL UNIQUE,
14
    parent_id   INTEGER REFERENCES categories(category_id)
15
);
16
 
17
CREATE TABLE products (
18
    product_id  INTEGER PRIMARY KEY AUTOINCREMENT,
19
    name        TEXT NOT NULL,
20
    description TEXT,
21
    price       REAL NOT NULL CHECK (price >= 0),
22
    stock       INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0),
23
    category_id INTEGER NOT NULL REFERENCES categories(category_id),
24
    is_active   INTEGER NOT NULL DEFAULT 1 CHECK (is_active IN (0, 1)),
25
    created_at  TEXT NOT NULL DEFAULT (datetime('now'))
26
);
27
 
28
CREATE TABLE orders (
29
    order_id    INTEGER PRIMARY KEY AUTOINCREMENT,
30
    user_id     TEXT NOT NULL REFERENCES users(user_id),
31
    status      TEXT NOT NULL DEFAULT 'pending'
32
                CHECK (status IN ('pending', 'confirmed', 'shipped', 'delivered', 'cancelled')),
33
    total       REAL NOT NULL CHECK (total >= 0),
34
    created_at  TEXT NOT NULL DEFAULT (datetime('now')),
35
    shipped_at  TEXT,
36
    CHECK (shipped_at IS NULL OR shipped_at >= created_at)
37
);
38
 
39
CREATE TABLE order_items (
40
    order_id   INTEGER NOT NULL REFERENCES orders(order_id) ON DELETE CASCADE,
41
    product_id INTEGER NOT NULL REFERENCES products(product_id),
42
    quantity   INTEGER NOT NULL CHECK (quantity > 0),
43
    unit_price REAL NOT NULL CHECK (unit_price >= 0),
44
    PRIMARY KEY (order_id, product_id)
45
);
46
 
47
-- Indexes for common queries
48
CREATE INDEX idx_orders_user ON orders (user_id, created_at DESC);
49
CREATE INDEX idx_order_items_product ON order_items (product_id);
50
CREATE INDEX idx_products_category ON products (category_id) WHERE is_active = 1;
51
CREATE INDEX idx_products_price ON products (price);
52