normalization پایگاه داده چیست؟ راهنمای کامل normalization

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

فرض کنید پایگاه داده ای دارید که با استفاده از آن کار های زیر را انجام می دهید:

• شناسایی فروشنده های شرکت
• لیست مشتریانی که شرکت با آن ها تماس گرفته است
• شناسایی اینکه کدام فروشنده ها با کدام مشتریان تماس گرفته اند

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

نکته: برای مطالعه این مقاله آشنایی با مباحث اولیه SQL و پایگاه داده الزامی است.

دلایل نرمال سازی پایگاه داده

سه دلیل اصلی برای نرمال سازی پایگاه داده وجود دارد:

• کاهش تکرار داده در پایگاه داده
• حذف یا کاهش شانس بروز data anomaly
• کاهش پیچیدگی کوئری ها، مخصوصا جست و جو ها

data anomaly یا «ناهنجاری داده» زمانی اتفاق می افتد که پایگاه داده نرمال سازی نشده باشد و طراحی آن دچار مشکلات جدی باشد. یک مثال از پایگاه داده ای که دچار ناهنجاری داده باشد این است که با حذف بخشی از داده ها، بخش های دیگر داده ها نیز حذف شود. مثلا اگر یک کتابخانه داشته باشیم و فردی کتابی را امانت گرفته باشد، ما نامش را به همراه نام کتاب قرض گرفته شده در پایگاه داده ذخیره می کنیم. زمانی که آن فرد کتاب را برگرداند باید این داده ذخیره شده در پایگاه داده را حذف کنیم اما با حذف آن کاربر، آن کتاب نیز از پایگاه داده حذف می شود! این نتیجه طراحی بد پایگاه داده است.

ستونی که زیرش خط کشیده شده است (EmployeeID – حاوی آیدی کارکنان شرکت) همان primary key در این جدول است. اگر به تصویر بالا با دقت نگاه کنید متوجه می شوید که این جدول چندین هدف مختلف را به صورت همزمان دنبال می کند:

• شناسایی فروشنده های شرکت (ستون SalesPerson)

• شناسایی دفتر های فروش (ستون SalesOffice) و شماره تلفن آن دفترها (ستون OfficeNumber)

• اتصال یک فروشنده به یک دفتر فروش

• نمایش مشتریان هر فروشنده

چنین جدولی نشان از طراحی بسیار بد و غیرحرفه ای است. مشکلات زیادی در این پایگاه داده وجود دارد که اولین آن ها تکرار داده است. مثلا برای هر فروشنده، هم دفتر فروش و هم شماره تلفن آن را ذخیره کرده ایم بنابراین داده ها مرتبا تکرار می شوند. این مسئله به خودی خود دو مشکل بزرگ را برایمان ایجاد می کند. اولا حجم پایگاه داده را بی جهت افزایش می دهد  که خودش باعث کاهش سرعت پایگاه داده می شود و دوما مدیریت تغییر داده ها در چنین پایگاه داده ای بسیار دشوار است.

منظور من از مدیریت تغییر داده چیست؟ منظورم هر عملیاتی مانند ویرایش (UPDATE) یا حذف (DELETE) و غیره است. مثلا فرض کنید دفتر فروش شیکاگو (Chicago) را به شهر Evanston منتقل کرده باشیم. حالا برای تغییر این موضوع در پایگاه داده باید شهر شیکاگو را برای تمام فروشنده ها (SalesPersons) ویرایش کنیم که یک UPDATE عظیم روی کل پایگاه داده خواهد بود. این یک مثال خوب از data anomaly (ناهنجاری داده) است که به طور خاص modification anomalies (ناهنجاری ویرایش) محسوب می شود.

نوع دیگری از ناهنجاری داده Insert Anomaly یا ناهنجاری ثبت است؛ در جدول بالا تا زمانی که اطلاعات تمام ستون ها را نداشته باشیم نمی توانیم داده هایی را ثبت کنیم. مثلا تا زمانی که فروشنده خود را نشناسیم و اطلاعاتش را نداشته باشیم نمی توانیم یک دفتر فروش را اضافه کنیم. چرا؟ به دلیل اینکه برای اضافه کردن یک ردیف به چنین جدولی باید EmployeeID (آیدی کارمند) را داشته باشیم.

حتما یادتان است که EmployeeID همان primary key جدول ما بود بنابراین نمی توانیم آن را خالی بگذاریم.

ناهنجاری بعدی Update Anomaly یا ناهنجاری ویرایش است. در جدول بالا اگر شماره تلفن یک دفتر فروش تغییر کند باید آن را در چندین محل مختلف ویرایش کنیم در غیر این صورت ممکن است برخی از ردیف ها شماره قدیمی را داشته باشند. در نهایت ناهنجاری حذف یا Deletion Anomaly را داریم که حتما می توانید معنی اش را حدس بزنید. مثلا اگر آقای John Hunt بازنشست بشود و ما ردیف او را از جدول بالا حذف کنیم اطلاعات دفتر فروش نیویورک را نیز از دست می دهیم!

در نهایت آخرین دلیل نرمال سازی پایگاه داده مسئله ساده سازی کوئری ها است. اگر بخواهیم در جدول بالا یک مشتری خاص (مثلا Ford) را پیدا کنیم باید چنین کوئری بنویسیم:

SELECT SalesOffice

FROM SalesStaff

WHERE Customer1 = ‘Ford’ OR

      Customer2 = ‘Ford’ OR

      Customer3 = ‘Ford’

طبیعتا اگر مشتریان در یک جدول جداگانه بودند مجبور به نوشتن چنین کوئری بدی نبودیم. مسئله زمانی بدتر می شود که بخواهید نتایج را بر اساس مشتریان مرتب کنید. در این حالت باید سه کوئری UNION جداگانه بنویسیم!

تعریف نرمال سازی پایگاه داده

نرمال سازی پایگاه داده، راه حل ما برای مشکلات بخش قبل است. همانطور که گفتم نرمال سازی سه حالت مختلف دارد:

• اولین حالت نرمال یا ۱NF: داده ها در یک جدول رابطه ای ذخیره می شوند و هر ستون مقادیر اتمی (atomic) را قبول می کند. مقادیر اتمی مقادیر تک و مستقل هستند (گروهی از داده ها نیستند). همچنین هیچ ستونی نمی تواند تکرار شود.
• دومین حالت نرمال یا ۲NF: جدول در حالت نرمال اول است و تمام ستون ها وابسته به primary key در آن جدول هستند.
• سومین حالت نرمال یا ۳NF: جدول در حالت نرمال دوم است و ستون ها فقط به صورت غیر ترایا به primary key وابسته هستند.