تصميم قاعدة بيانات وسائل التواصل الاجتماعي

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

-- جدول المستخدمين 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, full_name VARCHAR(100) NOT NULL, bio TEXT, avatar_url VARCHAR(500), cover_photo_url VARCHAR(500), website VARCHAR(255), location VARCHAR(100), is_verified BOOLEAN DEFAULT FALSE, is_private BOOLEAN DEFAULT FALSE, is_active BOOLEAN DEFAULT TRUE, date_of_birth DATE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, last_seen_at TIMESTAMP NULL, INDEX idx_username (username), INDEX idx_email (email), INDEX idx_verified (is_verified), FULLTEXT idx_search (username, full_name, bio) ) ENGINE=InnoDB; -- المتابعات/الاتصالات CREATE TABLE follows ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, follower_id BIGINT UNSIGNED NOT NULL COMMENT 'المستخدم الذي يتابع', following_id BIGINT UNSIGNED NOT NULL COMMENT 'المستخدم المتابَع', status ENUM('pending', 'accepted', 'blocked') DEFAULT 'accepted', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (follower_id) REFERENCES users(id) ON DELETE CASCADE, FOREIGN KEY (following_id) REFERENCES users(id) ON DELETE CASCADE, UNIQUE KEY unique_follow (follower_id, following_id), INDEX idx_follower (follower_id), INDEX idx_following (following_id), INDEX idx_status (status), CHECK (follower_id != following_id) ) ENGINE=InnoDB; -- المنشورات CREATE TABLE posts ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL, content TEXT NOT NULL, media_type ENUM('none', 'image', 'video', 'link') DEFAULT 'none', media_url VARCHAR(500), link_preview_title VARCHAR(255), link_preview_description TEXT, link_preview_image VARCHAR(500), is_public BOOLEAN DEFAULT TRUE, allow_comments BOOLEAN DEFAULT TRUE, location VARCHAR(255), 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_created (created_at), INDEX idx_public (is_public, created_at), FULLTEXT idx_content (content) ) ENGINE=InnoDB; -- وسائط المنشور (صور/فيديوهات متعددة لكل منشور) CREATE TABLE post_media ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, post_id BIGINT UNSIGNED NOT NULL, media_type ENUM('image', 'video') NOT NULL, media_url VARCHAR(500) NOT NULL, thumbnail_url VARCHAR(500), display_order INT DEFAULT 0, width INT UNSIGNED, height INT UNSIGNED, duration INT UNSIGNED COMMENT 'مدة الفيديو بالثواني', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE, INDEX idx_post (post_id) ) ENGINE=InnoDB; -- التعليقات CREATE TABLE comments ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, post_id BIGINT UNSIGNED NOT NULL, user_id BIGINT UNSIGNED NOT NULL, parent_comment_id BIGINT UNSIGNED NULL COMMENT 'للردود المتداخلة', content TEXT NOT NULL, 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 CASCADE, FOREIGN KEY (parent_comment_id) REFERENCES comments(id) ON DELETE CASCADE, INDEX idx_post (post_id, created_at), INDEX idx_user (user_id), INDEX idx_parent (parent_comment_id), FULLTEXT idx_content (content) ) ENGINE=InnoDB; -- الإعجابات (متعددة الأشكال - يمكن الإعجاب بالمنشورات أو التعليقات) CREATE TABLE likes ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL, likeable_type ENUM('post', 'comment') NOT NULL, likeable_id BIGINT UNSIGNED NOT NULL, reaction_type ENUM('like', 'love', 'haha', 'wow', 'sad', 'angry') DEFAULT 'like', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, UNIQUE KEY unique_like (user_id, likeable_type, likeable_id), INDEX idx_likeable (likeable_type, likeable_id), INDEX idx_user (user_id) ) ENGINE=InnoDB; -- المشاركات/إعادة التغريد CREATE TABLE shares ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL COMMENT 'المستخدم الذي شارك', original_post_id BIGINT UNSIGNED NOT NULL, caption TEXT COMMENT 'تعليق اختياري عند المشاركة', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, FOREIGN KEY (original_post_id) REFERENCES posts(id) ON DELETE CASCADE, INDEX idx_user (user_id), INDEX idx_post (original_post_id), INDEX idx_created (created_at) ) ENGINE=InnoDB; -- الهاشتاقات CREATE TABLE hashtags ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) UNIQUE NOT NULL, post_count INT UNSIGNED DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, INDEX idx_name (name), INDEX idx_trending (post_count, created_at) ) ENGINE=InnoDB; -- هاشتاقات المنشور (متعدد إلى متعدد) CREATE TABLE post_hashtags ( post_id BIGINT UNSIGNED NOT NULL, hashtag_id BIGINT UNSIGNED NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (post_id, hashtag_id), FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE, FOREIGN KEY (hashtag_id) REFERENCES hashtags(id) ON DELETE CASCADE, INDEX idx_hashtag (hashtag_id) ) ENGINE=InnoDB; -- المحادثات (للرسائل المباشرة) CREATE TABLE conversations ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, is_group BOOLEAN DEFAULT FALSE, group_name VARCHAR(100), created_by BIGINT UNSIGNED NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE RESTRICT, INDEX idx_group (is_group) ) ENGINE=InnoDB; -- مشاركو المحادثة CREATE TABLE conversation_participants ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, conversation_id BIGINT UNSIGNED NOT NULL, user_id BIGINT UNSIGNED NOT NULL, joined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, left_at TIMESTAMP NULL, last_read_at TIMESTAMP NULL, is_muted BOOLEAN DEFAULT FALSE, FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, UNIQUE KEY unique_participant (conversation_id, user_id), INDEX idx_user (user_id), INDEX idx_active (conversation_id, left_at) ) ENGINE=InnoDB; -- الرسائل CREATE TABLE messages ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, conversation_id BIGINT UNSIGNED NOT NULL, sender_id BIGINT UNSIGNED NOT NULL, content TEXT NOT NULL, media_url VARCHAR(500), media_type ENUM('none', 'image', 'video', 'file') DEFAULT 'none', is_deleted BOOLEAN DEFAULT FALSE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE, FOREIGN KEY (sender_id) REFERENCES users(id) ON DELETE RESTRICT, INDEX idx_conversation (conversation_id, created_at), INDEX idx_sender (sender_id) ) ENGINE=InnoDB; -- الإشعارات CREATE TABLE notifications ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL COMMENT 'المستلم', actor_id BIGINT UNSIGNED NOT NULL COMMENT 'المستخدم الذي بدأ', notification_type ENUM( 'follow', 'like', 'comment', 'share', 'mention', 'message', 'tag' ) NOT NULL, entity_type ENUM('post', 'comment', 'message') NULL, entity_id BIGINT UNSIGNED NULL, content TEXT, is_read BOOLEAN DEFAULT FALSE, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, FOREIGN KEY (actor_id) REFERENCES users(id) ON DELETE CASCADE, INDEX idx_user_unread (user_id, is_read, created_at), INDEX idx_actor (actor_id), INDEX idx_type (notification_type) ) ENGINE=InnoDB; -- حظر المستخدمين CREATE TABLE blocks ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, blocker_id BIGINT UNSIGNED NOT NULL, blocked_id BIGINT UNSIGNED NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (blocker_id) REFERENCES users(id) ON DELETE CASCADE, FOREIGN KEY (blocked_id) REFERENCES users(id) ON DELETE CASCADE, UNIQUE KEY unique_block (blocker_id, blocked_id), INDEX idx_blocker (blocker_id), INDEX idx_blocked (blocked_id) ) ENGINE=InnoDB; -- المنشورات المحفوظة (الإشارات المرجعية) CREATE TABLE saved_posts ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, user_id BIGINT UNSIGNED NOT NULL, post_id BIGINT UNSIGNED NOT NULL, collection_name VARCHAR(100) DEFAULT 'default', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE CASCADE, UNIQUE KEY unique_saved (user_id, post_id), INDEX idx_user (user_id, collection_name) ) ENGINE=InnoDB; -- التقارير (للإشراف على المحتوى) CREATE TABLE reports ( id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, reporter_id BIGINT UNSIGNED NOT NULL, reported_type ENUM('user', 'post', 'comment') NOT NULL, reported_id BIGINT UNSIGNED NOT NULL, reason ENUM( 'spam', 'harassment', 'hate_speech', 'violence', 'false_information', 'other' ) NOT NULL, description TEXT, status ENUM('pending', 'reviewing', 'resolved', 'dismissed') DEFAULT 'pending', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, reviewed_at TIMESTAMP NULL, FOREIGN KEY (reporter_id) REFERENCES users(id) ON DELETE CASCADE, INDEX idx_status (status), INDEX idx_reported (reported_type, reported_id) ) ENGINE=InnoDB;

-- الحصول على موجز المستخدم (منشورات من المستخدمين المتابعين) SELECT p.id, p.content, p.created_at, u.username, u.full_name, u.avatar_url, COUNT(DISTINCT l.id) AS like_count, COUNT(DISTINCT c.id) AS comment_count, COUNT(DISTINCT s.id) AS share_count, EXISTS( SELECT 1 FROM likes WHERE user_id = 1 AND likeable_type = 'post' AND likeable_id = p.id ) AS is_liked_by_me FROM posts p JOIN users u ON p.user_id = u.id JOIN follows f ON p.user_id = f.following_id LEFT JOIN likes l ON p.id = l.likeable_id AND l.likeable_type = 'post' LEFT JOIN comments c ON p.id = c.post_id LEFT JOIN shares s ON p.id = s.original_post_id WHERE f.follower_id = 1 AND f.status = 'accepted' AND p.is_public = TRUE GROUP BY p.id, p.content, p.created_at, u.username, u.full_name, u.avatar_url ORDER BY p.created_at DESC LIMIT 20; -- الحصول على عدد المتابعين والمتابعات SELECT (SELECT COUNT(*) FROM follows WHERE following_id = 1 AND status = 'accepted') AS followers, (SELECT COUNT(*) FROM follows WHERE follower_id = 1 AND status = 'accepted') AS following; -- الحصول على تفاصيل المنشور مع جميع التفاعلات SELECT p.*, u.username, u.full_name, u.avatar_url, COUNT(DISTINCT l.id) AS like_count, COUNT(DISTINCT c.id) AS comment_count, COUNT(DISTINCT s.id) AS share_count FROM posts p JOIN users u ON p.user_id = u.id LEFT JOIN likes l ON p.id = l.likeable_id AND l.likeable_type = 'post' LEFT JOIN comments c ON p.id = c.post_id LEFT JOIN shares s ON p.id = s.original_post_id WHERE p.id = 100 GROUP BY p.id; -- الحصول على التعليقات مع الردود المتداخلة WITH RECURSIVE comment_tree AS ( -- تعليقات المستوى الأعلى SELECT c.id, c.post_id, c.user_id, c.parent_comment_id, c.content, c.created_at, u.username, u.avatar_url, 0 AS depth, CAST(c.id AS CHAR(500)) AS path FROM comments c JOIN users u ON c.user_id = u.id WHERE c.post_id = 100 AND c.parent_comment_id IS NULL UNION ALL -- الردود المتداخلة SELECT c.id, c.post_id, c.user_id, c.parent_comment_id, c.content, c.created_at, u.username, u.avatar_url, ct.depth + 1, CONCAT(ct.path, '->', c.id) FROM comments c JOIN users u ON c.user_id = u.id JOIN comment_tree ct ON c.parent_comment_id = ct.id WHERE ct.depth < 5 ) SELECT * FROM comment_tree ORDER BY path; -- الحصول على الهاشتاقات الرائجة SELECT h.name, h.post_count, COUNT(DISTINCT ph.post_id) AS recent_posts FROM hashtags h LEFT JOIN post_hashtags ph ON h.id = ph.hashtag_id AND ph.created_at >= DATE_SUB(NOW(), INTERVAL 24 HOUR) GROUP BY h.id, h.name, h.post_count ORDER BY recent_posts DESC, h.post_count DESC LIMIT 10; -- الحصول على الإشعارات غير المقروءة SELECT n.id, n.notification_type, n.content, n.created_at, u.username AS actor_username, u.avatar_url AS actor_avatar FROM notifications n JOIN users u ON n.actor_id = u.id WHERE n.user_id = 1 AND n.is_read = FALSE ORDER BY n.created_at DESC LIMIT 50; -- الحصول على قائمة المحادثات مع آخر رسالة SELECT c.id, c.is_group, c.group_name, c.updated_at, m.content AS last_message, m.created_at AS last_message_at, sender.username AS last_sender, COUNT(DISTINCT CASE WHEN m2.created_at > cp.last_read_at THEN m2.id END) AS unread_count FROM conversations c JOIN conversation_participants cp ON c.id = cp.conversation_id LEFT JOIN messages m ON c.id = m.conversation_id AND m.id = ( SELECT id FROM messages WHERE conversation_id = c.id ORDER BY created_at DESC LIMIT 1 ) LEFT JOIN users sender ON m.sender_id = sender.id LEFT JOIN messages m2 ON c.id = m2.conversation_id WHERE cp.user_id = 1 AND cp.left_at IS NULL GROUP BY c.id, c.is_group, c.group_name, c.updated_at, m.content, m.created_at, sender.username ORDER BY c.updated_at DESC;

-- متابعة مستخدم (قبول تلقائي للحسابات العامة) INSERT INTO follows (follower_id, following_id, status) SELECT 1, 2, CASE WHEN is_private = TRUE THEN 'pending' ELSE 'accepted' END FROM users WHERE id = 2; -- قبول طلب المتابعة UPDATE follows SET status = 'accepted' WHERE follower_id = 1 AND following_id = 2 AND status = 'pending'; -- التحقق مما إذا كان المستخدم A يتابع المستخدم B SELECT EXISTS( SELECT 1 FROM follows WHERE follower_id = 1 AND following_id = 2 AND status = 'accepted' ) AS is_following; -- الحصول على المتابعين المتبادلين (الأصدقاء) SELECT DISTINCT u.* FROM users u JOIN follows f1 ON u.id = f1.following_id JOIN follows f2 ON u.id = f2.follower_id WHERE f1.follower_id = 1 AND f2.following_id = 1 AND f1.status = 'accepted' AND f2.status = 'accepted';

-- موجز النشاط المدمج (المنشورات + المشاركات) ( SELECT 'post' AS activity_type, p.id, p.user_id, u.username, p.content, p.created_at FROM posts p JOIN users u ON p.user_id = u.id JOIN follows f ON p.user_id = f.following_id WHERE f.follower_id = 1 AND f.status = 'accepted' ) UNION ALL ( SELECT 'share' AS activity_type, s.id, s.user_id, u.username, s.caption AS content, s.created_at FROM shares s JOIN users u ON s.user_id = u.id JOIN follows f ON s.user_id = f.following_id WHERE f.follower_id = 1 AND f.status = 'accepted' ) ORDER BY created_at DESC LIMIT 50;

-- إنشاء محادثة بين مستخدمين INSERT INTO conversations (is_group, created_by) VALUES (FALSE, 1); SET @conv_id = LAST_INSERT_ID(); INSERT INTO conversation_participants (conversation_id, user_id) VALUES (@conv_id, 1), (@conv_id, 2); -- إرسال رسالة INSERT INTO messages (conversation_id, sender_id, content) VALUES (@conv_id, 1, 'مرحباً، كيف حالك؟'); -- وضع علامة مقروءة على الرسائل UPDATE conversation_participants SET last_read_at = NOW() WHERE conversation_id = @conv_id AND user_id = 1; -- الحصول على الرسائل في محادثة SELECT m.id, m.content, m.created_at, u.username, u.avatar_url, m.sender_id = 1 AS is_mine FROM messages m JOIN users u ON m.sender_id = u.id WHERE m.conversation_id = @conv_id AND m.is_deleted = FALSE ORDER BY m.created_at ASC;

توليد الموجز: - استخدم نموذج الدفع للمستخدمين الذين لديهم أقل من 1000 متابع - استخدم نموذج السحب للمشاهير الذين لديهم ملايين المتابعين - نفذ التوزيع عند الكتابة للمستخدمين النشطين - خزن الموجزات مؤقتاً في Redis مع TTL تجزئة قاعدة البيانات: - قسم المستخدمين حسب نطاقات user_id - احتفظ بالرسم البياني الاجتماعي في مجموعة مخصصة - استخدم التجزئة المتسقة للرسائل - كرر للعمليات ذات القراءة الكثيفة تحسين الأداء: - ترقيم جميع الموجزات بترقيم قائم على المؤشر - غير طبيعية أعداد التفاعل (like_count، comment_count) - استخدم قوائم انتظار الرسائل للإشعارات - أرشف المنشورات القديمة بعد عام واحد - نفذ تحديد المعدل لكل مستخدم