تصميم قاعدة بيانات المدونة ونظام إدارة المحتوى

إدارة المحتوى: - منشورات بأنواع محتوى متعددة - فئات وتصنيفات هرمية - علامات وتصنيفات مخصصة - صور مميزة ومعارض وسائط - مسودات ومنشورات منشورة ومجدولة دعم اللغات المتعددة: - محتوى بلغات متعددة - عناوين URL خاصة باللغة - إدارة الترجمة أدوار المستخدمين والأذونات: - مؤلفون ومحررون ومسؤولون - سير عمل المحتوى (مسودة → مراجعة → نشر) - التحكم في الوصول القائم على الأذونات ميزات متقدمة: - مراجعات المنشورات وسجل الإصدارات - التعليقات والإشراف - بيانات SEO الوصفية - الحقول المخصصة/البيانات الوصفية

-- المستخدمون والأدوار CREATE TABLE users ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, username VARCHAR(50) UNIQUE NOT NULL, email VARCHAR(255) UNIQUE NOT NULL, password_hash VARCHAR(255) NOT NULL, display_name VARCHAR(100) NOT NULL, bio TEXT, avatar_url VARCHAR(500), role ENUM('subscriber', 'author', 'editor', 'admin') DEFAULT 'subscriber', is_active BOOLEAN DEFAULT TRUE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, INDEX idx_role (role), INDEX idx_email (email) ) ENGINE=InnoDB; -- اللغات CREATE TABLE languages ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, code VARCHAR(10) UNIQUE NOT NULL COMMENT 'مثل: en, ar, fr', name VARCHAR(50) NOT NULL, native_name VARCHAR(50) NOT NULL, is_rtl BOOLEAN DEFAULT FALSE, is_active BOOLEAN DEFAULT TRUE, display_order INT DEFAULT 0, INDEX idx_code (code) ) ENGINE=InnoDB; -- الفئات (هرمية) CREATE TABLE categories ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, parent_id INT UNSIGNED NULL, slug VARCHAR(200) NOT NULL, 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) ) ENGINE=InnoDB; -- ترجمات الفئات CREATE TABLE category_translations ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, category_id INT UNSIGNED NOT NULL, language_id INT UNSIGNED NOT NULL, name VARCHAR(200) NOT NULL, description TEXT, meta_title VARCHAR(255), meta_description VARCHAR(500), FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE, FOREIGN KEY (language_id) REFERENCES languages(id) ON DELETE CASCADE, UNIQUE KEY unique_translation (category_id, language_id), INDEX idx_category (category_id), INDEX idx_language (language_id) ) ENGINE=InnoDB; -- العلامات CREATE TABLE tags ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, slug VARCHAR(200) UNIQUE NOT NULL, post_count INT UNSIGNED DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX idx_slug (slug), INDEX idx_popular (post_count) ) ENGINE=InnoDB; -- ترجمات العلامات CREATE TABLE tag_translations ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, tag_id INT UNSIGNED NOT NULL, language_id INT UNSIGNED NOT NULL, name VARCHAR(100) NOT NULL, FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE, FOREIGN KEY (language_id) REFERENCES languages(id) ON DELETE CASCADE, UNIQUE KEY unique_translation (tag_id, language_id), INDEX idx_tag (tag_id) ) ENGINE=InnoDB; -- المنشورات CREATE TABLE posts ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, author_id BIGINT UNSIGNED NOT NULL, category_id INT UNSIGNED NULL, slug VARCHAR(255) NOT NULL, post_type ENUM('post', 'page', 'custom') DEFAULT 'post', status ENUM('draft', 'pending', 'published', 'scheduled', 'archived') DEFAULT 'draft', featured_image_url VARCHAR(500), comment_status ENUM('open', 'closed', 'registered_only') DEFAULT 'open', view_count INT UNSIGNED DEFAULT 0, is_featured BOOLEAN DEFAULT FALSE, published_at TIMESTAMP NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE RESTRICT, FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL, INDEX idx_author (author_id), INDEX idx_category (category_id), INDEX idx_slug (slug), INDEX idx_status_published (status, published_at), INDEX idx_featured (is_featured, published_at) ) ENGINE=InnoDB; -- ترجمات المنشورات (محتوى متعدد اللغات) CREATE TABLE post_translations ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, post_id BIGINT UNSIGNED NOT NULL, language_id INT UNSIGNED NOT NULL, title VARCHAR(255) NOT NULL, excerpt TEXT, content LONGTEXT NOT NULL, meta_title VARCHAR(255), meta_description VARCHAR(500), meta_keywords VARCHAR(500), FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE, FOREIGN KEY (language_id) REFERENCES languages(id) ON DELETE CASCADE, UNIQUE KEY unique_translation (post_id, language_id), INDEX idx_post (post_id), INDEX idx_language (language_id), FULLTEXT idx_search (title, content) ) ENGINE=InnoDB; -- علامات المنشورات (متعدد إلى متعدد) CREATE TABLE post_tags ( post_id BIGINT UNSIGNED NOT NULL, tag_id INT UNSIGNED NOT NULL, PRIMARY KEY (post_id, tag_id), FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE, FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE, INDEX idx_tag (tag_id) ) ENGINE=InnoDB; -- مراجعات المنشورات (سجل الإصدارات) CREATE TABLE post_revisions ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, post_id BIGINT UNSIGNED NOT NULL, language_id INT UNSIGNED NOT NULL, user_id BIGINT UNSIGNED NOT NULL, title VARCHAR(255) NOT NULL, content LONGTEXT NOT NULL, revision_note VARCHAR(500), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE, FOREIGN KEY (language_id) REFERENCES languages(id) ON DELETE CASCADE, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT, INDEX idx_post (post_id, created_at), INDEX idx_user (user_id) ) ENGINE=InnoDB; -- مكتبة الوسائط CREATE TABLE media ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL, file_name VARCHAR(255) NOT NULL, file_path VARCHAR(500) NOT NULL, file_type VARCHAR(50) NOT NULL COMMENT 'نوع MIME', file_size INT UNSIGNED NOT NULL COMMENT 'الحجم بالبايتات', width INT UNSIGNED COMMENT 'للصور', height INT UNSIGNED COMMENT 'للصور', alt_text VARCHAR(255), caption TEXT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT, INDEX idx_user (user_id), INDEX idx_type (file_type), INDEX idx_created (created_at) ) ENGINE=InnoDB; -- وسائط المنشور (علاقة المرفقات) CREATE TABLE post_media ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, post_id BIGINT UNSIGNED NOT NULL, media_id BIGINT UNSIGNED NOT NULL, display_order INT DEFAULT 0, FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE, FOREIGN KEY (media_id) REFERENCES media(id) ON DELETE CASCADE, INDEX idx_post (post_id), INDEX idx_media (media_id) ) ENGINE=InnoDB; -- التعليقات CREATE TABLE comments ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, post_id BIGINT UNSIGNED NOT NULL, user_id BIGINT UNSIGNED NULL COMMENT 'NULL للتعليقات من الضيوف', parent_comment_id BIGINT UNSIGNED NULL, author_name VARCHAR(100) NOT NULL, author_email VARCHAR(255) NOT NULL, author_url VARCHAR(255), author_ip VARCHAR(45), content TEXT NOT NULL, status ENUM('pending', 'approved', 'spam', 'trash') DEFAULT 'pending', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL, FOREIGN KEY (parent_comment_id) REFERENCES comments(id) ON DELETE CASCADE, INDEX idx_post_status (post_id, status), INDEX idx_user (user_id), INDEX idx_parent (parent_comment_id), INDEX idx_status (status) ) ENGINE=InnoDB; -- بيانات المنشور الوصفية (حقول مخصصة) CREATE TABLE post_meta ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, post_id BIGINT UNSIGNED NOT NULL, meta_key VARCHAR(255) NOT NULL, meta_value LONGTEXT, FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE, INDEX idx_post_key (post_id, meta_key), INDEX idx_key (meta_key) ) ENGINE=InnoDB; -- القوائم CREATE TABLE menus ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, location VARCHAR(50) NOT NULL COMMENT 'مثل: header, footer, sidebar', is_active BOOLEAN DEFAULT TRUE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX idx_location (location) ) ENGINE=InnoDB; -- عناصر القائمة CREATE TABLE menu_items ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, menu_id INT UNSIGNED NOT NULL, parent_id BIGINT UNSIGNED NULL, title VARCHAR(100) NOT NULL, url VARCHAR(500), target VARCHAR(20) DEFAULT '_self', css_class VARCHAR(100), display_order INT DEFAULT 0, FOREIGN KEY (menu_id) REFERENCES menus(id) ON DELETE CASCADE, FOREIGN KEY (parent_id) REFERENCES menu_items(id) ON DELETE CASCADE, INDEX idx_menu (menu_id), INDEX idx_parent (parent_id), INDEX idx_order (display_order) ) ENGINE=InnoDB; -- إعادات التوجيه (لتحسين محركات البحث) CREATE TABLE redirects ( id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, old_url VARCHAR(500) NOT NULL, new_url VARCHAR(500) NOT NULL, redirect_type ENUM('301', '302', '307') DEFAULT '301', hit_count INT UNSIGNED DEFAULT 0, is_active BOOLEAN DEFAULT TRUE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY unique_old_url (old_url), INDEX idx_active (is_active) ) ENGINE=InnoDB; -- بيانات SEO لكل منشور CREATE TABLE seo_data ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, post_id BIGINT UNSIGNED NOT NULL, language_id INT UNSIGNED NOT NULL, focus_keyword VARCHAR(255), canonical_url VARCHAR(500), noindex BOOLEAN DEFAULT FALSE, nofollow BOOLEAN DEFAULT FALSE, schema_type VARCHAR(50) COMMENT 'مثل: Article, NewsArticle, BlogPosting', schema_data JSON, FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE, FOREIGN KEY (language_id) REFERENCES languages(id) ON DELETE CASCADE, UNIQUE KEY unique_seo (post_id, language_id) ) ENGINE=InnoDB;

-- الحصول على المنشورات المنشورة مع الترجمات SELECT p.id, p.slug, p.published_at, p.view_count, pt.title, pt.excerpt, u.display_name AS author, c.slug AS category_slug, ct.name AS category_name FROM posts p JOIN post_translations pt ON p.id = pt.post_id JOIN users u ON p.author_id = u.id LEFT JOIN categories c ON p.category_id = c.id LEFT JOIN category_translations ct ON c.id = ct.category_id AND ct.language_id = pt.language_id WHERE p.status = 'published' AND pt.language_id = 1 AND p.published_at <= NOW() ORDER BY p.published_at DESC LIMIT 10; -- الحصول على منشور مع جميع التفاصيل (عرض منشور واحد) SELECT p.*, pt.title, pt.content, pt.meta_title, pt.meta_description, u.display_name AS author, u.avatar_url AS author_avatar, c.slug AS category_slug, ct.name AS category_name FROM posts p JOIN post_translations pt ON p.id = pt.post_id JOIN users u ON p.author_id = u.id LEFT JOIN categories c ON p.category_id = c.id LEFT JOIN category_translations ct ON c.id = ct.category_id AND ct.language_id = pt.language_id WHERE p.slug = 'my-post-slug' AND pt.language_id = 1 AND p.status = 'published'; -- الحصول على علامات المنشور SELECT t.id, t.slug, tt.name FROM tags t JOIN post_tags pt ON t.id = pt.tag_id JOIN tag_translations tt ON t.id = tt.tag_id WHERE pt.post_id = 100 AND tt.language_id = 1; -- البحث في المنشورات عبر جميع اللغات SELECT p.id, p.slug, pt.title, pt.excerpt, l.code AS language, MATCH(pt.title, pt.content) AGAINST('مصطلح البحث') AS relevance FROM posts p JOIN post_translations pt ON p.id = pt.post_id JOIN languages l ON pt.language_id = l.id WHERE p.status = 'published' AND MATCH(pt.title, pt.content) AGAINST('مصطلح البحث' IN NATURAL LANGUAGE MODE) ORDER BY relevance DESC LIMIT 20;

-- حفظ المراجعة قبل التحديث INSERT INTO post_revisions (post_id, language_id, user_id, title, content, revision_note) SELECT post_id, language_id, 1 AS user_id, title, content, 'قبل التعديل الكبير' FROM post_translations WHERE post_id = 100 AND language_id = 1; -- الحصول على سجل المراجعات SELECT pr.id, pr.revision_note, pr.created_at, u.display_name AS revised_by, LENGTH(pr.content) AS content_length FROM post_revisions pr JOIN users u ON pr.user_id = u.id WHERE pr.post_id = 100 AND pr.language_id = 1 ORDER BY pr.created_at DESC LIMIT 20; -- الاستعادة من مراجعة UPDATE post_translations pt JOIN post_revisions pr ON pt.post_id = pr.post_id AND pt.language_id = pr.language_id SET pt.title = pr.title, pt.content = pr.content WHERE pr.id = 500;

-- إضافة حقل مخصص INSERT INTO post_meta (post_id, meta_key, meta_value) VALUES (100, 'subtitle', 'عنوان فرعي مثير للاهتمام'); -- الحصول على جميع البيانات الوصفية لمنشور SELECT meta_key, meta_value FROM post_meta WHERE post_id = 100; -- تحديث قيمة البيانات الوصفية UPDATE post_meta SET meta_value = 'قيمة جديدة' WHERE post_id = 100 AND meta_key = 'subtitle';