MariaDB 10.6+ & MySQL 8.0+ InnoDB Blueprint
14 Lean Commerce Tables + 13 First-Party Auth/Queue Tables
Because WinCart targets single-TLD deployments exclusively, no store_id or domain_id column pollutes primary keys, foreign keys, or composite indexes. Every checkout mutation runs inside $db->transException(true)->transStart().
SELECT ... FOR UPDATE) prevent overselling during the 15-minute payment window without allowing Add-to-Cart bots to exhaust catalog stock.| Table | Module | Key Columns & Types | Integrity Invariant | Index Strategy |
|---|---|---|---|---|
| products & product_variants | Modules\Catalog | slug, sku, price_minor BIGINT, stock_qty INT | Every product has ≥1 default SKU variant; zero float prices | Unique (slug), Unique (sku) |
| categories & product_categories | Modules\Catalog | parent_id, slug, sort_order | Cascading FK links between catalog items and hierarchical categories | Unique (product_id, category_id) |
| carts & cart_items | Modules\Cart | cart_token CHAR(36), user_id INT NULL, variant_id | HttpOnly UUID cookie merges cleanly on Shield customer login | Unique (cart_token), Unique (cart_id, variant_id) |
| coupons & shipping_methods | Modules\Checkout | code, type, value_minor, min_subtotal_minor | Fixed minor-unit or basis-point % discounts + flat/weight shipping | Unique (code), Active window index |
| stock_reservations | Modules\Checkout | variant_id, cart_id, qty, expires_at DATETIME | Short-lived 15-min checkout hold acquired under FOR UPDATE lock | Composite (variant_id, expires_at) |
| orders & order_items | Modules\Orders | order_number, unit_price_minor, billing_address_json | Immutable point-in-time ledger snapshot immune to future catalog edits | Unique (order_number), FK (order_id) |
| payment_transactions | Modules\Orders | gateway, gateway_reference_id, amount_minor | Guarantees idempotent webhook settlement across retries | Unique (gateway, gateway_reference_id) |