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

طريقان إلى نفس الفكرة

الأساسيات المطلوبة

ثلاثة أشياء يجب أن تكون ثابتة قبل أن تصمّم:

المفاهيم الأساسية

التصميم الجيد يجعل الخطأ صعب الحدوث: كل معلومة تُخزَّن في مكان واحد فقط، والقواعد مكتوبة كقيود، فلا يستطيع أحد أن يُدخل بيانات متناقضة حتى لو أراد. والتطبيع هو الأسلوب المنهجي لبلوغ ذلك.

1الكيان والخاصية والعلاقة

2المفاتيح

3لماذا نطبّع؟ الشذوذ (Anomalies)

جدول واحد ضخم يخزّن كل شيء (كل سطر فيه الزبون والمشروب والباريستا) يكرّر المعلومة، والتكرار يولّد ثلاث مشكلات:

4الاعتماد الوظيفي (Functional Dependency)

نقول إن A → B (A يحدّد B) إذا كانت قيمة A تعيّن قيمة B بلا لبس: drink_id → drink_name, price‏. هذه الفكرة هي مفتاح التطبيع كله: اسأل في كل عمود: ما الذي يحدّده؟ فإن حدّدته قيمة ليست مفتاح الجدول كاملًا فالعمود في المكان الخطأ.

5الصيغ الطبيعية

6قرارات تصميم صغيرة لكنها مهمة

كيف نصمّم خطوة بخطوة؟

1. صِف المجال بجمل: «الزبون يقدّم طلبات، والطلب يحوي مشروبات بكميات، والباريستا يحضّر الطلب».
2. استخرج الكيانات والخصائص: الأسماء تصبح جداول، والصفات تصبح أعمدة.
3. اختر مفتاحًا لكل كيان (غالبًا id اصطناعي) وحدّد المفاتيح المرشحة التي تحميها بـ UNIQUE‏.
4. حدّد العلاقات والأعداد وضع المفتاح الأجنبي في جانب «المتعدد»، وجدولًا وسيطًا للمتعدد-لمتعدد.
5. طبّع: اكتب الاعتمادات الوظيفية وافحص 1NF ثم 2NF ثم 3NF.
6. أضف القيود: NOT NULL و CHECK و DEFAULT‏، وقرّر إجراء ON DELETE لكل مفتاح أجنبي.
7. اختبر بالبيانات: ضع عينة صغيرة واسأل أسئلة حقيقية؛ إن بدا استعلام طبيعي عسيرًا فراجع التصميم.

الرسم 26: مسار التطبيع: كل خطوة تزيل نوعًا محددًا من التكرار.

الرسم 27: المخطط النهائي لمقهانا بعد التطبيع: خمسة جداول، لكل معلومة مكان واحد.

الرسم 28: اختيار إجراء ON DELETE هو سؤال عن معنى العلاقة لا عن الصياغة.

مثال تطبيقي: من سجل مبيعات واحد إلى مخطط سليم

تخيّل أن أحدهم سجّل مبيعات المقهى في جدول واحد، لكل سطر فيه كل شيء:

الخطوة 1: الجدول الأول (غير المطبّع)

CREATE TABLE sales_log (
    order_id            INTEGER,
    drink_id            INTEGER,
    drink_name          TEXT,
    drink_category      TEXT,
    drink_price_halalas INTEGER,
    customer_phone      TEXT,
    customer_name       TEXT,
    customer_city       TEXT,
    barista_id          INTEGER,
    barista_name        TEXT,
    qty                 INTEGER,
    order_date          TEXT,
    PRIMARY KEY (order_id, drink_id)
);

(عرضنا سبعة أعمدة فقط للاختصار؛ الجدول فيه اثنا عشر عمودًا بما فيها رقم هاتف الزبون ورقم الباريستا وسعر المشروب.)

هل هو في 1NF؟ نعم: كل خلية قيمة واحدة والمفتاح (order_id, drink_id) يميّز كل صف. مثال على ما لا يكون في 1NF:

الخطوة 2: اكتشف الاعتمادات الوظيفية

الخطوة 3: شاهد الشذوذ بنفسك

التعديل: انتقلت نورة إلى جدة، وعدّلنا صفًّا واحدًا فقط ونسينا البقية:

UPDATE sales_log SET customer_city = 'Jeddah'
WHERE order_id = 101 AND drink_id = 1;   -- 1 row changed

SELECT DISTINCT customer_phone, customer_name, customer_city
FROM sales_log ORDER BY 1, 3;

نورة الآن في مدينتين! والتعديل الصحيح كان يحتاج WHERE customer_phone = … ليصيب ثلاثة صفوف. الحذف: إن حذفنا الطلب 103 فقد اختفى «سبانش لاتيه» وسلطان من القاعدة تمامًا (عدّ الصفوف التي تذكرهما فأعطى 0). الإدراج: لا مكان لمشروب جديد لم يُبَع بعد.

الخطوة 4: التفكيك إلى 3NF

كل مجموعة معتمدة على محدّدها تنتقل إلى جدول مفتاحه ذلك المحدّد. 2NF: ننقل أعمدة drink_id إلى drinks وأعمدة order_id إلى orders‏. 3NF: ننقل أعمدة customer_phone إلى customers وأعمدة barista_id إلى baristas‏. الناتج:

CREATE TABLE customers (
    id    INTEGER PRIMARY KEY,
    phone TEXT NOT NULL UNIQUE,
    name  TEXT NOT NULL,
    city  TEXT NOT NULL
);
CREATE TABLE baristas (
    id   INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);
CREATE TABLE drinks (
    id            INTEGER PRIMARY KEY,
    name          TEXT NOT NULL UNIQUE,
    category      TEXT NOT NULL CHECK (category IN ('hot', 'cold')),
    price_halalas INTEGER NOT NULL CHECK (price_halalas > 0)
);
CREATE TABLE orders (
    id          INTEGER PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id) ON DELETE SET NULL,
    barista_id  INTEGER NOT NULL REFERENCES baristas(id),
    order_date  TEXT NOT NULL
);
CREATE TABLE order_items (
    order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    drink_id INTEGER NOT NULL REFERENCES drinks(id) ON DELETE RESTRICT,
    qty      INTEGER NOT NULL CHECK (qty > 0),
    PRIMARY KEY (order_id, drink_id)
);

لاحظ القرارات: phone مفتاح مرشح محميّ بـ UNIQUE بينما id هو الأساسي؛ CHECK يحمي الفئة والسعر والكمية؛ وإجراءات ON DELETE الثلاثة مختلفة عن قصد (الرسم 28).

الخطوة 5: انقل البيانات القديمة

INSERT INTO customers (phone, name, city)
SELECT DISTINCT customer_phone, customer_name, customer_city FROM sales_log;

INSERT INTO baristas (id, name)
SELECT DISTINCT barista_id, barista_name FROM sales_log;

INSERT INTO drinks (id, name, category, price_halalas)
SELECT DISTINCT drink_id, drink_name, drink_category, drink_price_halalas FROM sales_log;

INSERT INTO orders (id, customer_id, barista_id, order_date)
SELECT DISTINCT s.order_id, c.id, s.barista_id, s.order_date
FROM sales_log s JOIN customers c ON c.phone = s.customer_phone;

INSERT INTO order_items (order_id, drink_id, qty)
SELECT order_id, drink_id, qty FROM sales_log;

INSERT … SELECT DISTINCT يُنزِل كل معلومة مرة واحدة. ثم نملأ orders بربط sales_log بجدول الزبائن الجديد لنأخذ id الزبون من رقم هاتفه. الجداول الجديدة:

الخطوة 6: تحقق أننا لم نفقد شيئًا

نعيد بناء الشكل المسطّح بربط الجداول الخمسة:

SELECT oi.order_id, d.name AS drink, c.name AS customer, c.city, b.name AS barista, oi.qty
FROM order_items oi
JOIN orders o    ON o.id = oi.order_id
JOIN drinks d    ON d.id = oi.drink_id
JOIN customers c ON c.id = o.customer_id
JOIN baristas b  ON b.id = o.barista_id
ORDER BY oi.order_id, d.id;

وفي اختبارنا الكامل (كل الأعمدة الاثنا عشر) طابقت نتيجة الربط جدول sales_log الأصلي صفًّا بصف. التفكيك بلا فقد (lossless): ما فككناه يمكن إعادة تجميعه تمامًا.

الخطوة 7: زالت الشذوذات

تغيير مدينة نورة صار UPDATE customers SET city = 'Jeddah' WHERE phone = '0501110001' الذي يغيّر صفًّا واحدًا فقط. وحذف الطلب لا يمحو المشروب ولا الزبون. وتسجيل مشروب جديد صار INSERT INTO drinks … دون أي طلب.

الخطوة 8: اختبر إجراءات ON DELETE

بعد PRAGMA foreign_keys = ON‏: حذف الزبون 1 (SET NULL) أبقى طلباته بلا زبون:

DELETE FROM customers WHERE id = 1;
SELECT id, customer_id FROM orders ORDER BY id;

وحذف الطلب 101 (CASCADE) أزال بنوده تلقائيًا:

DELETE FROM orders WHERE id = 101;
SELECT order_id, drink_id FROM order_items ORDER BY 1, 2;

أما مشروب مستخدم في طلب (RESTRICT) فيرفض المحرّك حذفه:

خرافة مقابل حقيقة

أين يُستخدم التصميم الجيد فعليًا؟

جرّب بنفسك

بطاقات مراجعة سريعة

اختبر فهمك