تصميم المخططات والتسوية

يُعد تصميم مخطط قاعدة البيانات من أهم المهارات التي تُختبر في مقابلات الخدمات الخلفية. فالمخطط المصمم بشكل سيئ يؤدي إلى شذوذ في البيانات ومشاكل في الأداء وكوابيس في الصيانة. يغطي هذا الدرس أشكال التسوية ومقايضات إلغاء التسوية وعرضًا كاملاً لتصميم مخطط بأسلوب المقابلات.

أشكال التسوية: من 1NF إلى BCNF

التسوية تزيل تكرار البيانات عبر تنظيم الأعمدة والجداول. كل شكل تسوية يبني على الذي قبله.

الشكل العادي الأول (1NF)

يكون الجدول في 1NF عندما:

• كل عمود يحتوي على قيم ذرية (غير قابلة للتجزئة)
• لا توجد مجموعات متكررة أو مصفوفات في عمود واحد

مثال على الانتهاك -- تخزين أرقام هواتف متعددة في عمود واحد:

-- سيئ: ينتهك 1NF
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    phones VARCHAR(255)  -- "555-0101, 555-0102, 555-0103"
);

-- جيد: متوافق مع 1NF
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE customer_phones (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(id),
    phone VARCHAR(20) NOT NULL
);

الشكل العادي الثاني (2NF)

يكون الجدول في 2NF عندما يكون في 1NF و كل عمود غير مفتاحي يعتمد على المفتاح الأساسي بالكامل، وليس على جزء منه فقط. ينطبق هذا فقط على الجداول ذات المفاتيح الأساسية المركبة.

مثال على الانتهاك -- product_name يعتمد فقط على product_id، وليس على المفتاح المركب بالكامل:

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

-- جيد: متوافق مع 2NF — فصل الاعتماد الجزئي
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE order_items (
    order_id INTEGER REFERENCES orders(id),
    product_id INTEGER REFERENCES products(id),
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    PRIMARY KEY (order_id, product_id)
);

الشكل العادي الثالث (3NF)

يكون الجدول في 3NF عندما يكون في 2NF و لا يوجد عمود غير مفتاحي يعتمد على عمود غير مفتاحي آخر (لا توجد اعتماديات متعدية).

مثال على الانتهاك -- city يعتمد على zip_code، الذي يعتمد على المفتاح الأساسي:

-- سيئ: ينتهك 3NF (المدينة تعتمد على الرمز البريدي، وليس مباشرة على المعرف)
CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    zip_code VARCHAR(10),
    city VARCHAR(100)  -- تعتمد بشكل متعدٍّ على id عبر zip_code
);

-- جيد: متوافق مع 3NF
CREATE TABLE zip_codes (
    zip_code VARCHAR(10) PRIMARY KEY,
    city VARCHAR(100) NOT NULL
);

CREATE TABLE customers (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    zip_code VARCHAR(10) REFERENCES zip_codes(zip_code)
);

شكل بويس-كود العادي (BCNF)

BCNF يعزز 3NF: كل محدِّد يجب أن يكون مفتاحًا مرشحًا. عمليًا، معظم مخططات 3NF هي بالفعل BCNF. الاستثناء يظهر عندما يحتوي الجدول على مفاتيح مرشحة متداخلة متعددة.

قاعدة عملية للمقابلات: إذا استطعت تبرير 3NF، فهذا عادة كافٍ. اذكر BCNF إذا تعمّق المحاور أكثر.

إلغاء التسوية: متى ولماذا

المخططات المُسوّاة بالكامل تقلل التكرار لكنها تتطلب عمليات JOIN لمعظم الاستعلامات. إلغاء التسوية يُدخل التكرار عمدًا لتحسين أداء القراءة.

السيناريو	تسوية	إلغاء تسوية
نظام OLTP كثيف الكتابة	نعم	لا
لوحة معلومات/تحليلات كثيفة القراءة	جزئيًا	نعم
لوحة متصدرين فورية	لا	نعم
سجلات المعاملات المالية	نعم	لا
كتالوج منتجات مع بحث	جزئيًا	نعم (عروض مادية)

تقنيات إلغاء التسوية الشائعة:

1. تكرار الأعمدة -- تخزين customer_name مباشرة في جدول orders لتجنب عملية JOIN عند كل استعلام عن الطلبات
2. التجميعات المحسوبة مسبقًا -- تخزين total_reviews وavg_rating في جدول products
3. العروض المادية (Materialized Views) -- جداول للقراءة فقط يتم تحديثها دوريًا وتجمع عدة جداول مصدرية

نصيحة للمقابلة: ابدأ دائمًا بتصميم مُسوّى، ثم اشرح أي أنماط قراءة محددة تبرر إلغاء التسوية. يريد المحاورون أن يروا أنك تفهم المقايضة: قراءات أسرع على حساب كتابات أكثر تعقيدًا واحتمال عدم اتساق البيانات.

القيود: شبكة أمان مخططك

القيود تفرض سلامة البيانات على مستوى قاعدة البيانات، وهذا أكثر موثوقية من التحقق على مستوى التطبيق وحده.

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    sku VARCHAR(50) NOT NULL UNIQUE,
    price NUMERIC(10,2) NOT NULL CHECK (price >= 0),
    stock_quantity INTEGER NOT NULL DEFAULT 0 CHECK (stock_quantity >= 0),
    category_id INTEGER NOT NULL REFERENCES categories(id),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

القيد	الغرض	مثال
PRIMARY KEY	معرف فريد للصف	id SERIAL PRIMARY KEY
NOT NULL	العمود يجب أن يحتوي على قيمة	name VARCHAR(200) NOT NULL
UNIQUE	لا قيم مكررة	sku VARCHAR(50) UNIQUE
CHECK	قاعدة تحقق مخصصة	CHECK (price >= 0)
DEFAULT	قيمة افتراضية إذا لم تُقدم	DEFAULT 0
REFERENCES (FK)	يفرض التكامل المرجعي	REFERENCES categories(id)

تمرين المقابلة: مخطط وسائل التواصل الاجتماعي

سؤال مقابلة شائع: "صمم مخطط قاعدة بيانات لتطبيق وسائل تواصل اجتماعي."

إليك منهجية منظمة:

الخطوة 1: تحديد الكيانات الأساسية

المستخدمون، المنشورات، التعليقات، الإعجابات، المتابعات، الوسائط

الخطوة 2: تحديد العلاقات

• المستخدم لديه عدة منشورات (واحد-لعدة)
• المنشور لديه عدة تعليقات (واحد-لعدة)
• المستخدم يتابع عدة مستخدمين (عدة-لعدة، مرجعية ذاتية)
• المستخدم يعجب بعدة منشورات (عدة-لعدة)

الخطوة 3: كتابة المخطط

CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(30) NOT NULL UNIQUE,
    email VARCHAR(255) NOT NULL UNIQUE,
    display_name VARCHAR(100) NOT NULL,
    bio TEXT,
    avatar_url VARCHAR(500),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE posts (
    id BIGSERIAL PRIMARY KEY,
    author_id BIGINT NOT NULL REFERENCES users(id),
    content TEXT NOT NULL CHECK (char_length(content) <= 5000),
    media_url VARCHAR(500),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE comments (
    id BIGSERIAL PRIMARY KEY,
    post_id BIGINT NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    author_id BIGINT NOT NULL REFERENCES users(id),
    content TEXT NOT NULL CHECK (char_length(content) <= 2000),
    parent_comment_id BIGINT REFERENCES comments(id),  -- ردود متداخلة
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE likes (
    user_id BIGINT NOT NULL REFERENCES users(id),
    post_id BIGINT NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (user_id, post_id)  -- يمنع الإعجابات المكررة
);

CREATE TABLE follows (
    follower_id BIGINT NOT NULL REFERENCES users(id),
    following_id BIGINT NOT NULL REFERENCES users(id),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (follower_id, following_id),
    CHECK (follower_id != following_id)  -- لا يمكنك متابعة نفسك
);

-- عدادات غير مُسوّاة للقراءة السريعة
ALTER TABLE users ADD COLUMN follower_count INTEGER NOT NULL DEFAULT 0;
ALTER TABLE users ADD COLUMN following_count INTEGER NOT NULL DEFAULT 0;
ALTER TABLE posts ADD COLUMN like_count INTEGER NOT NULL DEFAULT 0;
ALTER TABLE posts ADD COLUMN comment_count INTEGER NOT NULL DEFAULT 0;

الخطوة 4: اشرح مقايضاتك

• ON DELETE CASCADE على التعليقات والإعجابات: عند حذف منشور، تُنظّف البيانات المرتبطة تلقائيًا
• المفتاح الأساسي المركب على likes وfollows: يمنع التكرار دون الحاجة لقيد UNIQUE منفصل
• parent_comment_id: يتيح الردود المتداخلة على التعليقات باستخدام مفتاح أجنبي مرجعي ذاتي
• العدادات غير المُسوّاة: like_count وfollower_count تتجنب استعلامات COUNT(*) المكلفة عند كل عرض لملف شخصي أو منشور، لكنها تتطلب تحديثًا دقيقًا (باستخدام المشغلات أو منطق التطبيق)

النقطة الأساسية: في المقابلات، اشرح تفكيرك بصوت عالٍ دائمًا. تنقّل بين الكيانات والعلاقات والقيود والمقايضات بطريقة منظمة. مخطط متوسط مع تفسير ممتاز غالبًا يحصل على درجات أعلى من مخطط مثالي بدون أي شرح.

التالي: استراتيجيات الفهرسة وتحسين الاستعلامات ومستويات عزل المعاملات. :::