Cấu trúc Database Website thương mại điện tử: sản phẩm, tồn kho, đơn hàng và thanh toán an toàn

Đây là cấu trúc phù hợp cho shop có biến thể sản phẩm (màu/size), giỏ hàng, địa chỉ giao, đơn hàng, tồn kho và thanh toán qua cổng thứ ba. Quy tắc nền tảng: giá/tên trong `order_items` là **bản chụp tại lúc đặt hàng**; không lấy lại giá hiện tại của `products` để hiển thị hóa đơn cũ. Cổng thanh toán chỉ trả mã tham chiếu/token, không lưu số thẻ hoặc CVV. ## Quan hệ chính ```text users ─< addresses products ─< product_variants ─< inventory_movements users ─< carts ─< cart_items >─ product_variants users ─< shop_orders ─< order_items >─ product_variants shop_orders ─< payment_attempts ``` ## 1. Tạo database và schema ```sql CREATE DATABASE IF NOT EXISTS commerce_app CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE commerce_app; CREATE TABLE users ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, email VARCHAR(255) NOT NULL, password_hash VARCHAR(255) NOT NULL, display_name VARCHAR(120) NOT NULL, status ENUM('active','disabled') NOT NULL DEFAULT 'active', email_verified_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_users_email (email) ) ENGINE=InnoDB; CREATE TABLE addresses ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL, recipient_name VARCHAR(120) NOT NULL, phone VARCHAR(30) NOT NULL, line1 VARCHAR(255) NOT NULL, line2 VARCHAR(255) NULL, ward VARCHAR(120) NULL, district VARCHAR(120) NULL, province VARCHAR(120) NOT NULL, country_code CHAR(2) NOT NULL DEFAULT 'VN', is_default BOOLEAN NOT NULL DEFAULT FALSE, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_addresses_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, KEY idx_addresses_user (user_id) ) ENGINE=InnoDB; CREATE TABLE categories ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, parent_id BIGINT UNSIGNED NULL, name VARCHAR(160) NOT NULL, slug VARCHAR(180) NOT NULL, is_active BOOLEAN NOT NULL DEFAULT TRUE, UNIQUE KEY uq_categories_slug (slug), CONSTRAINT fk_categories_parent FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE SET NULL, KEY idx_categories_parent (parent_id) ) ENGINE=InnoDB; CREATE TABLE products ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, category_id BIGINT UNSIGNED NULL, name VARCHAR(255) NOT NULL, slug VARCHAR(280) NOT NULL, description MEDIUMTEXT NULL, status ENUM('draft','active','archived') NOT NULL DEFAULT 'draft', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_products_slug (slug), CONSTRAINT fk_products_category FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL, KEY idx_products_category_status (category_id, status) ) ENGINE=InnoDB; CREATE TABLE product_variants ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, product_id BIGINT UNSIGNED NOT NULL, sku VARCHAR(100) NOT NULL, option_values_json JSON NULL, price_amount DECIMAL(15,2) NOT NULL, currency CHAR(3) NOT NULL DEFAULT 'VND', stock_on_hand INT NOT NULL DEFAULT 0, is_active BOOLEAN NOT NULL DEFAULT TRUE, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uq_variants_sku (sku), CONSTRAINT fk_variants_product FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE, KEY idx_variants_product_active (product_id, is_active), CHECK (price_amount >= 0), CHECK (stock_on_hand >= 0) ) ENGINE=InnoDB; CREATE TABLE inventory_movements ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, variant_id BIGINT UNSIGNED NOT NULL, movement_type ENUM('receipt','sale','return','adjustment','reservation_release') NOT NULL, quantity_delta INT NOT NULL, reference_type VARCHAR(40) NULL, reference_id BIGINT UNSIGNED NULL, note VARCHAR(500) NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_inventory_variant FOREIGN KEY (variant_id) REFERENCES product_variants(id), KEY idx_inventory_variant_created (variant_id, created_at), KEY idx_inventory_reference (reference_type, reference_id) ) ENGINE=InnoDB; CREATE TABLE carts ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NULL, guest_token_hash CHAR(64) NULL, expires_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, CONSTRAINT fk_carts_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, KEY idx_carts_user (user_id), KEY idx_carts_expiry (expires_at) ) ENGINE=InnoDB; CREATE TABLE cart_items ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, cart_id BIGINT UNSIGNED NOT NULL, variant_id BIGINT UNSIGNED NOT NULL, quantity INT UNSIGNED NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_cart_variant (cart_id, variant_id), CONSTRAINT fk_cart_items_cart FOREIGN KEY (cart_id) REFERENCES carts(id) ON DELETE CASCADE, CONSTRAINT fk_cart_items_variant FOREIGN KEY (variant_id) REFERENCES product_variants(id), CHECK (quantity > 0) ) ENGINE=InnoDB; CREATE TABLE shop_orders ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, order_no VARCHAR(40) NOT NULL, user_id BIGINT UNSIGNED NULL, status ENUM('pending_payment','paid','processing','shipped','completed','cancelled','refunded') NOT NULL DEFAULT 'pending_payment', currency CHAR(3) NOT NULL DEFAULT 'VND', subtotal_amount DECIMAL(15,2) NOT NULL, shipping_amount DECIMAL(15,2) NOT NULL DEFAULT 0, discount_amount DECIMAL(15,2) NOT NULL DEFAULT 0, total_amount DECIMAL(15,2) NOT NULL, shipping_address_json JSON NOT NULL, placed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, paid_at DATETIME NULL, UNIQUE KEY uq_orders_no (order_no), CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL, KEY idx_orders_user_created (user_id, placed_at), KEY idx_orders_status_created (status, placed_at), CHECK (subtotal_amount >= 0), CHECK (total_amount >= 0) ) ENGINE=InnoDB; CREATE TABLE order_items ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, order_id BIGINT UNSIGNED NOT NULL, variant_id BIGINT UNSIGNED NULL, sku_snapshot VARCHAR(100) NOT NULL, product_name_snapshot VARCHAR(255) NOT NULL, options_snapshot_json JSON NULL, unit_price_amount DECIMAL(15,2) NOT NULL, quantity INT UNSIGNED NOT NULL, line_total_amount DECIMAL(15,2) NOT NULL, CONSTRAINT fk_order_items_order FOREIGN KEY (order_id) REFERENCES shop_orders(id) ON DELETE CASCADE, CONSTRAINT fk_order_items_variant FOREIGN KEY (variant_id) REFERENCES product_variants(id) ON DELETE SET NULL, KEY idx_order_items_order (order_id), CHECK (quantity > 0), CHECK (unit_price_amount >= 0), CHECK (line_total_amount >= 0) ) ENGINE=InnoDB; CREATE TABLE payment_attempts ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, order_id BIGINT UNSIGNED NOT NULL, provider VARCHAR(60) NOT NULL, provider_transaction_id VARCHAR(160) NULL, idempotency_key CHAR(36) NOT NULL, status ENUM('created','pending','succeeded','failed','cancelled','refunded') NOT NULL DEFAULT 'created', amount DECIMAL(15,2) NOT NULL, provider_payload_json JSON NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uq_payment_idempotency (idempotency_key), UNIQUE KEY uq_provider_transaction (provider, provider_transaction_id), CONSTRAINT fk_payments_order FOREIGN KEY (order_id) REFERENCES shop_orders(id) ON DELETE CASCADE, KEY idx_payments_order_status (order_id, status) ) ENGINE=InnoDB; ``` ## 2. Luồng đặt hàng không bán quá tồn Trong transaction, khóa biến thể trước khi kiểm tra tồn, tạo order/item, ghi ledger tồn và giảm số tồn. Không lấy `quantity` từ frontend làm giá; PHP phải truy vấn giá server-side. ```sql START TRANSACTION; SELECT id, price_amount, stock_on_hand FROM product_variants WHERE id = ? AND is_active = 1 FOR UPDATE; -- Ứng dụng kiểm tra stock_on_hand >= số lượng cần mua, -- rồi INSERT shop_orders, order_items. UPDATE product_variants SET stock_on_hand = stock_on_hand - ? WHERE id = ? AND stock_on_hand >= ?; INSERT INTO inventory_movements (variant_id, movement_type, quantity_delta, reference_type, reference_id) VALUES (?, 'sale', -?, 'order', ?); COMMIT; ``` Kiểm tra số hàng bị ảnh hưởng của `UPDATE`; nếu bằng 0 thì rollback vì đã hết hàng. Webhook thanh toán phải xác minh chữ ký của provider và xử lý idempotent qua `idempotency_key`/`provider_transaction_id`; không đổi trạng thái đơn chỉ vì trình duyệt báo “thanh toán thành công”. Bài 02 chuyển sang SaaS đa tenant — nơi `tenant_id` phải xuất hiện trong mọi truy vấn nghiệp vụ.