تبدیل نمودار ER به جدول (قواعد نگاشت با مثال SQL)

بعد از رسم نمودار ER، باید آن را به جدول‌های واقعی تبدیل کنی. خبر خوب این است که این کار تقریباً مکانیکی است و با چند قاعده‌ی ثابت انجام می‌شود. در این مقاله هر قاعده را با نمودار کوچک و کد CREATE TABLE می‌بینیم.

خلاصه‌ی قواعد نگاشت

قاعده ۱: موجودیت و صفت‌های ساده

هر موجودیت یک جدول می‌شود و هر صفت یک ستون. شناسه‌ی موجودیت کلید اصلی است.

CREATE TABLE Teacher (
  TeacherId INT IDENTITY PRIMARY KEY,
  FullName  NVARCHAR(100) NOT NULL,
  Email     NVARCHAR(150) UNIQUE
);

قاعده ۲: صفت چندمقداری

اگر یک استاد می‌تواند چند شماره‌ی تلفن داشته باشد، نمی‌توانی همه را در یک ستون بنویسی (قانون فرم نرمال اول). یک جدول جدا می‌سازیم:

CREATE TABLE TeacherPhone (
  TeacherId INT NOT NULL REFERENCES Teacher(TeacherId),
  Phone     VARCHAR(20) NOT NULL,
  PRIMARY KEY (TeacherId, Phone)
);

قاعده ۳: رابطه‌ی یک‌به‌چند

هر استاد چند درس ارائه می‌دهد و هر درس را یک استاد ارائه می‌دهد. کلید خارجی در سمت «چند» (Course) می‌نشیند:

CREATE TABLE Course (
  CourseId  INT IDENTITY PRIMARY KEY,
  Title     NVARCHAR(100) NOT NULL,
  TeacherId INT NOT NULL REFERENCES Teacher(TeacherId)
);

قاعده ۴: رابطه‌ی چندبه‌چند

دانشجو و درس چندبه‌چند هستند. یک جدول واسط می‌سازیم که کلید اصلی‌اش ترکیب دو کلید خارجی است و صفت‌های خودِ ارتباط (مثل نمره) هم در آن قرار می‌گیرند:

CREATE TABLE Enrollment (
  StudentId INT NOT NULL REFERENCES Student(StudentId),
  CourseId  INT NOT NULL REFERENCES Course(CourseId),
  Grade     DECIMAL(4,2) NULL,
  PRIMARY KEY (StudentId, CourseId)
);

قاعده ۵: رابطه‌ی یک‌به‌یک

هر کاربر دقیقاً یک پروفایل دارد. کلید خارجی را در یکی از دو جدول می‌گذاریم و با UNIQUE مطمئن می‌شویم هر ردیف فقط یک‌بار ارجاع می‌شود:

CREATE TABLE UserAccount (
  UserId   INT IDENTITY PRIMARY KEY,
  UserName NVARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE UserProfile (
  ProfileId INT IDENTITY PRIMARY KEY,
  UserId    INT NOT NULL UNIQUE REFERENCES UserAccount(UserId),
  Bio       NVARCHAR(500)
);

قاعده ۶: موجودیت ضعیف

«قلم فاکتور» بدون «فاکتور» معنا ندارد و شماره‌ی ردیف فقط داخل یک فاکتور یکتاست. کلید اصلی آن ترکیب کلید مالک و شناسه‌ی جزئی است:

CREATE TABLE InvoiceItem (
  InvoiceId INT NOT NULL REFERENCES Invoice(InvoiceId) ON DELETE CASCADE,
  LineNo    INT NOT NULL,
  Product   NVARCHAR(100) NOT NULL,
  Quantity  INT NOT NULL,
  PRIMARY KEY (InvoiceId, LineNo)
);

قاعده ۷: ارتباط بازگشتی

وقتی موجودیتی با خودش ارتباط دارد (مثلاً هر کارمند یک مدیر دارد که او هم کارمند است)، کلید خارجی به همان جدول اشاره می‌کند:

CREATE TABLE Employee (
  EmployeeId INT IDENTITY PRIMARY KEY,
  FullName   NVARCHAR(100) NOT NULL,
  ManagerId  INT NULL REFERENCES Employee(EmployeeId)
);

ارث‌بری (Specialization) در ER

اگر نمودار ER ارث‌بری دارد (مثلاً Person با زیرگروه‌های Student و Teacher)، سه روش رایج وجود دارد:

• یک جدول برای همه با ستون نوع (Type)؛ ساده اما پر از ستون‌های NULL.
• جدول برای هر زیرگروه که همه‌ی صفت‌ها را دارد؛ بدون JOIN اما تکرار صفت‌های مشترک.
• جدول پدر + جدول برای هر زیرگروه با کلید اصلی که همزمان کلید خارجی به پدر است؛ تمیزترین روش از نظر نرمال‌سازی.

چک‌لیست بعد از تبدیل

• هر جدول کلید اصلی دارد؟
• همه‌ی کلیدهای خارجی به کلید اصلی (یا ستون UNIQUE) جدول مقصد اشاره می‌کنند؟
• رابطه‌ی چندبه‌چندی باقی نمانده است؟
• ستون چندمقداری در یک ستون ذخیره نشده است؟
• جدول‌ها را با نرمال‌سازی بررسی کرده‌ای؟

بعد از این مرحله، کد را در Playground امتحان کن تا از درستی جدول‌ها و کلیدها مطمئن شوی.