مدل‌سازی روابط: یک‌به‌چند، چندبه‌چند، و نمودارهای موجودیت-رابطه

چرا روابط به‌همان‌اندازه موجودیت‌ها اهمیت دارند

موجودیت‌ها و ویژگی‌هایی که پیش‌تر در این مجموعه بحث شد آنچه در یک حوزه وجود دارد را می‌گیرند، اما یک دیتابیس همچنین نیاز دارد بگیرد چگونه آن چیزها به یکدیگر متصل‌اند — یک مشتری سفارش‌ها ثبت می‌کند، یک کتاب نویسنده‌ها دارد، یک درس دانش‌آموز دارد. این اتصالات Relationships (روابط) نامیده می‌شوند، و مدل‌سازی درست آن‌ها به‌همان‌اندازه شناسایی خود موجودیت‌ها مهم است.

کاردینالیتی: چند در هر سمت

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

روابط یک‌به‌یک

یک رابطه One-to-One (1:1) (یک‌به‌یک) یعنی هر نمونه از یک موجودیت با دقیقاً یک نمونه از دیگری مرتبط است، و برعکس.

مثال: employee و parking_spot
هر کارمند دقیقاً یک جای پارک اختصاص‌داده‌شده دارد،
و هر جای پارک متعلق به دقیقاً یک کارمند است

روابط یک‌به‌یک کم‌رایج‌ترین از این سه هستند، و اغلب نشانه‌ای هستند که دو موجودیت بالقوه می‌توانند در یک جدول واحد ادغام شوند، مگر اینکه یک دلیل خاص برای جداماندن آن‌ها وجود داشته باشد، مانند مجوزهای دسترسی متفاوت یا اینکه داده مرتبط برای بسیاری ردیف اختیاری باشد.

روابط یک‌به‌چند

یک رابطه One-to-Many (1:N) (یک‌به‌چند) یعنی یک نمونه از یک موجودیت می‌تواند با بسیاری نمونه از دیگری مرتبط باشد، اما هر نمونه از موجودیت دوم فقط به یک نمونه از اولی برمی‌گردد. این تا حد زیادی رایج‌ترین نوع رابطه در دیتابیس‌های واقعی است.

مثال: customer و orders
یک مشتری می‌تواند بسیاری سفارش ثبت کند،
اما هر سفارش متعلق به دقیقاً یک مشتری است

پیاده‌سازی یک رابطه یک‌به‌چند از مکانیزم کلید خارجی که پیش‌تر در این مجموعه معرفی شد استفاده می‌کند: کلید خارجی روی سمت "چند" رابطه قرار می‌گیرد، و به کلید اصلی سمت "یک" برمی‌گردد.

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER,     -- کلید خارجی، سمت "چند"
    order_date DATE,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

روابط چندبه‌چند

یک رابطه Many-to-Many (M:N) (چندبه‌چند) یعنی بسیاری نمونه از یک موجودیت می‌توانند با بسیاری نمونه از دیگری مرتبط باشند.

مثال: books و authors
یک کتاب می‌تواند چند نویسنده داشته باشد،
و یک نویسنده می‌تواند چند کتاب بنویسد

این دقیقاً همان وضعیتی است که یک ویژگی چندمقداری، که پیش‌تر در این مجموعه بحث شد، به آن اشاره می‌کند — نمی‌تواند صرفاً با قراردادن یک کلید خارجی در هر جدولی نمایش داده شود، چون یک ستون واحد فقط می‌تواند به یک مقدار ارجاع دهد. در عوض، یک رابطه چندبه‌چند نیازمند یک Junction Table (جدول اتصال) جداگانه است (که Bridge Table یا Associative Table نیز نامیده می‌شود)، که کلیدهای خارجی به هر دو موجودیت مرتبط را نگه می‌دارد.

CREATE TABLE book_authors (
    book_id INTEGER,
    author_id INTEGER,
    PRIMARY KEY (book_id, author_id),
    FOREIGN KEY (book_id) REFERENCES books(book_id),
    FOREIGN KEY (author_id) REFERENCES authors(author_id)
);

هر ردیف در این جدول اتصال یک جفت‌سازی کتاب-نویسنده خاص را نشان می‌دهد. یک کتاب با سه نویسنده سه ردیف در این جدول خواهد داشت، و یک نویسنده که پنج کتاب نوشته در پنج ردیف ظاهر می‌شود — این ساختار به‌طور طبیعی هر ترکیبی را بدون تکرار داده در خود جداول کتاب‌ها یا نویسنده‌ها پشتیبانی می‌کند.

نمودارهای موجودیت-رابطه: تصویرسازی طراحی

یک Entity-Relationship Diagram (ERD) نمادگذاری بصری استاندارد برای نمایش موجودیت‌ها، ویژگی‌هایشان، و روابط بین آن‌ها پیش از نوشتن هر SQL است. موجودیت‌ها معمولاً به‌عنوان جعبه‌ها رسم می‌شوند، ویژگی‌ها به‌عنوان برچسب‌های کوچک‌تر متصل به آن جعبه‌ها، و روابط به‌عنوان خط‌های متصل‌کننده موجودیت‌ها، حاشیه‌نویسی‌شده با کاردینالیتی‌شان.

نمادگذاری ERD ساده‌شده:

[Customer] ----1------N---- [Order]
   |
  ویژگی‌ها: customer_id، name، email

[Book] ----N------N---- [Author]
   |                        |
  ویژگی‌ها:              ویژگی‌ها:
  book_id، title          author_id، name

نشانه‌گذاری‌های "1" و "N" روی هر انتهای یک خط رابطه کاردینالیتی‌اش را نشان می‌دهد، که فوراً از نمودار روشن می‌کند کدام سمت یک رابطه یک‌به‌چند به کلید خارجی نیاز دارد، یا اینکه یک رابطه چندبه‌چند پس از پیاده‌سازی به یک جدول اتصال نیاز خواهد داشت.

چرا رسم این نمودار پیش از نوشتن SQL اهمیت دارد

طراحی بصری روابط پیش از ساخت هر جدولی، مسائل طراحی را بسیار ارزان‌تر از کشف آن‌ها پس از پیاده‌سازی می‌گیرد. یک رابطه که به‌نظر می‌رسد باید یک‌به‌چند باشد اما در بررسی دقیق‌تر قوانین کسب‌وکاری واقعی، در نهایت گاهی چندبه‌چند از آب درمی‌آید (مانند متوجه‌شدن اینکه یک کتاب، در واقعیت، می‌تواند چند نویسنده داشته باشد، وقتی طراحی اولیه فقط یکی را فرض می‌کرد)، بسیار راحت‌تر و ارزان‌تر