درس دوم- نرمال سازی و طراحی پایگاه داده‌ها

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

 

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

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

اگر بخواهم تعریف دیگری از نرمال سازی بدهیم می‌گوییم: نرمال سازی یک پایگاه، پروسه‌ای است از تغییر ساختار پایگاه، بر اساس یکسری قوانین که به آنها فرم می‌گویند.

 

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

Primary Key یا کلید اصلی: این کلید را در دوره قبلی مورد استفاده قرار دادیم که با استفاده از آن برای ستونی آنرا مشخصات ویژه‌ای می‌دهد. این مشخصات شامل:

• داشتن مقدار در هر شرایطی
• تغییر نکردن مقدار
• منحصر به فرد بودن مقدار

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

Foreign Key یا کلید خارجی (فرعی): این کلید همان کلید اصلی است که در جدول دیگری قرار می‌گیرد. بطور مثال اگر شما جدولی برای نگهداری اطلاعات مربوط به هر کاربر دارید (کاربران = users) و جدول دیگری برای نگهداری نوشته‌های هر کاربر (posts = مطالب). اگر در جدول کاربران ستونی به نام ID برای شماره گذاردن هر کاربر داشته باشید و این ستون را کلید اصلی این جدول کنید در جدول مطالب هم ستونی دقیقا با همان مشخصات می‌سازید. ستون ID در جدول کاربران کلید اصلی و در جدول مطالب کلید فرعی می‌شود. حال ارتباطی بین دو جدول توسط این کلید‌ها برقرار می‌شود که کار ارسال و دریافت داده‌ها را بهتر می‌کند. مثلا فرض کنید می‌خواهید مطلب خاصی را با نام نویسنده آن مطلب نمایش دهید. وجود کلید اصلی و فرعی این اجازه را به شما می‌دهد که با ارتباط بین آنها نویسنده و مطلب را دریافت کنید. بطور کلی وجود کلید اصلی و فرعی برای ساختن یک پیوند بین دو جدول با اطلاعات مختلف اما مرتبط معنی پیدا می‌کند.

نکته ۱: هر جدول تنها می‌تواند یک کلید اصلی داشته باشد.

نکته ۲: بهترین نوع کلید اصلی همان ساخت ستونی با مقدار عدد صحیح مانند ستون ID  در مثال ما است.

 

رابطه بین جدول‌ها
رابطه بین جدول‌ها بر اساس ارتباط داده‌های دو جدول است، در مثال کاربران و مطالب بطور مسلم رابطه‌ای بین کاربر و مطلب نوشته شده توسط خود او وجود دارد.

بطور کلی سه نوع رابطه جدول‌ها وجود دارد:

• یک به یک
• یک به همه
• همه به همه

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

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

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

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

سمبل‌های معرفی کننده رابطه‌ها

 

مراحل نرمال سازی
در انجام عمل نرمال سازی این عمل به چند قسمت تقسیم می‌شود. یعنی پدیده نرمال سازی در چند مرحله پیاده سازی می‌شود.

• مرحله (فرم) نخست نرمال سازی
• مرحله (فرم) دوم نرمال سازی
• مرحله (فرم) سوم نرمال سازی

مرحله نخست نرمال سازی
در مرحله نخستین نرمال سازی، قوانین زیر باید در جداول اعمال شود تا ما پایگاه نرمال شده با مرحله ابتدایی داشته باشیم:

۱. هر ستون باید تنها یک مقدار داشته باشد، یعنی شما نباید ستونی به اسم "نام" داشته باشید که نام و نام خانوادگی در آن قرار گیرد. در اصل شما باید این ستون را با ساخت ستون دیگری به دو ستون "نام" و "نام خانوادگی" تبدیل کنید.

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

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

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

نکته: یک راه برای بررسی جداول از نظر بر آوردن مرحله دوم نرمال سازی، این است که آیا رابطه همه به همه بین دو جدول وجود دارد یا نه. اگر این رابطه وجود داشت جداول نیاز به باز بینی دارند.

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

 

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

CREATE TABLE نام جدول (

.

.

) ENGINE = نوع جدول

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

نکته: در صورت عدم اعمال نوع به جدول، نوع پیش فرض اعمال می‌شود، که با توجه به نسخه پایگاه یکی از دو مورد MyISAM یا INNODB است.

 

زبان‌ها در پایگاه داده‌ها
دو فاکتور مهم دیگر در زمان ساخت پایگاه انتخاب نوع رمزگذاری یاEncoding  و تطبیق یا  Collate هستند. این دو مفهوم برای مشخص کردن نوع کاراکتر‌هایی (زبانی که در پایگاه نوشته می‌شود مثلن پارسی یا انگلیسی) که در پایگاه ثبت می‌شوند استفاده می‌شوند.

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

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

CREATE DATABASE نام پایگاه     CHARACTER SET        نوع کاراکتر    

CREATE DATABASE   users   CHARACTER SET    utf8

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

CREATE DATABASE نام پایگاه     CHARACTER SET        نوع کاراکتر     COLLATE نام تطبیق  

CREATE DATABASE   users   CHARACTER SET    utf8      COLLATE      utf8_general_ci

 

در درس بعدی به سراغ مفاهیم خیلی پیشرفته در SQL می‌رویم تا بتوانیم در ادامه یادگیری پی‌اچ‌پی از آنها استفاده کنیم.