نرمال‌سازی داده

یک پایگاه داده نرمال‌سازی شده چگونه به نظر می‌رسد و چرا ساختار جدول مهم است.

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

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

اصولی که در این درس پوشش می‌دهیم برای سیستم‌های مدیریت پایگاه داده رابطه‌ای (RDBMS) اعمال می‌شوند. اگر از یک پایگاه داده NoSQL یا مبتنی بر سند مثل MongoDB استفاده می‌کنید، اطلاعات زیر اعمال نخواهد شد.

ساده‌سازی و کاهش ذخیره‌سازی: مزایای داده نرمال‌سازی شده

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

• ساده‌سازی پرس‌وجوهای تراکنشی. با داده نرمال‌سازی شده، یک پرس‌وجو برای آدرس‌های مشتری فقط نیاز به نگاه در فیلد واحد که آن آدرس‌ها را ذخیره می‌کند دارد. اگر آدرس‌های مشتری را چندین بار در مکان‌های مختلف در پایگاه داده خود ذخیره کنید یا حتی چندین آدرس را در همان فیلد نگه دارید، آن پرس‌وجو زمان بیشتری برای اجرا می‌برد.
• کاهش اندازه پایگاه داده شما. اگر داده مشتری را در چندین مکان در پایگاه داده خود تکرار کنید، یعنی فضایی برای ذخیره آن اطلاعات چندین بار ساخته‌اید. این ممکن است نگرانی عمده‌ای نباشد اگر پایگاه داده شما فقط شامل چند جدول است، اما اگر در مقیاس بزرگتر کار می‌کنید، فضای دیسک می‌تواند در اولویت باشد. کاهش اطلاعات تکراری یعنی کاهش هزینه‌های ذخیره‌سازی، چه یک سرور محلی اجرا می‌کنید یا به یک پایگاه داده میزبانی شده در ابر متکی هستید.
• آسان‌تر کردن نگهداشت پایگاه داده. به همان داده مشتری ذخیره شده چندین بار در پایگاه داده خود فکر کنید. هر بار که یک مشتری آدرس خود را تغییر می‌دهد، باید در هر نمونه از یک فیلد Customer Address به‌روزرسانی شود، که فضای زیادی برای خطا باقی می‌گذارد. اگر داده شما نرمال‌سازی شده است، فقط یک فیلد Customer Address خواهید داشت، که به جداول مرتبط دیگر مثل Orders join می‌شود.

ناهنجاری‌های داده

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

ناهنجاری به‌روزرسانی

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

ناهنجاری درج

یک ناهنجاری درج زمانی رخ می‌دهد که یک رکورد نمی‌تواند بدون اینکه فیلدهای خاص شامل داده باشند ایجاد شود — داده‌ای که ممکن است هنوز وجود نداشته باشد. به عنوان مثال، یک پایگاه داده denormalized ممکن است طوری ساختار یافته باشد که یک حساب مشتری نمی‌تواند ایجاد شود مگر اینکه آن مشتری یک سفارش انجام داده باشد. نرمال‌سازی آن پایگاه داده این مشکل را حل می‌کند، از طریق ایجاد جداول جداگانه Orders و Customers، بدون قانونی که مقادیر null را ممنوع کند.

ناهنجاری حذف

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

قوانین نرمال‌سازی

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

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

فرم نرمال اول (1NF)

فرم نرمال اول (1NF) دیکته می‌کند که هر فیلد در یک جدول باید فقط یک مقدار ذخیره کند، و جدول شما نباید شامل چندین فیلد که اطلاعات مشابه را ذخیره می‌کنند باشد، مثل ستون‌های با عنوان Address1 و Address2.

در اینجا یک مثال از یک جدول که طبق فرم نرمال اول نرمال‌سازی می‌کنیم. این جدول شامل اطلاعات درباره دوره‌های کالج و کسانی که آن‌ها را تدریس می‌کنند است.

جدول استاد

Professor ID	Professor name	Course name
P001	Gene Watson	Intro to Philosophy; Ethics
P002	Melissa King	Quantum Mechanics
P003	Errol Tyson	Macroeconomics
P004	Mary Jacobson	Graphic Novels

متوجه می‌شویم که در حالی که فیلدهای ما متمایز هستند، یک استاد (Gene Watson، در ردیف اول) دو دوره تدریس می‌کند، و آن اطلاعات در حال حاضر در یک سلول واحد ذخیره شده است. اگر این جدول را طبق 1NF نرمال‌سازی کنیم، باید داده خود را به چندین جدول تقسیم کنیم:

جدول استاد نرمال‌سازی شده

Professor ID	Professor name
P001	Gene Watson
P002	Melissa King
P003	Errol Tyson
P004	Mary Jacobson

جدول دوره نرمال‌سازی شده

Course ID	Course name	Professor ID
C001	Intro to Philosophy	P001
C002	Ethics	P001
C003	Quantum Mechanics	P002
C004	Macroeconomics	P003
C005	Graphic Novels	P004

از آنجایی که یک استاد می‌تواند بیش از یک دوره تدریس کند، این داده را به دو جدول تقسیم کرده‌ایم. حالا، جدول Professor ما یک رابطه یک به چند با جدول Course دارد. این ساختار جدول جدید فرم نرمال اول را برآورده می‌کند، و دو جدول را از طریق یک کلید خارجی، فیلد Professor ID join می‌کند.

فرم نرمال دوم (2NF)

فرم نرمال دوم درباره کاهش تکراری‌ها و اطمینان از اینکه هر فیلد چیزی درباره آنچه entity key شناسایی می‌کند توصیف می‌کند. برای برآورده کردن 2NF، همه فیلدها در یک جدول که entity key نیستند باید کاملاً به entity key جدول وابسته باشند (که ممکن است یک کلید ترکیبی متشکل از دو فیلد باشد). بیایید به یک مثال جدید نگاه کنیم — یک جدول که شامل اطلاعات درباره تولد کارمندان شما است.

جدول تولد کارمند

Employee ID	Birthday	Department
E001	November 18	Accounting
E002	March 29	Sales
E003	June 1	Marketing
E004	February 7	Accounting

این جدول 1NF را برآورده می‌کند، چون هر ستون متمایز است و فقط یک مقدار در هر سلول نگه می‌دارد. با این حال، این جدول یک کلید ترکیبی دارد: Employee ID + Birthday با هم entity key جدول را تشکیل می‌دهند. این جدول در حالت فعلی 2NF را برآورده نمی‌کند، چون فیلد Department فقط به طور جزئی به کلید ترکیبی وابسته است، چون بخش یک کارمند به تولد آن‌ها بستگی ندارد، فقط به ID کارمند آن‌ها. برای رفع این، این اطلاعات را به دو جدول تقسیم می‌کنیم:

جدول تولد کارمند نرمال‌سازی شده

Employee ID	Birthday
E001	November 18
E002	March 29
E003	June 1
E004	February 7

جدول بخش کارمند نرمال‌سازی شده

Employee ID	Department
E001	Accounting
E002	Sales
E003	Marketing
E004	Accounting

فرم نرمال سوم (3NF)

یک جدول فرم نرمال سوم را برآورده می‌کند اگر (علاوه بر برآورده کردن 2NF) شامل هیچ وابستگی تراگذر نباشد. وابستگی تراگذر زمانی رخ می‌دهد که ستون A به ستون B وابسته است، و ستون B به entity key وابسته است. اگر می‌خواهید طبق 3NF نرمال‌سازی کنید، باید ستون A را از جدول حذف کنید، چون به entity key به طور مستقیم وابسته نیست، و آن را در یک جدول متفاوت با entity key خود قرار دهید.

جدول سفارش‌ها

Order ID	Order date	Customer ID	Customer zip code
R001	01/17/2021	C032	99702
R002	03/01/2021	C004	39204
R003	06/30/2021	C054	06505
R004	08/22/2021	C010	84098
R005	09/27/2021	C004	39204

این جدول در فرم نرمال سوم نیست چون فیلد Customer zip code به Customer ID وابسته است، که entity key این جدول نیست (entity key اینجا Order ID است). ساختار فعلی ما می‌تواند منجر به از دست دادن ناخواسته اطلاعات شود؛ اگر مشتری C032 سفارش خود را برگرداند و نیاز به حذف این رکورد داشتیم، به طور ناخواسته اطلاعات کد پستی آن‌ها را از دست می‌دهیم. اگر مشتری C004 هرگز نقل مکان کند و کد پستی آن‌ها تغییر کند، همچنین باید آن را در دو مکان به‌روزرسانی کنیم، چون آن‌ها چندین سفارش انجام داده‌اند. برای آوردن این جدول به 3NF — حدس زدید — آن را به دو جدول تقسیم می‌کنیم.

جدول سفارش‌های نرمال‌سازی شده

Order ID	Order date	Customer ID
R001	01/17/2021	C032
R002	03/01/2021	C004
R003	06/30/2021	C054
R004	08/22/2021	C010
R005	09/27/2021	C004

جدول مشتریان نرمال‌سازی شده

Customer ID	Customer zip code
C032	99702
C004	39204
C054	06505
C010	84098

معایب نرمال‌سازی: چه زمانی denormalize کنیم

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