עיצוב בסיס נתונים — ER, נורמליזציה ואינדקסים

מה זה?#

בשיעור הקודם למדנו את חמש פקודות ה-SQL הבסיסיות — CREATE TABLE, INSERT, SELECT, UPDATE, DELETE — על טבלה בודדת, פשוטה. אבל מה קורה כשיש כמה טבלאות שקשורות זו לזו (תלמידים וקורסים, למשל), ואיך בכלל מחליטים אילו עמודות שייכות לאיזו טבלה? טבלה מעוצבת גרוע (למשל שם תלמיד שחוזר בכל שורת ציון שלו) יוצרת סתירות — עדכון שם במקום אחד ולא באחרים. עיצוב DB נכון (ER Model, נורמליזציה) פותר את זה לפני שכותבים שורת CREATE TABLE אחת.

מילות מפתח שחשוב לזכור#

• ER Model (Entity-Relationship) — תרשים שמתאר את הישויות (טבלאות) והקשרים ביניהן, לפני כתיבת SQL

• Primary Key (PK) — עמודה (או כמה) שמזהה שורה ייחודית בטבלה, לעולם לא כפולה

• Foreign Key (FK) — עמודה בטבלה אחת שמצביעה ל-Primary Key בטבלה אחרת — זה ה"קשר" בפועל

• Normalization — פירוק נתונים לטבלאות כך שכל עובדה מאוחסנת פעם אחת בלבד, ללא כפילות

• Index — מבנה נתונים נוסף שמאיץ חיפוש בעמודה, במחיר מסוים על כתיבה

-- Before normalization — the student's name repeats in every row, easily contradicts itself
-- StudentGrades: StudentName | StudentEmail | CourseName | Grade
-- "Dana" | "dana@mail.com" | "Mathematics" | 95
-- "Dana" | "dana@mail.com" | "Physics"     | 88   -- duplication: name and email repeat

-- After normalization — every fact stored once
CREATE TABLE Students (
    Id INT PRIMARY KEY IDENTITY,
    Name NVARCHAR(100) NOT NULL,
    Email NVARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE Courses (
    Id INT PRIMARY KEY IDENTITY,
    Name NVARCHAR(100) NOT NULL
);

CREATE TABLE Grades (
    Id INT PRIMARY KEY IDENTITY,
    StudentId INT NOT NULL,
    CourseId INT NOT NULL,
    Score INT NOT NULL,
    FOREIGN KEY (StudentId) REFERENCES Students(Id) ON DELETE CASCADE,
    FOREIGN KEY (CourseId) REFERENCES Courses(Id)
);

-- Index — speeds up searching by Email (e.g. during login)
CREATE INDEX IX_Students_Email ON Students(Email);

  erDiagram
    STUDENTS ||--o{ GRADES : "receives"
    COURSES ||--o{ GRADES : "includes"
    STUDENTS {
        int Id PK
        string Name
        string Email
    }
    COURSES {
        int Id PK
        string Name
    }
    GRADES {
        int Id PK
        int StudentId FK
        int CourseId FK
        int Score
    }

הסבר עיקרי#

נורמליזציה פותרת סתירות, לא רק “יופי” — בטבלה הלא-מנורמלת, אם דנה מעדכנת את המייל שלה, צריך לעדכן כל שורה שבה שמה מופיע — ואם שוכחים שורה אחת, יש שני מיילים “נכונים” סותרים לאותו תלמיד. אחרי נורמליזציה, המייל קיים פעם אחת בטבלת Students — עדכון במקום אחד, תמיד עקבי.

FK ... ON DELETE CASCADE — כשמוחקים תלמיד, מה קורה לציונים שלו? ON DELETE CASCADE אומר “מחק אוטומטית גם את כל השורות הקשורות ב-Grades” — בלי זה, מחיקת תלמיד עם ציונים קיימים הייתה נכשלת (הפרת FK constraint) או משאירה ציונים “יתומים” שמצביעים לתלמיד שכבר לא קיים.

Index הוא Trade-off, לא “תמיד כדאי” — Index על Email הופך WHERE Email = '...' למהיר בהרבה (כמו אינדקס בסוף ספר, לא לדפדף עמוד-עמוד) — אבל כל INSERT/UPDATE חייב גם לעדכן את ה-Index, כך שיותר מדי אינדקסים מאטים כתיבה. מוסיפים Index על עמודות שמחפשים בהן הרבה, לא על כל עמודה “ליתר ביטחון”.

יתרונות#

נורמליזציה מונעת סתירות נתונים (אותה עובדה, ערכים שונים במקומות שונים); FK אוכף שלמות קשרים ברמת מסד הנתונים עצמו, לא רק בקוד; Index מאיץ שאילתות חיפוש משמעותית על טבלאות גדולות.

חסרונות#

נורמליזציה מוגזמת (“עד הסוף”) דורשת יותר JOIN-ים לשליפת נתונים שלמים — trade-off ביצועים; יותר מדי אינדקסים מאטים כתיבה (INSERT/UPDATE/DELETE) ותופסים שטח דיסק נוסף.

נקודות חשובות#

• PK מזהה שורה ייחודית; FK מצביע ל-PK בטבלה אחרת — זה מה שיוצר את הקשר

• נורמליזציה = כל עובדה מאוחסנת פעם אחת בלבד, מונעת סתירות עדכון

• ON DELETE CASCADE מוחק אוטומטית שורות תלויות; בלעדיו, מחיקה עם קשרים קיימים עלולה להיכשל

• Index מאיץ חיפוש אך מאט כתיבה — לא מוסיפים על כל עמודה סתם

טעויות נפוצות#

• לשמור נתונים חוזרים (כמו שם ומייל) בכל שורה במקום בטבלה נפרדת עם FK — מוביל לסתירות

• לשכוח ON DELETE CASCADE (או להגדיר אותו בטעות איפה שלא רצוי) ולקבל שגיאות FK בלתי-צפויות במחיקה

• להוסיף Index על כל עמודה “כדי שיהיה מהיר”, בלי לחשוב על עלות הכתיבה

סיכום#

ER Model מתאר ישויות וקשרים לפני כתיבת SQL. Primary Key מזהה שורה ייחודית; Foreign Key יוצר קשר בין טבלאות, עם אפשרות ל-ON DELETE CASCADE לניהול מחיקות תלויות אוטומטית. נורמליזציה מפרקת נתונים כך שכל עובדה מאוחסנת פעם אחת בלבד, מונעת סתירות עדכון. Index מאיץ חיפוש בעמודות נפוצות-לחיפוש, במחיר מסוים על מהירות כתיבה — trade-off מודע, לא ברירת מחדל אוטומטית.

דוקומנטציה רשמית#

Microsoft Learn — Database normalization basics

תרגילים#

תרגיל 1 — זיהוי כפילות#

המשימה: קחו טבלה (OrderId, CustomerName, CustomerEmail, ProductName, Price) שמייצגת הזמנות, וזהו אילו עמודות חוזרות על עצמן בין שורות — פרקו אותה ל-3 טבלאות מנורמלות עם FK מתאימים.

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

תרגיל 2 — ER Diagram#

המשימה: ציירו (על נייר או בכלי דיאגרמות) ER Diagram למערכת “ספרייה” עם Books, Members, ו-Loans (השאלות), כולל PK/FK בכל טבלה.

בדיקה: הדיאגרם מראה נכון שה-Loans מקשרת בין Book ל-Member (יחס many-to-many דרך טבלת קשר).

פרויקט מסכם#

המשימה: תכננו ויישמו (עם CREATE TABLE) סכמת DB מנורמלת למערכת “בלוג” עם פוסטים, תגובות ומחברים.

דרישות:

1. שלוש טבלאות לפחות: Authors, Posts, Comments — עם PK בכל אחת
2. FK מ-Posts ל-Authors, ומ-Comments ל-Posts (ואופציונלית גם ל-Authors)
3. ON DELETE CASCADE על Comments כשפוסט נמחק, אך לא על Posts כשמחבר נמחק (דיון: איזו התנהגות הגיונית יותר עסקית, ולמה)

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

מה בפרק הבא#

בפרק הבא — EF Core: איך ממפים את הטבלאות שתכננו כאן למחלקות C#, עם DbContext, DbSet ו-Migrations — בלי לכתוב CREATE TABLE ידני.