یادداشت‌ها

»

کار با داده های سلسله مراتبی در پایگاه داده

گاهی برای یک سایت نیاز است که بخش‌ها یا محصولات را به صورت دسته و زیردسته (category & sub-category) تو در تو ذخیره شود. علاوه بر این موارد، گاهی در مورد مطالب مرتبط (مثلا نظرات یک مطلب) نیز لازم است این حالت شاخه شاخه تو در تو ایجاد شود.
برای پیاده‌سازی این امر، 4 روش وجود دارد:

1. Adjacency list
2. Nested sets
3. Path enumeration
4. Closure table

که در ادامه به بررسی هر یک از این 4 روش و معایب و محاسن آنها می‌پردازم...

1. روش لیست مجاورت (Adjacency list)

این روش ساده‌ترین و ابتدائی‌ترین روشی است که اکثر برنامه‌نویسان به ذهنشان می‌رسد و از آن استفاده می‌کنند.
در این روش هر رکورد دیتابیس، شامل id, parent_id و مشخصات آبجکت است. به عبارت دیگر با مراجعه به هر رکورد، بلافاصله می‌توان نام رکورد والد آن را متوجه شد.
از جمله محاسن این روش، سادگی درج رکورد جدید و انتقال یک رکورد (با زیرمجموعه‌هایش) به عنوان زیرمجموعه یک رکورد دیگر است.
عیب مهم این روش نیازمند بودن آن به کوئری طولانی‌تر یا استفاده از چندین کوئری برای کسب اطلاعات درخت یا بخشی از آن است.
به عبارت دیگر در این روش درج و تغییر (که توسط مدیر انجام می‌شود) دارای انعطاف‌پذیری و سرعت بالایی است و برنامه‌نویس به سادگی می‌تواند برنامه موردنظر را برای آن بنویسد اما نمایش آن (که معمولا به تعداد بسیار بیشتری انجام می‌شود) پرفرمنس کمتری دارد و لذا این روش برای مواقعی که میزان استفاده از منابع محدود باشد یا به دلیل بازدید و بار بالا، پرفرمنس مهم باشد، توصیه نمی‌شود.
برای دسترسی به فرزندان یک node کافی است از کوئری شبیه زیر استفاده کنیم:

SELECT * FROM Comments c1
    LEFT JOIN Comments c2
    ON (c2.parent_id = c1.comment_id);

برای دسترسی به والد یک node کافی است از کوئری شبیه زیر استفاده کنیم:

SELECT * FROM Comments c1
    JOIN Comments c2
    ON (c1.parent_id = c2.comment_id);

سایر کوئری‌های مربوط به این امر در لینک‌های انتهای همین مطلب آمده است.

2. روش مجموعه‌های تو در تو (Nested sets)

توضیحات مصور پیرامون این روش را می‌توانید در این مقاله یا ویکی پدیا ببینید.
این روش که توسط آقای Joe Celko معرفی و مانور زیادی روی آن داده شده، از نظر محاسن و معایب، عکس روش شماره 1 است یعنی نمایش و دسترسی به نقاط یک شاخه یا برگ‌های یک درخت، کاری ساده است و با کوئری‌های ساده قابل حصول است اما حذف و جابجایی یک نقطه نیاز به کوئری‌های بیشتر و پیچیده‌تری دارد.
البته این روش برای مبتدیان شاید کمی سخت به نظر برسد اما پرفرمنس بهتری (برای نمایش) نسبت به روش اول دارد.
ذکر این نکته بد نیست که جناب Joe Celko کتابی دارند به نام Trees and Hierarchies in SQL for Smarties که در آن روش‌های موضوع این صفحه به تفصیل بحث شده است.

3. روش شماره‌بندی مسیر (Path enumeration)

این روش شبیه روش شماره 1 است با این تفاوت که بجای فیلد parent_id فیلد path را داریم که در آن id تمام parent ها ذکر شده و با کاراکتری غیرعددی مانند اسلش (/) یا نقطه (.) جدا شده است.
از نظر محاسن و معایب نیز این روش مانند روش قبل است. مثلا برای انتقال یک شاخه به شاخه دیگر، چندین آپدیت نیاز است.

4. روش جدول محصور (Closure table)

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

* لینکهای آموزشی و کمکی مقالاتی درباره این موضوع

• Managing Hierarchical Data in MySQL
  برای آشنایی با روش کار با متدهای شماره 1 و 2 و نوشتن کوئری هر یک از این دو و همچنین آشنایی با نکات و منابع بیشتر، می‌توانید این مقاله خوب را ببینید.
• Storing Hierarchical Data in a Database
  یک مقاله سه بخشی در این موضوع.
• What is the most efficient/elegant way to parse a flat table into a tree?
  پاسخ خوب یکی از اعضاء stackoverflow به سوال فوق که به هر 4 روشی که گفتم، اشاره کرده.

نمایش داده‌های سلسله مراتبی در سمت کاربر نیز نکته خاصی ندارد و با نوشتن توابعی در PHP می‌توان آنها را به صورت ul/li نمایش داد و سپس با css/javascript آنها را به صورت دلخواه یا حتی منوهای بازشو درآورد. اما برای مدیریت آن، کمی امکانات بیشتر لازم است. مثلا مدیر نیاز دارد که بتواند ترتیب موارد موجود در یک شاخه را تغییر دهد یا یک/چند شاخه خالی یا حاوی زیرشاخه را، به مکان دیگری منتقل کند و ...
در اینگونه موارد بهترین کار، ارسال داده منوها به صورت JSON و سپس استفاده از پلاگین‌هایی است که از روی اطلاعات JSON ، منوها را می‌سازند و امکان تغییر موقعیت عناصر را به سادگی فراهم می‌کنند. jqTree یک نمونه از این پلاگین‌هاست که قبلا در اینجا معرفی کرده بودم.

---

یادداشت‌های مرتبط

1. روش پشتیبان گیری از پایگاه داده MySQL و طریقه بازگردانی آن
2. API چیست؟
3. پایگاه داده MySQL و مدیریت آن
4. گرافیک برداری
5. آبجکت در جاوا اسکریپت
6. کشیدن و انداختن (رها کردن)
7. کلید خارجی (Foreign Key) ، محاسن و معایب آن
8. پایگاه داده در جاوا اسکریپت