گردش کار مدل‌سازی داده: از صورت مسئله تا CDM، LDM، PDM و اسکریپت SQL

طراحی پایگاه‌داده یک «کار یک‌مرحله‌ای» نیست؛ یک مسیر مشخص دارد که از صورت مسئله شروع می‌شود و به اسکریپت SQL می‌رسد. در این درس همان مسیر را با یک مثال کامل (نوبت‌دهی یک کلینیک) قدم‌به‌قدم می‌رویم. درس‌های بعدی همین کار را با ابزارهای واقعی (Power Designer و ابزارهای رایگان) انجام می‌دهند.

پنج مرحله‌ی مدل‌سازی

مرحله ۱: تحلیل نیازمندی‌ها

«کلینیک بیماران را با نام، تاریخ تولد و تلفن ثبت می‌کند. هر پزشک نام و تخصص دارد. بیمار می‌تواند برای یک پزشک نوبت بگیرد؛ هر نوبت تاریخ و ساعت و وضعیت (ثبت‌شده، انجام‌شده، لغو) دارد. یک بیمار می‌تواند چند نوبت داشته باشد و یک پزشک در روز چند نوبت دارد، اما یک پزشک در یک ساعت فقط یک نوبت دارد.»

اسم‌ها را بردار: بیمار، پزشک، نوبت (موجودیت‌ها) و صفت‌هایی مثل نام، تخصص، تاریخ. فعل‌ها: «نوبت می‌گیرد»، «ویزیت می‌کند» (ارتباط‌ها). جمله‌ی آخر یک قاعده‌ی کسب‌وکار است که بعداً به قید تبدیل می‌شود.

مرحله ۲: مدل مفهومی (CDM)

فقط موجودیت‌ها، صفت‌ها، شناسه‌ها و ارتباط‌ها را می‌کشیم. هنوز هیچ کلید خارجی یا نوع داده‌ای نداریم. روی هر سر ارتباط، کاردینالیتی (min,max) نوشته می‌شود:

مرحله ۳: مدل منطقی (LDM)

هر موجودیت جدول می‌شود و برای هر رابطه‌ی یک‌به‌چند، شناسه‌ی سمت «یک» به‌عنوان کلید خارجی به سمت «چند» می‌آید: PatientId و DoctorId به Appointment اضافه می‌شوند. چون هیچ رابطه‌ی چندبه‌چندی نداریم، جدول واسط لازم نیست.

مرحله ۴: مدل فیزیکی (PDM)

حالا برای هر ستون یک نوع داده‌ی SQL Server انتخاب می‌کنیم و قیدها و ایندکس‌ها را اضافه می‌کنیم. قاعده‌ی «یک نوبت در هر ساعت برای هر پزشک» همین‌جا به یک ایندکس یکتا تبدیل می‌شود:

CREATE TABLE Patient (
  PatientId INT IDENTITY(1,1) PRIMARY KEY,
  FullName  NVARCHAR(100) NOT NULL,
  BirthDate DATE NULL,
  Phone     VARCHAR(20) NULL
);

CREATE TABLE Doctor (
  DoctorId  INT IDENTITY(1,1) PRIMARY KEY,
  FullName  NVARCHAR(100) NOT NULL,
  Specialty NVARCHAR(50)  NOT NULL
);

CREATE TABLE Appointment (
  AppointmentId   INT IDENTITY(1,1) PRIMARY KEY,
  PatientId       INT NOT NULL REFERENCES Patient(PatientId),
  DoctorId        INT NOT NULL REFERENCES Doctor(DoctorId),
  AppointmentDate DATETIME2 NOT NULL,
  Status          NVARCHAR(20) NOT NULL
    CONSTRAINT CK_Appointment_Status CHECK (Status IN (N'registered', N'done', N'cancelled'))
);

-- قاعده‌ی کسب‌وکار: هر پزشک در هر زمان فقط یک نوبت دارد
CREATE UNIQUE INDEX UX_Appointment_Doctor_Time
  ON Appointment (DoctorId, AppointmentDate);

-- ایندکس برای فهرست نوبت‌های یک بیمار
CREATE INDEX IX_Appointment_Patient ON Appointment (PatientId);

مرحله ۵: بازبینی با داده و پرس‌وجو

مدل وقتی ارزش دارد که پرسش‌های واقعی را جواب بدهد. چند داده‌ی نمونه وارد کن و سؤال‌های کلینیک را بپرس:

-- نوبت‌های امروز یک پزشک
SELECT a.AppointmentDate, p.FullName AS Patient
FROM Appointment a
JOIN Patient p ON p.PatientId = a.PatientId
WHERE a.DoctorId = 1
  AND CAST(a.AppointmentDate AS DATE) = CAST(GETDATE() AS DATE)
ORDER BY a.AppointmentDate;

-- بیمارانی که هیچ نوبتی نداشته‌اند
SELECT p.FullName
FROM Patient p
LEFT JOIN Appointment a ON a.PatientId = p.PatientId
WHERE a.AppointmentId IS NULL;

اگر پرسشی بود که مدل جوابش را نمی‌داد (مثلاً «هر نوبت چه خدماتی داشته؟»)، به مرحله‌ی ۲ برگرد و مدل را اصلاح کن. این رفت‌وبرگشت طبیعی است.

ردیابی نیازمندی تا جدول

در درس بعد، همین مسیر را با Power Designer انجام می‌دهیم.