איך לא ליפול בפח כשמתכננים מסד נתונים מסוג mySQL?

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

1. כל טבלה צריכה לתאר נושא אחד בלבד. לדוגמה, טבלה של עובדים צריכה להכיל את השם, תאריך הלידה, מספר הטלפון. אבל היא לא צריכה להכיל את פרטי הרכב שבו נוהג העובד את הרכבים נקבץ בטבלה אחרת.
2. אין טעם לפרק יותר מדי את הנתונים. לדוגמה, לפתוח טבלה לעובדים, טבלה למספרי טלפון של העובדים וטבלה לתאריכי לידה.
3. כל רשומה של טבלה צריכה שיהיה לה מספר סידורי primary key ייחודי שיזהה אותה מיתר הרשומות באותה טבלה. בדומה, למספר הזהות שלך שהוא ייחודי רק לך.
4. כל תא בטבלה צריך לתאר מידע אטומי (שאינו רשימה). אם אתה רואה שאחד התאים מכיל רשימה של ערכים זה רמז לכך שצריך לפתוח טבלה אחרת. לדוגמה, אם בטבלת העובדים אחת העמודות היא בונוסים שקיבל העובד אז אתה עלול להתפתות ולשים באותו תא את רשימת הבונוסים אותם קיבל העובד מדי חודש. עדיף לך ליצור טבלה חדשה של בונוסים עם הפניות באמצעות מפתחות זרים לעובדים.
5. אם אתה צריך להקליד שוב ושוב את אותו שם ברשומות שונות זה סימן שאתה צריך לפתוח טבלה אחרת. לדוגמה, אם בטבלת העובדים אתה צריך שוב ושוב להקליד עבור עובדים שונים את אותו מודל מכונית אז כדאי לך לפתוח טבלת מכוניות ולקשר אליה באמצעות מפתחות זרים.

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

יחסים בין טבלאות במסד נתונים

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

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

series

+----+------------------+
| id | name             |
+----+------------------+
|  1 | mySQL            |
|  2 | JavaScript       |
|  3 | Machine learning |
+----+------------------+

tutorials

+----+-------------------------+-----------+
| id | name                    | series_id |
+----+-------------------------+-----------+
|  1 | SQL database design     | 1         |
|  2 | Seaborn essentials      | 3         |
|  3 | Fetch API the right way | 2         |
+----+-------------------------+-----------+

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

buyers

+----+---------+
| id | name    |
+----+---------+
|  1 | Moshe   |
|  2 | David   |
|  3 | Joe     |
+----+---------+

orders

+----+------------+-----------+
| id | date       | buyer_id  |
+----+------------+-----------+
|  1 | 2020-01-01 | 1         |
|  2 | 2020-01-03 | 3         |
|  3 | 2020-01-12 | 3         |
|  4 | 2020-01-12 | 3         |
+----+------------+-----------+

3. יחס של רבים לרבים (N:N) אותו נסביר בחלקו הבא של המדריך.

שימוש בטבלת ציר ביחס של רבים לרבים

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

בדוגמה הבאה, הטבלה cars_workers היא טבלת הציר הקושרת בין הערכים בטבלת cars ו-worker:

cars

+----+---------+
| id | model   |
+----+---------+
|  1 | Talbot  |
|  2 | Sussita |
|  3 | Mustang |
+----+---------+

workers

+----+-----------+
| id | name      |
+----+-----------+
|  1 | Moshe     |
|  2 | Yechezkel |
|  3 | Yirmiyahu |
|  4 | Gershon   |
|  5 | Asher     |
+----+-----------+

cars_workers

+----+---------+------------+
| id | cars_id | workers_id |
+----+---------+------------+
|  1 |       2 |          1 |
|  2 |       2 |          5 |
|  3 |       1 |          3 |
|  4 |       3 |          3 |
+----+---------+------------+

אולי נראה לך בעייתי להבין באיזה מכונית נוסע כל עובד. נשתמש ב-JOIN (לקריאת מדריך JOIN) כדי לצרף את הטבלאות:

SELECT cw.id AS cw_id, name, w.id AS w_id, model, c.id AS c_id
FROM cars_workers AS cw
JOIN workers AS w
ON w.id = cw.workers_id
JOIN cars AS c
ON c.id = cw.cars_id;

התוצאה:

+-------+-----------+------+---------+------+
| cw_id | name      | w_id | model   | c_id |
+-------+-----------+------+---------+------+
|     1 | Moshe     |    1 | Sussita |    2 |
|     2 | Asher     |    5 | Sussita |    2 |
|     3 | Yirmiyahu |    3 | Talbot  |    1 |
|     4 | Yirmiyahu |    3 | Mustang |    3 |
+-------+-----------+------+---------+------+

איתור רשומות ללא מיפוי

אחרי שאנחנו יודעים אילו עובדים קיבלו רכבים אולי השאלה הבאה היא אילו עובדים לא קיבלו רכבים כי אנחנו לא רוצים לקפח אף אחד.

נכתוב שאילתה שתאפשר לנו לראות את רשימת כל העובדים בין אם קיבלו רכבים או שלא:

SELECT w.id AS w_id, name, cw.id AS cw_id
FROM workers AS w
LEFT JOIN cars_workers AS cw
ON w.id = cw.workers_id;

+------+-----------+-------+
| w_id | name      | cw_id |
+------+-----------+-------+
|    1 | Moshe     |     1 |
|    5 | Asher     |     2 |
|    3 | Yirmiyahu |     3 |
|    3 | Yirmiyahu |     4 |
|    2 | Yechezkel |  NULL |
|    4 | Gershon   |  NULL |
+------+-----------+-------+

אפשר לשפר את השאילתה עוד יותר אם נתמקד באותם העובדים שלא מופיעים בטבלת הציר באמצעות הוספת הפיסקה הבאה לשאילתה:

WHERE cw.id IS NULL

נכתוב את השאילתה במלואה:

SELECT w.id AS w_id, name
FROM workers AS w
LEFT JOIN cars_workers AS cw
ON w.id = cw.workers_id
WHERE cw.id IS NULL;

והתוצאה:

+------+-----------+
| w_id | name      |
+------+-----------+
|    2 | Yechezkel |
|    4 | Gershon   |
+------+-----------+

לכל מדריכי ה- mySQL

אהבתם? לא אהבתם? דרגו!

0 הצבעות, ממוצע 0 מתוך 5 כוכבים