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

إدارة المنتجات: - منتجات مع فئات متعددة - تنوعات المنتج (الحجم، اللون، إلخ) - تتبع المخزون - التسعير والخصومات معالجة الطلبات: - وظيفة عربة التسوق - تقديم الطلبات وتتبعها - إدارة حالة الطلب - معالجة الدفع إدارة العملاء: - حسابات وملفات المستخدمين - عناوين الشحن - سجل الطلبات - وظيفة قائمة الرغبات

-- جدول المستخدمين CREATE TABLE users ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, email VARCHAR(255) UNIQUE NOT NULL, password_hash VARCHAR(255) NOT NULL, first_name VARCHAR(100) NOT NULL, last_name VARCHAR(100) NOT NULL, phone VARCHAR(20), is_active BOOLEAN DEFAULT TRUE, email_verified_at TIMESTAMP NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_email (email), INDEX idx_active (is_active) ) ENGINE=InnoDB; -- جدول الفئات (هرمي) CREATE TABLE categories ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, parent_id INT UNSIGNED NULL, name VARCHAR(100) NOT NULL, slug VARCHAR(100) UNIQUE NOT NULL, description TEXT, image_url VARCHAR(500), display_order INT DEFAULT 0, is_active BOOLEAN DEFAULT TRUE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE SET NULL, INDEX idx_parent (parent_id), INDEX idx_slug (slug), INDEX idx_active (is_active) ) ENGINE=InnoDB; -- جدول المنتجات CREATE TABLE products ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, category_id INT UNSIGNED NOT NULL, name VARCHAR(255) NOT NULL, slug VARCHAR(255) UNIQUE NOT NULL, description TEXT, short_description VARCHAR(500), base_price DECIMAL(10, 2) NOT NULL, sku VARCHAR(100) UNIQUE NOT NULL, is_active BOOLEAN DEFAULT TRUE, is_featured BOOLEAN DEFAULT FALSE, weight DECIMAL(8, 2) COMMENT 'الوزن بالكيلوجرام', meta_title VARCHAR(255), meta_description VARCHAR(500), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE RESTRICT, INDEX idx_category (category_id), INDEX idx_slug (slug), INDEX idx_sku (sku), INDEX idx_active_featured (is_active, is_featured), FULLTEXT idx_search (name, description) ) ENGINE=InnoDB; -- صور المنتجات CREATE TABLE product_images ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, product_id BIGINT UNSIGNED NOT NULL, image_url VARCHAR(500) NOT NULL, display_order INT DEFAULT 0, is_primary BOOLEAN DEFAULT FALSE, alt_text VARCHAR(255), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE, INDEX idx_product (product_id), INDEX idx_primary (product_id, is_primary) ) ENGINE=InnoDB; -- تنوعات المنتج (الحجم، اللون، إلخ) CREATE TABLE product_variations ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, product_id BIGINT UNSIGNED NOT NULL, sku VARCHAR(100) UNIQUE NOT NULL, variation_name VARCHAR(100) NOT NULL COMMENT 'مثل: "كبير / أحمر"', price DECIMAL(10, 2) NOT NULL, stock_quantity INT UNSIGNED DEFAULT 0, is_active BOOLEAN DEFAULT TRUE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE, INDEX idx_product (product_id), INDEX idx_sku (sku), INDEX idx_active (is_active) ) ENGINE=InnoDB; -- تتبع المخزون CREATE TABLE inventory_transactions ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, product_variation_id BIGINT UNSIGNED NOT NULL, transaction_type ENUM('purchase', 'sale', 'adjustment', 'return') NOT NULL, quantity INT NOT NULL COMMENT 'إيجابي للزيادة، سلبي للنقصان', reference_id BIGINT UNSIGNED COMMENT 'معرف الطلب أو الشراء', notes TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (product_variation_id) REFERENCES product_variations(id) ON DELETE RESTRICT, INDEX idx_variation (product_variation_id), INDEX idx_type (transaction_type), INDEX idx_created (created_at) ) ENGINE=InnoDB; -- العناوين CREATE TABLE addresses ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL, address_type ENUM('shipping', 'billing') NOT NULL, full_name VARCHAR(200) NOT NULL, phone VARCHAR(20) NOT NULL, address_line1 VARCHAR(255) NOT NULL, address_line2 VARCHAR(255), city VARCHAR(100) NOT NULL, state VARCHAR(100), postal_code VARCHAR(20) NOT NULL, country VARCHAR(100) NOT NULL, is_default BOOLEAN DEFAULT FALSE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, INDEX idx_user (user_id), INDEX idx_type (address_type), INDEX idx_default (user_id, is_default) ) ENGINE=InnoDB; -- عربة التسوق CREATE TABLE cart_items ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL, product_variation_id BIGINT UNSIGNED NOT NULL, quantity INT UNSIGNED NOT NULL DEFAULT 1, added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, FOREIGN KEY (product_variation_id) REFERENCES product_variations(id) ON DELETE CASCADE, UNIQUE KEY unique_cart_item (user_id, product_variation_id), INDEX idx_user (user_id) ) ENGINE=InnoDB; -- الطلبات CREATE TABLE orders ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL, order_number VARCHAR(50) UNIQUE NOT NULL, status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled', 'refunded') DEFAULT 'pending', subtotal DECIMAL(10, 2) NOT NULL, tax_amount DECIMAL(10, 2) DEFAULT 0, shipping_amount DECIMAL(10, 2) DEFAULT 0, discount_amount DECIMAL(10, 2) DEFAULT 0, total_amount DECIMAL(10, 2) NOT NULL, shipping_address_id BIGINT UNSIGNED NOT NULL, billing_address_id BIGINT UNSIGNED NOT NULL, payment_method VARCHAR(50) NOT NULL, payment_status ENUM('pending', 'paid', 'failed', 'refunded') DEFAULT 'pending', notes TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT, FOREIGN KEY (shipping_address_id) REFERENCES addresses(id) ON DELETE RESTRICT, FOREIGN KEY (billing_address_id) REFERENCES addresses(id) ON DELETE RESTRICT, INDEX idx_user (user_id), INDEX idx_order_number (order_number), INDEX idx_status (status), INDEX idx_payment_status (payment_status), INDEX idx_created (created_at) ) ENGINE=InnoDB; -- عناصر الطلب CREATE TABLE order_items ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, order_id BIGINT UNSIGNED NOT NULL, product_id BIGINT UNSIGNED NOT NULL, product_variation_id BIGINT UNSIGNED NOT NULL, product_name VARCHAR(255) NOT NULL COMMENT 'لقطة في وقت الطلب', variation_name VARCHAR(100), sku VARCHAR(100) NOT NULL, quantity INT UNSIGNED NOT NULL, unit_price DECIMAL(10, 2) NOT NULL, total_price DECIMAL(10, 2) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE RESTRICT, FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT, FOREIGN KEY (product_variation_id) REFERENCES product_variations(id) ON DELETE RESTRICT, INDEX idx_order (order_id), INDEX idx_product (product_id) ) ENGINE=InnoDB; -- معاملات الدفع CREATE TABLE payment_transactions ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, order_id BIGINT UNSIGNED NOT NULL, transaction_id VARCHAR(255) UNIQUE NOT NULL, payment_method VARCHAR(50) NOT NULL, amount DECIMAL(10, 2) NOT NULL, currency VARCHAR(3) DEFAULT 'USD', status ENUM('pending', 'completed', 'failed', 'refunded') NOT NULL, gateway_response TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE RESTRICT, INDEX idx_order (order_id), INDEX idx_transaction (transaction_id), INDEX idx_status (status) ) ENGINE=InnoDB; -- الكوبونات/الخصومات CREATE TABLE coupons ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, code VARCHAR(50) UNIQUE NOT NULL, discount_type ENUM('percentage', 'fixed') NOT NULL, discount_value DECIMAL(10, 2) NOT NULL, min_purchase_amount DECIMAL(10, 2) DEFAULT 0, max_discount_amount DECIMAL(10, 2), usage_limit INT UNSIGNED, times_used INT UNSIGNED DEFAULT 0, valid_from TIMESTAMP NOT NULL, valid_until TIMESTAMP NOT NULL, is_active BOOLEAN DEFAULT TRUE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX idx_code (code), INDEX idx_active (is_active), INDEX idx_valid (valid_from, valid_until) ) ENGINE=InnoDB; -- التقييمات CREATE TABLE product_reviews ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, product_id BIGINT UNSIGNED NOT NULL, user_id BIGINT UNSIGNED NOT NULL, order_id BIGINT UNSIGNED NOT NULL COMMENT 'يجب أن يكون قد اشترى', rating TINYINT UNSIGNED NOT NULL CHECK (rating BETWEEN 1 AND 5), title VARCHAR(255), comment TEXT, is_verified BOOLEAN DEFAULT TRUE, is_approved BOOLEAN DEFAULT FALSE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE CASCADE, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE RESTRICT, UNIQUE KEY unique_review (product_id, user_id, order_id), INDEX idx_product (product_id), INDEX idx_user (user_id), INDEX idx_approved (is_approved) ) ENGINE=InnoDB;

علاقات واحد إلى متعدد: - categories → categories (هرمي) - categories → products - products → product_images - products → product_variations - product_variations → inventory_transactions - users → addresses - users → cart_items - users → orders - orders → order_items - orders → payment_transactions علاقات متعدد إلى متعدد: - products ↔ orders (من خلال order_items) - users ↔ products (من خلال cart_items) - users ↔ products (من خلال product_reviews)

-- الحصول على المنتجات مع المخزون والصور SELECT p.id, p.name, p.base_price, c.name AS category, pi.image_url, COALESCE(SUM(pv.stock_quantity), 0) AS total_stock FROM products p LEFT JOIN categories c ON p.category_id = c.id LEFT JOIN product_images pi ON p.id = pi.product_id AND pi.is_primary = TRUE LEFT JOIN product_variations pv ON p.id = pv.product_id WHERE p.is_active = TRUE GROUP BY p.id, p.name, p.base_price, c.name, pi.image_url HAVING total_stock > 0 ORDER BY p.created_at DESC; -- الحصول على عربة المستخدم مع تفاصيل المنتج SELECT ci.id, ci.quantity, p.name AS product_name, pv.variation_name, pv.price, (ci.quantity * pv.price) AS subtotal, pi.image_url, pv.stock_quantity FROM cart_items ci JOIN product_variations pv ON ci.product_variation_id = pv.id JOIN products p ON pv.product_id = p.id LEFT JOIN product_images pi ON p.id = pi.product_id AND pi.is_primary = TRUE WHERE ci.user_id = 1 ORDER BY ci.added_at DESC; -- الحصول على تفاصيل الطلب مع العناصر SELECT o.order_number, o.status, o.total_amount, o.created_at, oi.product_name, oi.variation_name, oi.quantity, oi.unit_price, oi.total_price FROM orders o JOIN order_items oi ON o.id = oi.order_id WHERE o.user_id = 1 ORDER BY o.created_at DESC, oi.id; -- حساب متوسط تقييم المنتج SELECT p.id, p.name, COUNT(pr.id) AS review_count, ROUND(AVG(pr.rating), 2) AS avg_rating FROM products p LEFT JOIN product_reviews pr ON p.id = pr.product_id AND pr.is_approved = TRUE GROUP BY p.id, p.name HAVING review_count > 0 ORDER BY avg_rating DESC; -- المنتجات الأكثر مبيعاً SELECT p.id, p.name, SUM(oi.quantity) AS total_sold, SUM(oi.total_price) AS total_revenue FROM products p JOIN order_items oi ON p.id = oi.product_id JOIN orders o ON oi.order_id = o.id WHERE o.status IN ('delivered', 'shipped') AND o.created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY) GROUP BY p.id, p.name ORDER BY total_sold DESC LIMIT 10;

-- الحصول على جميع التنوعات لمنتج SELECT p.name AS product_name, pv.variation_name, pv.sku, pv.price, pv.stock_quantity, pv.is_active FROM product_variations pv JOIN products p ON pv.product_id = p.id WHERE p.id = 100 ORDER BY pv.price; -- التحقق من المخزون قبل الإضافة إلى العربة SELECT pv.id, pv.stock_quantity, COALESCE(ci.quantity, 0) AS cart_quantity, (pv.stock_quantity - COALESCE(ci.quantity, 0)) AS available FROM product_variations pv LEFT JOIN cart_items ci ON pv.id = ci.product_variation_id AND ci.user_id = 1 WHERE pv.id = 500;

-- تسجيل معاملة مخزون (بيع) INSERT INTO inventory_transactions (product_variation_id, transaction_type, quantity, reference_id, notes) VALUES (500, 'sale', -2, 12345, 'Order #ORD-2024-001'); -- تحديث كمية المخزون UPDATE product_variations SET stock_quantity = stock_quantity - 2 WHERE id = 500; -- الحصول على سجل المخزون SELECT it.id, pv.sku, it.transaction_type, it.quantity, it.reference_id, it.created_at FROM inventory_transactions it JOIN product_variations pv ON it.product_variation_id = pv.id WHERE pv.product_id = 100 ORDER BY it.created_at DESC LIMIT 50;

-- بدء المعاملة START TRANSACTION; -- إنشاء الطلب INSERT INTO orders (user_id, order_number, subtotal, tax_amount, shipping_amount, total_amount, shipping_address_id, billing_address_id, payment_method) VALUES (1, 'ORD-2024-001', 150.00, 15.00, 10.00, 175.00, 5, 5, 'credit_card'); SET @order_id = LAST_INSERT_ID(); -- إدراج عناصر الطلب من العربة INSERT INTO order_items (order_id, product_id, product_variation_id, product_name, variation_name, sku, quantity, unit_price, total_price) SELECT @order_id, p.id, ci.product_variation_id, p.name, pv.variation_name, pv.sku, ci.quantity, pv.price, (ci.quantity * pv.price) FROM cart_items ci JOIN product_variations pv ON ci.product_variation_id = pv.id JOIN products p ON pv.product_id = p.id WHERE ci.user_id = 1; -- تحديث المخزون UPDATE product_variations pv JOIN cart_items ci ON pv.id = ci.product_variation_id SET pv.stock_quantity = pv.stock_quantity - ci.quantity WHERE ci.user_id = 1; -- تسجيل معاملات المخزون INSERT INTO inventory_transactions (product_variation_id, transaction_type, quantity, reference_id) SELECT ci.product_variation_id, 'sale', -ci.quantity, @order_id FROM cart_items ci WHERE ci.user_id = 1; -- مسح العربة DELETE FROM cart_items WHERE user_id = 1; -- تأكيد المعاملة COMMIT;

تحسينات الأداء: - تقسيم جدول الطلبات حسب التاريخ (شهري/سنوي) - أرشفة الطلبات القديمة إلى جداول منفصلة - استخدام نسخ القراءة لتصفح المنتجات - تخزين استعلامات المنتجات الشائعة مؤقتاً - تنفيذ الترقيم لجميع القوائم - استخدام الفهارس الشاملة للاستعلامات الشائعة إدارة نمو البيانات: - أرشفة الطلبات المكتملة بعد عامين - نقل معاملات المخزون القديمة إلى الأرشيف - تنفيذ الحذف الناعم لسجل التدقيق - تنظيف دوري لعربات التسوق المهجورة - تحسين تخزين الصور باستخدام CDN