من التحليل إلى تصميم النظام

تصميم طبقة البيانات من مخطط العلاقات

18 دقيقة الدرس 5 من 10

تصميم طبقة البيانات من مخطط العلاقات

بحلول مرحلة التصميم، يكون بحوزتك مخطط علاقات الكيانات (ERD) المعتمد الذي يلتقط البيانات التي تحتاجها المؤسسة وكيفية ترابط الكيانات. هذا المخطط هو نموذج منطقي — يصف العالم بمصطلحات الأعمال بمعزل عن أي تقنية قاعدة بيانات. الخطوة التالية هي ترجمته إلى مخطط فيزيائي: جداول ملموسة، وأعمدة ذات أنواع بيانات، وفهارس، وقيود سلامة يمكن لمحرك قاعدة البيانات الفعلي تطبيقها. تُعرف هذه الترجمة بـالتحويل من النموذج المنطقي إلى الفيزيائي، وهي من أهم القرارات التي يتخذها محلل الأنظمة. إذا نُفِّذت بإتقان دعمت قاعدة البيانات التطبيق لسنوات، وإن نُفِّذت بتهاون فرضت هجرات مؤلمة، وقد تفقد البيانات صامتةً، أو تتعثر الاستعلامات تحت الحِمل.

الخطوة الأولى — تحويل كل كيان إلى جدول

القاعدة الأساسية بسيطة: كل كيان قوي في مخطط ERD يصبح جدولًا، وكل سمة تصبح عمودًا. لكن "البساطة" لا تعني "التسرع". كل قرار يستحق تأملًا متعمدًا:

  • اصطلاح التسمية: يجب أن تكون أسماء الجداول متسقة — مثل snake_case بصيغة الجمع (clinic_appointments) أو PascalCase بصيغة المفرد، لكن لا تُخلط الأسلوبان. اختر أسلوبًا واحدًا وطبّقه على المخطط كله.
  • المفتاح الأساسي: كل جدول يحتاج مفتاحًا أساسيًا. إذا كان المفتاح الطبيعي (كرقم الهوية الوطنية أو البريد الإلكتروني) ثابتًا ومدمجًا، فيمكن استخدامه. وفي الغالب يُفضَّل مفتاح بديل — عدد صحيح تلقائي الزيادة أو UUID — لأن المفاتيح الطبيعية قد تتغير وقد تتكرر بالواقع.
  • الكيانات الضعيفة: الكيان الضعيف الذي لا يُحدَّد بدون كيانه المالك يُحوَّل إلى جدول مفتاحه الأساسي مركّب من مفتاحه الجزئي ومفتاح أجنبي يشير إلى جدول المالك.
ملاحظة للمحلل: لا تسمح للمطورين بإعادة تسمية جداولك أثناء التنفيذ دون موافقتك. أسماء الأعمدة والجداول في المخطط الفيزيائي يجب أن تتتبع المصطلحات المستخدمة في مخطط ERD ووثيقة المتطلبات. إعادة التسمية تكسر قابلية التتبع بصمت.

الخطوة الثانية — اختيار أنواع بيانات الأعمدة بدقة

يجب تعيين نوع بيانات محدد لكل سمة في مخطط ERD. هذا الاختيار يؤثر على كفاءة التخزين وأداء الاستعلامات وسلامة البيانات. فيما يلي أكثر قرارات التحويل شيوعًا:

  • النصوص القصيرة (أسماء، رموز، علامات الحالة) → VARCHAR(n) بحد أقصى واقعي — لا تستخدم VARCHAR(255) في كل مكان؛ رمز الدولة حرفان لا 255.
  • النصوص الطويلة (ملاحظات، أوصاف، عناوين) → TEXT أو NVARCHAR(MAX).
  • الأعداد الصحيحة (كميات، أعداد، أعمار) → INT أو SMALLINT؛ استخدم BIGINT فقط عند الحاجة الفعلية.
  • المبالغ المالية والنسب → DECIMAL(p, s)، وليس FLOAT أو DOUBLE؛ حسابات الفاصلة العائمة تُدخل أخطاء تقريب في الحسابات المالية.
  • التواريخ والأوقات → DATE أو TIME أو DATETIME أو TIMESTAMP — اختر بناءً على ما تخزّنه فعلًا. موعد الحجز لا يحتاج مكوّن الوقت؛ سجل النشاط يحتاجه. خزّن جميع الطوابع الزمنية بتوقيت UTC.
  • الأعلام المنطقية → BOOLEAN — تجنب تخزين 'Y'/'N' كنصوص؛ فهي غير مرئية لمُحسِّنات الاستعلام.
  • القيم المعدودة (حالة، فئة) → نوع ENUM أو جدول مرجعي. الجداول المرجعية أسهل صيانةً عندما تتغير القائمة مع الوقت.

الخطوة الثالثة — ترجمة العلاقات إلى مفاتيح أجنبية

كل خط علاقة في مخطط ERD يُصبح عمودًا أو أكثر من المفاتيح الأجنبية في المخطط الفيزيائي. تحدد أسلوبية الترقيم موضع المفتاح الأجنبي:

  • واحد-إلى-كثير (1:N): يذهب المفتاح الأجنبي إلى الجانب "الكثير". في نظام عيادة، طبيب واحد له مواعيد كثيرة، لذا يحتوي appointments.doctor_id على مرجع لـdoctors.id.
  • واحد-إلى-واحد (1:1): يمكن وضع المفتاح الأجنبي على أي من الجانبين؛ ضعه في الجدول الاختياري أو الأكثر استعلامًا منفردًا.
  • كثير-إلى-كثير (M:N): أنشئ جدول وصل (يُسمى أيضًا جدول جسر أو ربط) مفتاحه الأساسي مركّب من المفتاحين الأجنبيين. نظام تسجيل المقررات فيه students وcourses في علاقة M:N — جدول الوصل enrollments يحتوي على student_id وcourse_id، إضافةً إلى سمات التسجيل (تاريخ التسجيل، الدرجة).
ERD-to-Physical Mapping: Clinic Appointment Example Logical (ERD) Physical (Tables) Doctor id, name, specialty Patient id, name, dob, phone Appointment id, date, time status, notes 1 N 1 N doctors 🔑 id INT PK AUTO name VARCHAR(100) NOT NULL specialty VARCHAR(60) patients 🔑 id INT PK AUTO name VARCHAR(100) NOT NULL dob DATE phone VARCHAR(20) UNIQUE appointments 🔑 id INT PK AUTO 🔗 doctor_id INT FK 🔗 patient_id INT FK appt_date DATE NOT NULL appt_time TIME NOT NULL status ENUM(...) notes TEXT created_at TIMESTAMP maps to
تحويل مخطط ERD بثلاثة كيانات (طبيب، مريض، موعد) إلى ثلاثة جداول فيزيائية بأعمدة مُصنَّفة الأنواع ومفاتيح أجنبية.

الخطوة الرابعة — تعريف القيود

لا يمكن تطبيق سلامة البيانات بكود التطبيق وحده — فالمستخدمون يصلون إلى قواعد البيانات عبر مسارات متعددة (أدوات التقارير، السكربتات الإدارية، واجهات API). يجب أن تكون قاعدة البيانات نفسها آخر خط دفاع. أنواع القيود الرئيسية التي يجب توثيقها في وثيقة التصميم هي:

  • NOT NULL: كل عمود يجب أن يحتوي دائمًا على قيمة — لا تترك قابلية القيمة الفارغة للمصادفة. في مخطط العيادة، لا يمكن أن يكون appt_date فارغًا أبدًا؛ أما notes فيمكن.
  • UNIQUE: قيد التفرد على عمود واحد أو متعدد — رقم هاتف المريض يجب أن يكون فريدًا؛ وسطر الفاتورة يجب أن يكون فريدًا بـ(invoice_id, line_number).
  • CHECK: تحقق من صحة القيمة داخليًا — CHECK (price >= 0)، CHECK (end_date >= start_date). حدِّد هذه القيود دائمًا؛ تمنع الحالات المستحيلة من الوصول إلى قاعدة البيانات.
  • FOREIGN KEY مع إجراء المرجعية: عرِّف سلوك ON DELETE وON UPDATE صراحةً. الخيارات هي RESTRICT وCASCADE وSET NULL وNO ACTION. في العيادة، يجب أن يكون حذف طبيب له مواعيد قيدًا RESTRICT — لا تريد إنشاء سجلات يتيمة بصمت.
  • القيم الافتراضية: created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP يُلغي العبء على التطبيق ويجعل تتبع التدقيق تلقائيًا.
فخ شائع — الحذف المتتالي: ON DELETE CASCADE خطر ما لم تكن تريد فعلًا تدمير الصفوف الأبناء عند حذف الأب. في نظام التجارة الإلكترونية، حذف product بـCASCADE سيمحو صامتًا جميع أسطر الطلبات المرتبطة بذلك المنتج — مما يدمر سجلات المبيعات التاريخية. استخدم RESTRICT كإعداد افتراضي وبرِّر كل CASCADE كتابيًا.

الخطوة الخامسة — تصميم الفهارس للأداء

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

  • أعمدة المفاتيح الأجنبية: فهرِس كل عمود مفتاح أجنبي. بدون فهرس على appointments.doctor_id، سيُجري استعلام "كل مواعيد الدكتورة هناء" فحصًا كاملًا للجدول في كل مرة.
  • الأعمدة كثيرة التصفية: إذا كان المستخدمون يبحثون عن المواعيد حسب الحالة أو النطاق الزمني، فتلك الأعمدة تحتاج فهارس.
  • الفهارس المركّبة: عندما تُصفِّي الاستعلامات دائمًا على عمودين معًا (مثل patient_id + appt_date)، يكون الفهرس المركّب أسرع من فهرسين منفصلين.
  • الفهارس الفريدة: أي قيد UNIQUE يُنفَّذ كفهرس فريد — أعلن عن كليهما معًا.
Index Design Decision Map Index Design: appointments table appointments columns id doctor_id patient_id appt_date appt_time status notes created_at PK index on id auto-created by engine idx_appt_doctor FK join to doctors idx_appt_patient FK join to patients idx_appt_date_status composite: date + status for dashboard queries No index on notes large TEXT, never filtered
تحديد الفهارس الواجب إنشاؤها على جدول المواعيد بناءً على أنماط الاستعلام.

الخطوة السادسة — معالجة السمات المشتقة ومتعددة القيم

تحتوي مخططات ERD أحيانًا على سمات لا تُحوَّل مباشرةً إلى عمود واحد:

  • السمات المشتقة (مثل العمر المشتق من تاريخ الميلاد): لا تخزّنها كأعمدة. خزّن المصدر (dob) ودع التطبيق أو عرض قاعدة البيانات يحسب القيمة المشتقة. تخزين البيانات المشتقة يخلق مشكلات اتساق فور تغيير المصدر.
  • السمات متعددة القيم (مثل امتلاك المريض أرقام هواتف متعددة): لا تحشرها في عمود واحد مفصول بفواصل. أنشئ جدولًا فرعيًا — patient_phones(id, patient_id, phone_number, phone_type) — بمفتاح أجنبي يعود إلى الجدول الأب. هذا يحافظ على التسوية ويجعل كل رقم هاتف قابلًا للاستعلام بشكل مستقل.
فحص سريع للتسوية: إذا كان أي عمود في جدول جديد يمكنه حمل قائمة قيم في خلية واحدة — أرقام هواتف، علامات، حالات — توقف واسأل نفسك: هل جدول فرعي هو الجواب الصحيح؟ الجواب دائمًا تقريبًا: نعم.

توثيق المخطط الفيزيائي

مخرج هذه الخطوة ليس كودًا — بل هو مواصفة طبقة البيانات التي تصبح قسمًا في وثيقة مواصفات التصميم. لكل جدول وثِّق: اسم الجدول والغرض منه، وأسماء الأعمدة مع أنواعها وقابلية القيمة الفارغة، والمفتاح الأساسي، وجميع المفاتيح الأجنبية وإجراءاتها المرجعية، وجميع قيود CHECK، وجميع الفهارس غير الأساسية، وأي ملاحظات ذات صلة بالقيم الافتراضية أو المشغّلات. هذه الوثيقة يراجعها مدير قاعدة البيانات والمطورون والمحلل الرئيسي قبل كتابة سطر SQL واحد.

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

الدرس السابق مواصفات الواجهات وواجهات برمجة التطبيقات
الدرس التالي تصميم المدخلات والمخرجات
العودة للدورة