Follow up thread to Israeli Data Platform User Group meeting - SE7EN sins of db design RRS feed

  • שאלה

  • Link to Session Recording:

    Reply to this thread to provide your own examples of tables in your database which may or may not comply with these guidelines.Try and see for yourself what would happen to your queries, if you were to change the design to be more compliant.

    • A relational data model is a model of reality. The more accurately it reflect reality, the less work we’ll have to do.
    • Every relation represents one thing from the real world.
    • A tuple is a uniquely identifiable instance of that thing.
    • Every attribute describes an inherent property of that thing.
    • Identifying attributes can distinguish one thing from the other.
    • Every fact is represented in one place in the database, and always as an intersection of anattribute and a tuple.

    If you want to see a "real" database designed according to these principles, see my 'animal shelter' GitHub Repository @

    It's a generic sample database you are welcome to use for free, and even better - ask questions, provide feedback, or submit Pull Requests for improvements!

    Since last year, I use a subset of this database for examples in all my training classes:


    • נערך על-ידי AmiLevin יום שישי 20 נובמבר 2020 17:08
    • נערך על-ידי pituachMVP, Editor יום רביעי 25 נובמבר 2020 20:19 next attempt
    יום רביעי 18 נובמבר 2020 19:13

כל התגובות

  • אהלן עמי

    למה אתה לא עורך את הדיון בעברית?!?

    שים לב שהקישור בראש ההודעה לא עובד לי ואני מקבל שגיאה 404. תבדוק את הקישור

    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    יום שישי 20 נובמבר 2020 06:39
    מנחה דיון
  • כי אני אמריקאי :-) ?!?!

    תוקן, היה תו לא חוקי ] בסוף.

    יום שישי 20 נובמבר 2020 17:09
  • אנחנו עדיין רואים בך ישראלי

    פרט לכך שהפורום בעברית 😃

    הרצאה מעולה, כמו תמיד

    בהחלט מומלץ לצפות בהקלטה 👍

    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    שבת 21 נובמבר 2020 21:28
    מנחה דיון
  • Part 2 Highlights + links to additional reading:


    Model the real world, not the application or your concept of it.

    The application will change often, the real world much less so.
    A model that accurately reflects reality is the ultimate goal.
    If your model is not an accurate reflection, you will have to process the data to fit, and process it again when you read it. This is like trying to push a round object through a square hole.
    Redundant work will be needed both ways.

    For balance -


    If you try to squeeze 2 different things into one space, you'll need additional logic to distinguish these row 'types'. Much better is to be as specific as possible, even if it means ending up with many narrow tables. This eliminates the logic from all queries and significantly simplifies the code. 

    It also improves data consistency as different things will have different attributes, and you won't be able to enforce nullability - they will have to be NULL for the 'other type' of rows.

    Pro Tip - look for tables and columns with the word 'type' in their name.

    EAV is the 'ultimate' generalization. Unfortunately, it is in sharp opposition to the whole essence of the relational model. See 


    It is not a process. Normal forms are a tool, a gauge to validate your design. It does not replace proper relational modeling, and is not fool proof. If you follow the design principles we discussed, you'll naturally get 3NF and very often much higher than that, see the sample databases below with many tables in 6NF.

    "A man with 2 watches never knows the time".

    Never compromise data integrity for any performance gain. If you have applications whose requirements you can't meet, use a read-only pre-processed / flattened / aggregated / chewed / massaged / whatever copy.
    Don't compromise your only source of truth.

    Surrogate Keys (the magical, one size fits all "ROW ID")

    A surrogate key does not identify things in the real world. It is not an attribute of anything. It's a meaningless, arbitrary 'pointer' which is a mind set left over from the 1950's that everything must have a physical address or pointer.

    It doesn't. The relational model is a purely logical model. The users don't care what ID any row got. They want to see the 'real world' data, and must be able to distinguish between instances of 'things' based on their real world attributes. Using IDs takes away a significant part of the benefits that the RDM offers.

    Sample databases to get you started 'feeling' what a proper relational database looks like.

    Must read (short): 


    יום רביעי 25 נובמבר 2020 23:49
  • הי עמי,

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

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

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

    לדוגמא (וזאת רק דוגמא קטנה), אם אנחנו עובדים עם SQL Server, ואנחנו יוצרים מפתח ראשי עם שם הקמפוס ושם הבניין (בניגוד למספר רץ), אז ההכנסה של שורות לתוך האינדקס תהיה רנדומלית. זה יגרום להרבה Page Splits, וזה יכביד על פעולות ה-INSERT. זה גם יצור פרגמנטציה גבוהה, מה שיגדיל את כמות ה-Pages, את ה-IO, ובעיקר יכביד על פעולות שמבצעות Scan. באופן עקיף, אם יש לנו תהליך תחזוקה שמסתמך על רמות פרגמנטציה, אז הוא יצטרך לעבוד יותר קשה, להגדיל את הלוג, וכו' וכו'

    מצד שני, יש את כל היתרונות שתיארת בהרצאה, כמו העובדה שאפשר לחסוך בפעולות Join מיותרות, ואפשר לאכוף אילוצים במודל הנתונים בצורה יותר טובה.

    עכשיו השאלה היא מה יותר חשוב, ואיזה פתרון יותר מתאים. והתשובה היא אף פעם לא חד משמעית. התשובה היא כמעט תמיד "זה תלוי".

    זה תלוי במודל הנתונים. זה תלוי בפלטפורמה. זה תלוי ב-Workload. זה תלוי בדרישות הביצועים מהמערכת. זה תלוי…

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


    יום חמישי 26 נובמבר 2020 18:59
    מנחה דיון
  • תודה גיא!

    אתה מעלה נקודות חשובות שמטרידות רבים.

     "אם אנחנו עובדים עם SQL Server, ואנחנו יוצרים מפתח ראשי עם שם הקמפוס ושם הבניין (בניגוד למספר רץ), אז ההכנסה של שורות לתוך האינדקס תהיה רנדומלית. זה יגרום להרבה Page Splits, וזה יכביד על פעולות ה-INSERT."

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

    אבל, בוא נניח לרגע שההבדל הוא פי 100 לטובת IDENTITY. כמה טבלאות ב DB סובלות מ INSERT בקצב גבוה? 90 אחוז או חצי אחוז? מה עם כל השאר? יותר מזה... כל טבלה שיש לה הרבה INSERT, מן הסתם יש גם הרבה SELECT. אני לא מכיר טבלאות INSERT ONLY... אם אוכיח לך שלמרות שתשלם פי 100 ב INSERT, תרוויח פי 100 ב SELECT? אז זה שווה? ופי 1000?

    מה שאני אומר זה אל תפתור בעיות שלא קיימות ותוך כדי, למרות הכוונות הטובות, תייצר 10 בעיות חדשות, שרובן פי 1000 יותר חמורות מהבעיה המקורית (הדמיונית*) שניסית לפתור. תעשה את כל התכנון לפי הספר ותריץ בסוף Benchmark. אם תמצא טבלה שלא עומדת בדרישות לקצב הכנסה, והגעת למסקנה שהפתרון הנכון הוא לשים Clustered index על IDENTITY, תפאדל. רק תזכור לבדוק גם מה יקרה ל SELECT, וגם שזה לא אומר בהכרח שזה צריך להיות PK....

    "זה גם יצור פרגמנטציה גבוהה"

    הערך של כל המפתחות הטבעיים חייבים להיות מאונדקסים, גם כי הם מחייבים Unique Constraints וגם כי מה לעשות, באופן טבעי החיפושים יהיו עליהם. "תראה לי את המכירות של רמת גן" , לא "תראה לי את המכירות של עיר עם ID 3223". זה אומר שתצטרך להתמודד עם הפרגמנטציה בכל מקרה, זה שברירת המחדל היא שמפתח ראשי הוא גם Clustered שיותר כואב לעשות לו תחזוקה, זה אתגר שאינו קשור לעניין. אני טוען שברוב המקרים, ובעיקר עם מפתחות סדרתיים, ה PK צריך להיות Non Clustered. אפשר לעשות דיון גם על זה, אבל אין הצדקה לעקם את המודל בגלל שמיקרוסופט עשו החלטה לא אופטימלית, והם יודעים את זה. זה היה ה Lesser evil.... אם אתה יודע מה אתה עושה, אין הצדקה ליפול לברירת מחדל שאתה משלם עליהם מחיר יקר.

    "זה תלוי במודל הנתונים."

    נכון. כל מה שאני מדבר עליו רלוונטי למודל הרלציוני.

    אני לא מומחה ב Graph, Object Oriented, Hierarchical, Flat, Document, Key Value וכולי לכן איני יכול להביע דעה.

    "זה תלוי בפלטפורמה"

    פה אני לא מסכים. הטענה שלי היא שבשלב המודל והסכמה אופטימיזציה לפלטפורמה ספציפית תהיה שגויה מיסודה. מה שיהיה תלוי בפלטפורמה אלו האופטימיזציוית שתבצע לאחר שתבנה מודל קרוב ככל האפשר לאידיאל הרלציוני, ורק אם תגלה שיש בעיה ספציפית שהפתרון הנכון שלה הוא לעקם את הסכמה, ולקחת בחשבון את ההשלכות האפשריות. 
    בשנים האחרונות אני רואה יותר ויותר ארגונים נוטשים פלטפורמות ןעוברים ל PaaS. מ Oracle  ל SQL Server, מ SQL Server ל Postgres וכולי. כל האפליקציות שהיו Optimized לפלטפורמה סובלות קשה.... אני יודע ממקור ראשון :-) 

    "זה תלוי ב-Workload"

    אני מדבר אך ורק על ה Source of Truth שמשמש כמודל של העולם. אם יש לך Workload אחר שיכול להרוויח ממודל שאינו רלציוני, יש לך כמה פתרונות. אתה יכול לייצא עותק Read Only. אתה יכול לחשוף View במודל הרצוי, ואם צריך אז לעשות לו Materialize. יש המון פתרונות נקודתיים, והם עובדים מצויין כשהתשתית נכונה. 

    "זה תלוי בדרישות הביצועים מהמערכת"ראה ...

    הלוואי שיכולתי לחשוף לך נתוני אמת על לקוחות שלי שעשו את הקפיצה. הביצועים השתפרו בכמה סדרי גודל, ובאופן מפתיע, גם DML. יש לי לקוחות שירדו ממכונות של 80 מעבדים שעבדה ב 95%~ רוב הזמן, למכונה של 8 מעבדים, חצי זכרון, דיסקים דומים, שעכשיו עובדת בסביבת 25%~ רוב הזמן, וזמני התגובה של האפליקציה התקצרו ב 2 סדרי גודל.
    It's not a different league, it's a different ball game.

    יש כל כך הרבה השלכות לזה שהשאילתות הופכות להיות פשוטות, כמו פחות נעילות, הרבה פחות קומפילציות, גם הסטטיסטיקות הופכות להיות יותר משמעותיות. לדוגמא, שאילתה שמחפשת הזמנות מלקוחות קנדים. עם ID, השאילתא תעשה JOIN למדינות על פי ה ID, אבל הפילטר יהיה על "קנדה", שם המדינה. מכיוון שב Compile Time האופטימייזר לא יודע מה ה ID של קנדה, הסטטיסטיקות על Country ID חסרות משמעות. לעומת זאת, אם היית שומר את שם המדינה, האופטימייזר ידע באופן די מדויק כמה הזמנות יש מקנדה ויוכל לעשות החלטות יותר טובות.

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

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

    קחו קבוצה של כמה טבלאות, ותנסו לחשוב על מודל שיותר קרוב למה שאני מתאר. עכשיו קחו כמה שאילתות ותכתבו מחדש מול המודל החדש. ספרו לי מה קרה :-)

    יום חמישי 26 נובמבר 2020 22:52
  • שלום לכולם,

    לפני שאני מוסיף כמה נקודות לעניין הדיון, הנה קצת מנהלה לכל מי שמשתתף בדיון או רוצה להצטרף לדיון

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

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

    2. הפורום עוצב במקור לשפות Left To Right. יש תמיכה מלאה בשפות כמו עברית אבל מבחינה עיצובית כדי ליישר את כל ההודעות בפורום לימין הצוות של הפיתוח בחר בשיטה עצלנית ולא יעילה: כל העמוד של הפורומים מוגדר בכיוון בהתאם לכתובת ה URL של העמוד. ז"א שאם גולשים לפורום בעברית וה URL כולל He-IL אז כל ההדעות מוצמדו לימין

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

    מי שכותב באנגלית יכול לעבור ל mode עריכה של HTML ולהוסיף בתחילת ההודעה את הקוד 

    <div style="direction:ltr; text-align:left">

    ובסוף התוכן לסגור עם הקוד


    תוספת זו תעטוף את כל ההודעה שלכם בקביעת כיוון לשמאל

    שימו לב שזה מתאים רק אם אתם כותבים באנגלית

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

    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    יום שישי 27 נובמבר 2020 16:29
    מנחה דיון
  • רונן ויזהר:

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

    לרונן מגיעה עוד סליחה על שהנחתי שהוא מתנגד, בלי לבדוק לעומק! 


    תהיה בריאים וחזקים כולם!

    יום שישי 27 נובמבר 2020 19:33
  • עמי, תודה על התשובה המפורטת!

    בהחלט פתחת לי את הראש.

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

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

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

    יום ראשון 29 נובמבר 2020 08:40
    מנחה דיון
  • תגיד תודה לד"ר קוד, אני רק השליח :-)

    "המקרה הראשון הוא שאני מחפש הזמנות של לקוח ספציפי"

    נניח שהלקוח מזוהה בעולם האמיתי על ידי שם מלא + כתובת (טריויה - זה המפתח הסטנדרטי במוסדות אקדמיים בארה"ב*) ונניח לרגע שזה כל מה שצריך בדו"ח.

    אם השתמשנו ב "Customer ID", נצטרך או לבצע JOIN יקר ולמנוע מהאופטימייזר שימוש בהיסטוגרמות, או להריץ שתי שאילתות במקום אחת. בכל מקרה שילמנו ביוקר. זה כמו הדוגמא של המשתתפים בכנס, שהבהירה לכולם שהריצה לדף ה "LOOKUP" שהיה על הקיר מיותרת לחלוטין...
    אם השתמשנו ב שם + כתובת כמפתח, בכל מקרה נריץ רק שאילתה אחת, עם פילטר על ערכים אמיתיים והיסטוגרמות משמעותיות. זאת תהיה יותר פשוטה ויעילה.

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

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

    "המקרה השני הוא שאני מחפש הזמנות של קבוצה של לקוחות לפי מאפיינים של הלקוחות (שהם לא חלק מהמפתח הטבעי של הלקוח)."

    להפיק דו"ח הזמנות של לקוחות קנדיים עם Country ID + Customer ID, דורש את השלבים הלוגיים הבאים:

    1. לך לטבלת המדינות ותמצא מה ה ID של קנדה וכדאי שתזכור לייצר אינדקס על Country Name. זאת יכולה להיות שאילתא נפרדת, או JOIN / תת שאילתא.
    2. קח את ה ID שמצאת, ולך לטבלת הלקוחות. מצא את כל ה ID של הלקוחות שקוד המדינה שלהם תואם. אן עשית JOIN או תת-שאילתא, סטטיסטיקות לא יעזרו לך. עם עשית שאילתא נפרדת, שילמת ביוקר. גם פה כדאי שיהיה אינדקס, אבל על FK כדאי שיהיה ממילא.
    3. לך לטבלת ההזמנות ומצא את כל ההזמנות שקוד הלקוח תואם לאחד מאלה שמצאת בשלב 2. שוב, או JOIN או שאילתא נפרדת. ראה הערת אינדקס קודמת.

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

    1.  אין צורך לגשת לטבלת המדינות, אז לך ישר לטבלת הלקוחות ותסנן רק את הקנדים. האינדקס המתאים כבר קיים כ FK.
    2. לך לטבלת ההזמנות, ותסנן רק את אלה שתואמים לרשימה שמצאת בשלב 1. גם פה האינדקס כבר קיים כ PK.

    שים לב כמה עבודה מיותרת גרמו ה ID.
    ומה הרווחת? 5% בנפח הכולל ושיפור מינורי בביצועים של INSERT על שתי טבלאות מתוך 500 (גם זה לא...*)?
    ומה עם ה Data integrity שהלך לפח? ומה עם שינויים?

    ויש לי עוד הרבה דוגמאות. כדור השלג הזה בולע הכל.
    תזכיר לי מחר לדבר בקצרה על Bypass Joins ולהראות דוגמא של הסטטיסטיקות.
    במקרה השני אתה תראה שרוב האינדקסים כבר קיימים באופן טבעי בגלל ששמת PK ו UNIQUE על המפתחות. רק צריך להוסיף על FK (יש ERD שעושית את זה אוטומטית*) ועוד כמה נבחרים. כל מי שעשה איתי את הקפיצה גילה שפתאום צריך מקסימום אינדקס אחד או שניים על טבלאות מרכזיות, וברוב אין צורך בשום אינדקס חוץ מהמפתחות.

    עד שלא תנסו בעצמכם, לא תבינו.

    Hope it makes sense...

    יום ראשון 29 נובמבר 2020 15:47