مشروع: قاعدة بيانات متجر إلكتروني — SQL الأساسيات

مشروع: قاعدة بيانات متجر إلكتروني

مشروع يجمع الدورة: تصميم مخطط، قيود تحمي القواعد، معاملات، فهارس، وتقارير. المثال الكامل يعمل على PostgreSQL (يمكنك تشغيله في Supabase SQL Editor أو أي PostgreSQL محلي).

المتطلبات

1. عملاء ومنتجات بمخزون وأسعار.

2. طلبات بعدة عناصر، تحفظ سعر الوحدة وقت الشراء.

3. لا يُباع أكثر من المخزون أبداً، حتى مع طلبات متزامنة.

4. حالات الطلب: pending ← paid ← shipped، أو cancelled.

5. تقارير: المبيعات اليومية، أفضل المنتجات، أفضل العملاء.

المخطط

CREATE TABLE customers (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE,
  city TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE products (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  price NUMERIC(12, 2) NOT NULL CHECK (price >= 0),
  stock INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0)
);

CREATE TABLE orders (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id BIGINT NOT NULL REFERENCES customers(id) ON DELETE RESTRICT,
  status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled')),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- سعر الوحدة يُحفظ وقت الشراء: تغيّر سعر المنتج لاحقاً لا يغيّر فواتير الماضي
CREATE TABLE order_items (
  order_id BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  product_id BIGINT NOT NULL REFERENCES products(id),
  quantity INTEGER NOT NULL CHECK (quantity > 0),
  unit_price NUMERIC(12, 2) NOT NULL CHECK (unit_price >= 0),
  PRIMARY KEY (order_id, product_id)
);

CREATE INDEX orders_customer_idx ON orders(customer_id);
CREATE INDEX order_items_product_idx ON order_items(product_id);

راجع كل قرار:

| القرار | السبب |

|---|---|

| NUMERIC(12,2) للأسعار | لا أخطاء تقريب عشري |

| CHECK (stock >= 0) | القاعدة نفسها ترفض المخزون السالب — خط دفاع حتى لو أخطأ الكود |

| unit_price في order_items | لقطة السعر وقت الشراء |

| مفتاح مركب في order_items | منتج واحد مرة واحدة في الطلب |

| ON DELETE RESTRICT للعميل | لا يُحذف عميل له طلبات (سجل مالي) |

| فهارس على المفاتيح الأجنبية | JOINs وتقارير سريعة |

إنشاء طلب: معاملة واحدة

CREATE TABLE customers (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE,
  city TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE products (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  price NUMERIC(12, 2) NOT NULL CHECK (price >= 0),
  stock INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0)
);

CREATE TABLE orders (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id BIGINT NOT NULL REFERENCES customers(id) ON DELETE RESTRICT,
  status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled')),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- سعر الوحدة يُحفظ وقت الشراء: تغيّر سعر المنتج لاحقاً لا يغيّر فواتير الماضي
CREATE TABLE order_items (
  order_id BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  product_id BIGINT NOT NULL REFERENCES products(id),
  quantity INTEGER NOT NULL CHECK (quantity > 0),
  unit_price NUMERIC(12, 2) NOT NULL CHECK (unit_price >= 0),
  PRIMARY KEY (order_id, product_id)
);

CREATE INDEX orders_customer_idx ON orders(customer_id);
CREATE INDEX order_items_product_idx ON order_items(product_id);

INSERT INTO customers (name, email) VALUES ('سارة', 'sara@example.com');
INSERT INTO products (sku, name, price, stock) VALUES ('PEN-01', 'قلم', 1.50, 100), ('NB-01', 'دفتر', 3.00, 2);

BEGIN;
-- 1) إنشاء الطلب
INSERT INTO orders (customer_id) VALUES (1);
-- 2) خصم المخزون ذرياً: يفشل القيد CHECK إن لم يكفِ المخزون فتُلغى المعاملة كلها
UPDATE products SET stock = stock - 2 WHERE sku = 'NB-01';
UPDATE products SET stock = stock - 5 WHERE sku = 'PEN-01';
-- 3) العناصر بسعر اللحظة
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
SELECT 1, id, CASE sku WHEN 'NB-01' THEN 2 ELSE 5 END, price FROM products WHERE sku IN ('NB-01', 'PEN-01');
COMMIT;

SELECT name, stock FROM products ORDER BY id;

لماذا stock = stock - 2 بدل "اقرأ المخزون في التطبيق ثم اكتب القيمة الجديدة"؟ لأن طلبين متزامنين قد يقرآن نفس القيمة ويبيعان نفس القطعة مرتين. التحديث الذري مع قيد CHECK يمنع ذلك.

التقارير

CREATE TABLE customers (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE,
  city TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE products (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  price NUMERIC(12, 2) NOT NULL CHECK (price >= 0),
  stock INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0)
);

CREATE TABLE orders (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id BIGINT NOT NULL REFERENCES customers(id) ON DELETE RESTRICT,
  status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled')),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- سعر الوحدة يُحفظ وقت الشراء: تغيّر سعر المنتج لاحقاً لا يغيّر فواتير الماضي
CREATE TABLE order_items (
  order_id BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  product_id BIGINT NOT NULL REFERENCES products(id),
  quantity INTEGER NOT NULL CHECK (quantity > 0),
  unit_price NUMERIC(12, 2) NOT NULL CHECK (unit_price >= 0),
  PRIMARY KEY (order_id, product_id)
);

CREATE INDEX orders_customer_idx ON orders(customer_id);
CREATE INDEX order_items_product_idx ON order_items(product_id);

INSERT INTO customers (name, email, city) VALUES ('سارة', 's@example.com', 'بغداد'), ('عمر', 'o@example.com', 'البصرة');
INSERT INTO products (sku, name, price, stock) VALUES ('A', 'قلم', 1.50, 100), ('B', 'حقيبة', 25.00, 10);
INSERT INTO orders (customer_id, status) VALUES (1, 'paid'), (2, 'paid'), (1, 'cancelled');
INSERT INTO order_items VALUES (1, 1, 10, 1.50), (1, 2, 1, 25.00), (2, 2, 2, 25.00), (3, 1, 3, 1.50);

-- أفضل المنتجات (الطلبات المدفوعة فقط)
SELECT p.name, SUM(oi.quantity) AS units, SUM(oi.quantity * oi.unit_price) AS revenue
FROM order_items oi
JOIN orders o ON o.id = oi.order_id AND o.status = 'paid'
JOIN products p ON p.id = oi.product_id
GROUP BY p.name
ORDER BY revenue DESC;

-- المبيعات اليومية
SELECT date_trunc('day', o.created_at) AS day, COUNT(DISTINCT o.id) AS orders, SUM(oi.quantity * oi.unit_price) AS revenue
FROM orders o JOIN order_items oi ON oi.order_id = o.id
WHERE o.status = 'paid'
GROUP BY 1 ORDER BY 1;

View للوحة التحكم

المثال الكامل ينشئ order_summaries: كل طلب مع اسم العميل وعدد العناصر والمجموع — تستخدمه لوحة التحكم كجدول عادي دون تكرار منطق الحساب في الكود.

تحديات إضافية

1. جدول payments (مبلغ، طريقة، حالة) وقيد يمنع دفع طلب ملغى (بـ trigger أو منطق في المعاملة).

2. انتقالات الحالة المسموحة فقط (لا shipped ← pending): دالة PL/pgSQL أو جدول انتقالات.

3. إلغاء الطلب داخل معاملة يعيد الكميات للمخزون.

4. كوبونات خصم بنسبة وصلاحية زمنية وحد استخدام.

5. حوّل المخطط إلى ملفات ترحيل مرقمة (الدرس السابق).

CREATE TABLE customers (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE,
  city TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE products (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  sku TEXT NOT NULL UNIQUE,
  name TEXT NOT NULL,
  price NUMERIC(12, 2) NOT NULL CHECK (price >= 0),
  stock INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0)
);

CREATE TABLE orders (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id BIGINT NOT NULL REFERENCES customers(id) ON DELETE RESTRICT,
  status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled')),
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- سعر الوحدة يُحفظ وقت الشراء: تغيّر سعر المنتج لاحقاً لا يغيّر فواتير الماضي
CREATE TABLE order_items (
  order_id BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  product_id BIGINT NOT NULL REFERENCES products(id),
  quantity INTEGER NOT NULL CHECK (quantity > 0),
  unit_price NUMERIC(12, 2) NOT NULL CHECK (unit_price >= 0),
  PRIMARY KEY (order_id, product_id)
);

CREATE INDEX orders_customer_idx ON orders(customer_id);
CREATE INDEX order_items_product_idx ON order_items(product_id);

-- بيانات تجريبية
INSERT INTO customers (name, email, city) VALUES
  ('سارة', 'sara@example.com', 'بغداد'), ('عمر', 'omar@example.com', 'البصرة');
INSERT INTO products (sku, name, price, stock) VALUES
  ('PEN-01', 'قلم', 1.50, 100), ('NB-01', 'دفتر', 3.00, 20), ('BAG-01', 'حقيبة', 25.00, 3);

-- طلب ناجح: معاملة واحدة
BEGIN;
INSERT INTO orders (customer_id, status) VALUES (1, 'paid');
UPDATE products SET stock = stock - 1 WHERE sku = 'BAG-01';
UPDATE products SET stock = stock - 10 WHERE sku = 'PEN-01';
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
SELECT 1, id, CASE sku WHEN 'BAG-01' THEN 1 ELSE 10 END, price FROM products WHERE sku IN ('BAG-01', 'PEN-01');
COMMIT;

-- طلب ثانٍ لعميل آخر
BEGIN;
INSERT INTO orders (customer_id, status) VALUES (2, 'paid');
UPDATE products SET stock = stock - 2 WHERE sku = 'BAG-01';
INSERT INTO order_items (order_id, product_id, quantity, unit_price)
SELECT 2, id, 2, price FROM products WHERE sku = 'BAG-01';
COMMIT;

-- تغيّر السعر لاحقاً: الفواتير السابقة لا تتأثر
UPDATE products SET price = 30.00 WHERE sku = 'BAG-01';

-- View للوحة التحكم
CREATE VIEW order_summaries AS
SELECT o.id, c.name AS customer, o.status, COUNT(*) AS lines,
       SUM(oi.quantity) AS units, SUM(oi.quantity * oi.unit_price) AS total
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
GROUP BY o.id, c.name, o.status;

SELECT * FROM order_summaries ORDER BY id;

-- أفضل العملاء
SELECT customer, SUM(total) AS spent FROM order_summaries WHERE status = 'paid' GROUP BY customer ORDER BY spent DESC;

SELECT sku, stock FROM products ORDER BY id;