SQL: تصميم قواعد البيانات

[كيان] —— سمة1، سمة2، سمة3
   |
  (علاقة)  التعددية: 1:1, 1:N, M:N
   |
[كيان] —— سمة1، سمة2

-- ❌ ينتهك 1NF: عمود الهاتف يخزن قيماً متعددة
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    phones VARCHAR(200)  -- '138xxx,139xxx,137xxx'
);

-- ✅ يتوافق مع 1NF: كل حقل يخزن قيمة ذرية واحدة
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);
CREATE TABLE user_phones (
    id INT PRIMARY KEY,
    user_id INT,
    phone VARCHAR(20)
);

-- ❌ ينتهك 2NF: في عناصر الطلب، يعتمد product_name فقط على product_id وليس على order_id
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    product_name VARCHAR(100),  -- اعتماد جزئي
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

-- ✅ يتوافق مع 2NF: تقسيم إلى جدول المنتجات
CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);
CREATE TABLE order_items (
    order_id INT,
    product_id INT,
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

-- ❌ ينتهك 3NF: يعتمد city_name على city_id، ويعتمد city_id على id → اعتماد عابر
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    city_id INT,
    city_name VARCHAR(50)  -- اعتماد عابر
);

-- ✅ يتوافق مع 3NF: تقسيم إلى جدول المدن
CREATE TABLE cities (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    city_id INT
);

-- تخزين عدد التعليقات بشكل متكرر في جدول المقالات لتجنب COUNT في كل مرة
ALTER TABLE articles ADD COLUMN comment_count INT DEFAULT 0;

-- تحديث الحقل المتكرر
UPDATE articles SET comment_count = (
    SELECT COUNT(*) FROM comments WHERE comments.article_id = articles.id
);

-- مفتاح أساسي بتزايد تلقائي
CREATE TABLE users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50)
);

-- مفتاح أساسي UUID
CREATE TABLE users (
    id CHAR(36) PRIMARY KEY DEFAULT (UUID()),
    name VARCHAR(50)
);

-- مفتاح أساسي مركب (جدول وسيط)
CREATE TABLE article_tag (
    article_id BIGINT,
    tag_id BIGINT,
    PRIMARY KEY (article_id, tag_id)
);

-- القالب الأساسي لإنشاء جدول
CREATE TABLE table_name (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT 'المفتاح الأساسي',
    -- حقول العمل
    name VARCHAR(100) NOT NULL COMMENT 'الاسم',
    status TINYINT NOT NULL DEFAULT 1 COMMENT 'الحالة: 1-نشط 0-معطل',
    -- حقل المفتاح الخارجي
    user_id BIGINT NOT NULL COMMENT 'معرّف المستخدمين',
    -- حقول الطوابع الزمنية
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'وقت الإنشاء',
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'وقت التحديث',
    -- الفهارس
    INDEX idx_user_id (user_id),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='تعليق الجدول';

-- جدول الطلاب
CREATE TABLE students (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT 'معرّف الطالب',
    student_no VARCHAR(20) NOT NULL UNIQUE COMMENT 'رقم الطالب',
    name VARCHAR(50) NOT NULL COMMENT 'الاسم',
    gender TINYINT NOT NULL DEFAULT 0 COMMENT 'الجنس: 0-غير معروف 1-ذكر 2-أنثى',
    enrollment_year INT NOT NULL COMMENT 'سنة القبول',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='جدول الطلاب';

-- جدول المقررات
CREATE TABLE courses (
    id BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT 'معرّف المقرر',
    course_no VARCHAR(20) NOT NULL UNIQUE COMMENT 'رقم المقرر',
    name VARCHAR(100) NOT NULL COMMENT 'اسم المقرر',
    credit DECIMAL(3,1) NOT NULL COMMENT 'الساعات المعتمدة',
    teacher VARCHAR(50) COMMENT 'المحاضر',
    max_students INT NOT NULL DEFAULT 60 COMMENT 'الحد الأقصى للتسجيل',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='جدول المقررات';

-- جدول التسجيلات (جدول وسيط، علاقة متعدد إلى متعدد)
CREATE TABLE enrollments (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    student_id BIGINT NOT NULL COMMENT 'معرّف الطالب',
    course_id BIGINT NOT NULL COMMENT 'معرّف المقرر',
    score DECIMAL(5,2) COMMENT 'الدرجة',
    enrolled_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'وقت التسجيل',
    UNIQUE KEY uk_student_course (student_id, course_id),
    INDEX idx_course_id (course_id),
    INDEX idx_student_id (student_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='جدول التسجيلات';

-- جدول فئات المنتجات
CREATE TABLE categories (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL COMMENT 'اسم الفئة',
    parent_id BIGINT DEFAULT NULL COMMENT 'معرّف الفئة الأب، NULL للفئات الرئيسية',
    sort_order INT NOT NULL DEFAULT 0 COMMENT 'ترتيب الفرز',
    INDEX idx_parent_id (parent_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='جدول فئات المنتجات';

-- جدول المنتجات
CREATE TABLE products (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    category_id BIGINT NOT NULL COMMENT 'معرّف الفئة',
    name VARCHAR(200) NOT NULL COMMENT 'اسم المنتج',
    price DECIMAL(10,2) NOT NULL COMMENT 'سعر البيع',
    stock INT NOT NULL DEFAULT 0 COMMENT 'المخزون',
    status TINYINT NOT NULL DEFAULT 1 COMMENT 'الحالة: 1-معروض 0-غير معروض',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_category (category_id),
    INDEX idx_status_price (status, price)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='جدول المنتجات';

-- جدول الطلبات (إزالة التطبيع: تخزين معاينة عنوان الشحن بشكل متكرر)
CREATE TABLE orders (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    order_no VARCHAR(32) NOT NULL UNIQUE COMMENT 'رقم الطلب',
    user_id BIGINT NOT NULL COMMENT 'معرّف المستخدمين',
    total_amount DECIMAL(12,2) NOT NULL COMMENT 'إجمالي مبلغ الطلب',
    status TINYINT NOT NULL DEFAULT 0 COMMENT 'الحالة: 0-بانتظار الدفع 1-مدفوع 2-مشحون 3-مكتمل 4-ملغى',
    receiver_name VARCHAR(50) NOT NULL COMMENT 'اسم المستلم',
    receiver_phone VARCHAR(20) NOT NULL COMMENT 'هاتف المستلم',
    receiver_address VARCHAR(500) NOT NULL COMMENT 'عنوان الشحن',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    paid_at DATETIME COMMENT 'وقت الدفع',
    INDEX idx_user_id (user_id),
    INDEX idx_status (status),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='جدول الطلبات';

-- جدول عناصر الطلب
CREATE TABLE order_items (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    order_id BIGINT NOT NULL COMMENT 'معرّف الطلب',
    product_id BIGINT NOT NULL COMMENT 'معرّف المنتج',
    product_name VARCHAR(200) NOT NULL COMMENT 'اسم المنتج (معاينة)',
    product_price DECIMAL(10,2) NOT NULL COMMENT 'سعر الوحدة (معاينة)',
    quantity INT NOT NULL COMMENT 'الكمية',
    subtotal DECIMAL(12,2) NOT NULL COMMENT 'المجموع الفرعي',
    INDEX idx_order_id (order_id),
    INDEX idx_product_id (product_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='جدول عناصر الطلب';

-- جدول المستخدمينين
CREATE TABLE blog_users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    avatar_url VARCHAR(500),
    bio TEXT,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='جدول مستخدمي المدونة';

-- جدول المقالات
CREATE TABLE blog_articles (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    category_id BIGINT,
    title VARCHAR(200) NOT NULL,
    content TEXT NOT NULL,
    status TINYINT NOT NULL DEFAULT 0 COMMENT '0-مسودة 1-منشور 2-غير مدرج',
    view_count INT NOT NULL DEFAULT 0,
    like_count INT NOT NULL DEFAULT 0,
    published_at DATETIME,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_user_id (user_id),
    INDEX idx_category (category_id),
    INDEX idx_status_published (status, published_at DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='جدول المقالات';

-- جدول الوسوم
CREATE TABLE blog_tags (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL UNIQUE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='جدول الوسوم';

-- جدول الربط بين المقالات والوسوم
CREATE TABLE blog_article_tag (
    article_id BIGINT NOT NULL,
    tag_id BIGINT NOT NULL,
    PRIMARY KEY (article_id, tag_id),
    INDEX idx_tag_id (tag_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='جدول ربط المقالات بالوسوم';

-- جدول التعليقات (يدعم التعليقات المتداخلة)
CREATE TABLE blog_comments (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    article_id BIGINT NOT NULL,
    user_id BIGINT NOT NULL,
    parent_id BIGINT COMMENT 'معرّف التعليق الأب، NULL للتعليقات الرئيسية',
    content TEXT NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_article_id (article_id),
    INDEX idx_parent_id (parent_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='جدول التعليقات';

-- جدول المستأجرين
CREATE TABLE tenants (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL COMMENT 'اسم الشركة',
    plan VARCHAR(20) NOT NULL DEFAULT 'free' COMMENT 'الخطة: free/basic/pro',
    max_users INT NOT NULL DEFAULT 10,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='جدول المستأجرين';

-- جدول المستخدمينين (كل سجل ينتمي إلى مستأجر واحد)
CREATE TABLE tenant_users (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL COMMENT 'معرّف المستأجر',
    username VARCHAR(50) NOT NULL,
    role VARCHAR(20) NOT NULL DEFAULT 'member',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_tenant_username (tenant_id, username),
    INDEX idx_tenant_id (tenant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='جدول مستخدمي المستأجرين';

-- جميع جداول الأعمال تتضمن حقل tenant_id
CREATE TABLE tenant_orders (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL,
    user_id BIGINT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_tenant_id (tenant_id),
    INDEX idx_tenant_user (tenant_id, user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='جدول طلبات المستأجرين';