נורמליזציה של מסדי נתונים

במאמר הקודם למדנו על מודלים של מסדי נתונים ובמיוחד על מודל Relational Database וראינו סכמה של מסד נתונים לדוגמא עם יחס של יחיד לרבים ורבים לרבים.

למודל ה-Relational database יש יתרונות רבים וכדי להנות מהם אנו חייבים ל"נרמל" את מסד הנתונים שלנו.מה זה לנרמל? להעביר את מסד הנתונים תהליך מסוים שנקרא נורמליזציה שבסופו אנו נשיג את המטרות הבאות:

1. מניעת כפילויות של מידע מיותר.
2. מניעת תלויות פונקציונליות לא רלוונטיות.
3. טבלאות רלוונטיות יותר שגורמות למסד הנתונים שלנו להפוך לעמיד ולגמיש יותר.

ישנן כמה וכמה צעדי נורמליזציה שמביאים אותנו ממסד נתונים לא מנורמל למסד נתונים שעבר נורמליזציה. בתיאוריה ישנם 3 צעדי נורמליזציה בסיסיים ועוד צעדים נוספים ומורכבים שבאים לאחר 3 צעדי הנורמליזציה הראשונים. בעולם האמיתי, או יותר נכון בעולם ה-OLTP, אנו משתמשים בשלושת צעדי הנורמליזציה הראשונים ולפעמים אפילו משמיטים את הצעד השלישי. למה? כיוון שצעדי הנורמליזציה יוצרים בדרך כלל מספר טבלאות שאותן יש לקשר זו לזו עם JOIN ויותר מדי JOIN גורמים להאטה רצחנית של מסד הנתונים. אבל על כך בהמשך.

הרציונל מאחורי צעדי הנורמליזציה היא לקחת מסד נתונים לא מנורמל ולהפוך אותו לנקי מאנומליות (מה זה אנומליות? בהמשך נדע). כמובן שכאשר אנו באים לעצב מסד נתונים מאפס, אנחנו לא צריכים לבנות אותו כמסד נתונים לא מנורמל ולהעביר אותו את הויה דלורוזה של הנורמליזציה אלא להתחשב בכל העקרונות המנחים של הנורמליזציה.

הצעד הראשון של הנורמליזציה 1NF

הנורמליזציה מדרגה ראשונה היא פשוטה ביותר. כל מה שעלינו לעשות זה לדאוג לדברים הבאים:

1. ערך אחד לתא אחד (אטומיות). זה די פשוט, אין מצב ביקום שיהיו כמה ערכים בתוך שדה אחד. כך למשל לא נכניס גם את שמות הילדים של אותו אדם בתא אחד).
2. נתונים דינמיים לא יופיעו כערך בשדות (כלומר נתונים שאפשר לחשב אותם בקלות בעזרת שאילתה). כך למשל לא יופיע תאריך יום הולדת של מישהו בשדה אחד ובשדה השני את הגיל שלו.
3. תלות פונקציונלית של כל מרכיבי הטבלה במפתח – זה קצת מורכב להבנה:
אנו מבודדים מפתח בטבלה ממקמים אותו בצד שמאל של הטבלה וגורמים לכך ששאר התאים בטבלה יהיו תלויים בו ובו בלבד. כלומר, יש יחסי תלות בין השדה הראשון בטבלה לבין שאר השדות בטבלה. שדות שלא תלויים בשדה הראשון (המפתח) יזוזו לטבלה אחרת ואנו נעשה את הקישור ביניהם עם JOIN.

הכל פשוט יותר עם דוגמא. בואו ונניח שיש לי טבלה של מנוים בחדר כושר עם הקורסים שלהם והעלויות שלהם:

+-------------+------------+------+-----------+---------------------------+
| costumer_id | activities | cost | location  | contact                   |
+-------------+------------+------+-----------+---------------------------+
|           1 | swim       |  200 | pool      | Kaplan street, 03-1234567 |
|           2 | golf       |  300 | golf club | Moshe street, 03-1222354  |
|           3 | swim       |  200 | pool      | David street, 03-1673234  |
|           4 | chess      |  100 | hall      | Noway street, 09-4355688  |
+-------------+------------+------+-----------+---------------------------+

לכאורה, אחלה של טבלה. למה להתחיל עם נורמליזציות וברדקים? אבל יש בטבלה הזו כמה פגמים מהותיים מאד. ראשית, אני לא יכול להכניס פעילות בלי לקוחות. קצת בעייתי אם אני רוצה לפתוח פעילות חדשה… שנית, אם אני מוחק את לקוח מספר 1, גם פעילות הסקי נמחקת לי. שלישית, אם אני רוצה לעדכן מחיר של פעילות, אני עלול להסתבך עם השאילתא לא נוסחה כמו שצריך (שלא לדבר על זה שצריך לעדכן מלא שורות…).
כל הבעיות האלו נקראות אנומליות – הבעיה הראשונה של חוסר אפשרות להכנסת נתון כלשהו ללא נתון אחר שאינו רלוונטי אליו נקרא Insertion Anomaly, הבעיה השניה של מחיקת נתון שגוררת מחיקה של נתון אחר שאינו תלוי בו נקראת Delete Anomaly והבעיה השלישית של עדכון המחיר נקראת Update Anomaly.

מה הפתרון?
קודם כל אנחנו לוקחים את שדה contact ונפריד אותו לשני שדות נפרדים – טלפון וכתובת. הדבר הזה יאפשר לנו לחפש לקוח רק לפי טלפון בלי צורך לכתוב שאילתא מסובכת מאד. אחרי שעשינו את זה, מילאנו את התנאי הראשון של 1NF.
התנאי השני של 1NF מבקש שלא יהיו לנו נתונים שאפשר למצוא עם שאילתא (נתתי כדוגמא תאריך לידה וגיל) – אבל בדוגמא שלנו אין את הדבר הזה.
התנאי השלישי הוא מעט יותר קשה להבנה – בדיוק כפי שציינו קודם, נבודד מפתח בטבלה, נזיז אותו לצד שמאל ונעיף מהטבלה שלנו כל דבר שאינו תלוי במפתח שלנו.
יש לנו 5 שדות בטבלה. הראשון הוא מספר לקוח. האם הפעילות תלויה במספר הלקוח? כן. האם המחיר של הפעילות תלוי במספר הלקוח? התשובה היא לא. מחיר הפעילות תלוי בפעילות. לפיכך מחיר הפעילות עף מהטבלה שלנו. נשארת לנו טבלה עם ארבע שדות – מספר לקוח, שם פעילות של הלקוח, מקום הפעילות של הלקוח (תלוי בפעילות הלקוח), כתובת ומספר טלפון.
נשאר המחיר שייכנס לטבלה משלו. במה הוא תלוי? ובכן, הוא תלוי בפעילות. ניצור טבלה שבה המפתח הוא הפעילות.

מה שיש לנו הוא מסד נתונים שבו יש שתי טבלאות המקושרות ביניהן באמצעות הפעילות – one to many. כך נראית הסכמה לפני ואחרי:

וכך זה נראה בפועל:

טבלת הלקוחות:

+-------------+------------+-----------+---------------+------------+
| costumer_id | activities | location  | street        | telephone  |
+-------------+------------+-----------+---------------+------------+
|           1 | swim       | pool      | Kaplan street | 03-1234567 |
|           2 | golf       | golf club | Moshe street  | 03-1222354 |
|           3 | swim       | pool      | David street  | 03-1673234 |
|           4 | chess      | hall      | Noway street  | 09-4355688 |
+-------------+------------+-----------+---------------+------------+

טבלת הפעילויות

+------------+------+
| activities | cost |
+------------+------+
| golf       |  300 |
| swim       |  200 |
| chess      |  100 |
+------------+------+

הקישור ביניהן נעשה כמובן עם JOIN שאותו אנו כבר אמורים להכיר. לדוגמא, אם אני רוצה לדעת כמה לקוח מספר 3 משלם. כל מה שעלי לעשות זה להריץ את השאילתא הבאה:

SELECT activities.cost FROM activities INNER JOIN costumers ON activities.activities=costumers.activities AND costumers.costumer_id = '3';

ותתקבל התוצאה:

+------+
| cost |
+------+
|  200 |
+------+

שהיא כמובן התוצאה הנכונה.

הצעד השני של הנורמליזציה 2NF

כאשר אנו מבצעים נורמליזציה מסדר גודל שני, אנחנו חייבים להשתמש במסד נתונים שנמצא כבר בדרגת 1NF.
השלב הזה יותר פשוט וכל מה שהוא דורש שלא יהיו בטבלה כלשהי שדות כפולים שלא תלויים במפתח או לפחות בחלק ממנו. כך למשל יש לנו בטבלת הדוגמא שלנו את המיקום של הפעילות שאינו תלוי במפתח (מספר הלקוח) וכן הוא מופיע מספר פעמים (שימו לב לכפילות במקרה של פעילות שחיה). על מנת לחסוך במקום, יותר כדאי שנעביר אותו לטבלת הפעילויות:

הצעד השלישי של הנורמליזציה 3NF

מדובר בצעד שצריך להזהר איתו במסדי נתונים מסוג OLPT כיוון שהעלות של כל JOIN מתחילה להיות יקרה. באופן עקרוני, בצעד הזה אנו מסירים כל שדה שאינו תלוי במפתח הראשי של הטבלה. אם בדוגמא שלנו במקום סתם שם של רחוב היתה עיר, מיקוד ורחוב – אז ב-2NF לא היתה לנו צריכה להיות בעיה כי גם העיר, גם המיקוד וגם הרחוב תלויים בשם הלקוח. אבל הצעד השלישי מבקש תלות ישירה וברור ששם העיר יכול להקבע על ידי המיקוד. הסכמה הבאה תדגים את הכל בצורה טובה:

באפליקציה אמיתית, לא בטוח שהנורמליזציה מסדר גודל שלישי תהיה שווה לי אם בכל פעם שאני שארצה להריץ בדיקה של כתובת הלקוח אני אצטרך להשתמש ב-JOIN שכאמור לוקח זמן יקר שפשוט אין לנו באפליקציות רשת ואם אני אגיע למצב שיש לי כמה וכמה JOINים בשאילתא, זה יכול לתקוע לי את מסד הנתונים. לפיכך גם אני לא נכנס לנורמליזציות גבוהות יותר – כי פשוט אין טעם לעשות את זה ב-MySQL שמשמש לאפליקציות ווביות.

באופן עקרוני, יותר מאשר חשוב לזכור את המשמעות האקדמית של הנורמליזציה, יותר חשוב לזכור את העיקרון – פריט מידע אחד לשדה, תלות פונקציונלית במפתח שבחרתם, בדיקת האנומליות כדי לראות שבמקרה של שינויים לא תצטרכו להחריב/להרחיב את הטבלאות שלכם ותמיד לזכור את המחיר הכבד שיש לנורמליזציה – כל JOIN ו-JOIN יכול לדפוק לכם את זמן התגובה.

⚠️המדריכים כאן הם טעימה בלבד! יש ספר מקיף המלמד MySQL מרמת בסיס ועד רמה מתקדמת. הספר הדיגיטלי, שרלוונטי גם לכל מסדי הנתונים שעובדים עם SQL – זמין כאן כחלק מהפרויקט "ללמוד ג'אווהסקריפט בעברית" – זו הדרך הטובה ביותר ללמוד באופן מקיף MySQL.

במאמר הבא אנו נמשיך ונלמד על אינדקסים מונח שבו רק נגענו במאמר הזה ועל היישום שלו ב-MySQL.