מאי31
Written by:
ronen ariely
31/05/2012 08:35 
מה יש לנו כאן?
* הקדמה
* המטרה
* רעיונות מהירים שהוצגו ונקודות בעייתיות הקשורות בהן
* הסברים מלאים ללוגיקה של הפתרונות
* הפתרונות
- פתרון ראשון
- פתרון שני
* קפוץ לסיום לקישורים מוכנים של הפתרונות להורדה
הקדמה
בבלוג זה נדון על מורכב קטנה שנוצרת בעבודה עם טבלאות הנמצאות בקשר יחיד לרבים. המצב הכי נפוץ בו יש לנו יחס בין טבלאות הוא בטח היחס יחד לרבים. למשל יחס בין טבלה של שאלות וטבלה של התשובות לכל שאלה. מדובר על מצב בו לכל שאלה יכולות להיות מספר תשובות. נעזר בדוגמה זו על מנת להציג נושאים מתקדמים יותר שהועלו בפורום MSDN. בבלוג זה נדון ביחסים בין טבלת שאלות וטבלת תשובות אפשריות. כאשר לכל שאלה ניתן להכניס מספר תשובות נכונות
החלק "המיוחד" בו נדון הוא דרישת הלקוח לקבוע לכל שאלה תשובה אחת כברירת המחדל.
הנושא עלה בדיון בפורום וקיבל כמה פתרונות מהירים בהתחלה. עם זה מכאן התחיל דיון יותר מעמיק יותר ביוזמתו של עמי על הפתרונות השונים. אני אציג בקצרה מאוד חלק מהערות שהועלו ואת הסיבה המרכזית לכך שהיה צורך במשהו אחר. פירוט מעמיק לגבי הנקדוות הבעייתיות של הפתרונות הראשונים אני אשאיר למישהו אחר (הבנתי שעמי מתכוון לכתוב על זה משהו). אני אשים דגש על הפתרון ולא על הבעיות בבלוג זה ואציג כאן בפירוט את הפתרונות שהצגתי בפורום.
המטרה
נתונה לנו טבלה של שאלות וטבלה של תשובות. הטבלאות מקושרות ביחס יחיד לרבים כך שלכל שאלה יכולות להיות כמה תשובות נכונות. ניתן לראות את המצב ב DDL הבא.
/*********************************************************
********************************** Questions-Answers: DDL
**********************************************************/
CREATE table Questions (
Q_ID int PRIMARY KEY not null identity,
Q_Str nvarchar(100)
);
CREATE TABLE Answers
(
A_ID int PRIMARY KEY NOT NULL identity,
A_Q_ID int REFERENCES Questions(Q_ID) NOT NULL,
A_Str nvarchar(100) NULL
);
GO
INSERT INTO Questions VALUES (N'Who'), (N'What')
INSERT INTO Answers VALUES (1, N'Clinton'), (1, N'Obama'), (2, N'Blue'), (2, N'red')
GO
SELECT * FROM Questions
SELECT * FROM Answers
GO
המטרה שלנו זה להוסף אפשרות של בחירת תשובה אחת כברירת מחדל לכל שאלה.
רעיונות מהירים שהוצגו ונקודות בעייתיות הקשורות בהן
הערה: אני לא מציג את כל הרעיונות שעלו בהכרח אלא רק את העיקריים בעיני כרגע לדיון
רעיון 1: הוספת עמודה בטבלת התשובות ושימוש באינדקס מפולטר
נוסיף לטבלת התשובות עמודה בשם IsDefault למשל אשר תקבל אכן true או flase. כמו כן נוסיף אינדקס ייחודי שיוודא שלא תהיה יותר מתשובה אחת שמסומנת כברירת המחדל לאותה שאלה. נבצע את זה בעזרת אינדקס שעושה שימוש בפילטר Where IsDefault=1 כפי שנראה בקוד:
ALTER TABLE Answers
ADD IsDefault bit
GO
Create Unique Index Idx_QuestionID On Answers(A_Q_ID) Where IsDefault=1;
GO
נקודות שעלו:
1. תאימות: מה קורה אם רוצים לעבוד עם גרסה 2005 של SQL או עם מסד נתונים שונה? מה אם רוצים פתרון שתואם לתקן ANSI של SQL? אנו רוצים פתרון פשוט שמתאים לכל סוגי מסדי הנתונים בנפוצים. אינדקס מפולטר אינו קיים בתקן.
2. מידע נוסף: אנו שומרים טור מידע עבור כל רשומה בטבלת התשובות ז"א מייצרים מידע כמספר התשובות שיש לכל שאלה.
3. כל שינוי מחייב שינוי של 2 רשומות נפרדות. שינוי הרשומה שהיתה קודם ברירת המחדל וכן שינוי הרשומה החדשה שרוצים כבררית מחדל.
ננקה את השינויים של רעיון זה
drop Index Idx_QuestionID On Answers
ALTER TABLE Answers DROP COLUMN IsDefault
GO
רעיון 2: הוספת עמודה של מספר תשובת ברירת המחדל לטבלת השאלות
נוסיף עמודה לטבלה של השאלות דווקא. בעמודה זו נשמור לכל שאלה את מספר תשובת ברירת המחדל שלה.
ALTER TABLE Questions ADD DefaultQuestionID int
GO
ללא ספק הרבה יותר פשוט, ללא אינדקסים מיוחדים ומייצרים רק נתון אחד עבור כל שאלה ולא עבור כל תשובה של כל שאלה. למעשה אני חייב לציין שזה הפתרון שקפץ לי אישית לראש (לא אני הצגתי אותו בפורום). מכאן שגם עדכון של הנתון אינו מחייב עדכון של כמה רשומות אלא רק של נתון בודד.
נקודות שעלו: בעייתיות של cyclic reference כאשר טבלה 1 מצביעה ל 2 ו 2 ל 1. תיאורטית הדבר יכול ליצור מגבלות בהמשך.
רעיון 3: הוספת שדה נוסף לטבלת התשובות ללא שימוש באינדקסים מיוחדים
רעיון זה הועלה על ידי ומתבסס על כך שבדרך כלל (אם לא תמיד) באפליקציות כשיש לנו מספר נתונים מוצגים בעמוד אנו בכל מקרה צריכי לקבוע מה להציג לפני מה, ולא לתת לשרת ה SQL לקבוע בשבילנו. במקרה שלנו יש מספר תשובות לכל שאלה והתשובות לא מסודרות לפי ID מסויים אלא נכנסות בצורה לא מסודרת ולא רציפה (ניתן הלכניס תשובה אחת לשאלה 1 ואז תשובה 2 לשאלה 2 ואז להוסיף תשובה 2 לשאלה 1 וכן הלאה). במקרה זה יהיה עלינו בכל מקרה לאפיין את המערכת מעט שונה והלוסיף שדה של ORDER לקביעת סדר הצגת התשובות (בהנחה שרוצים להציג יותר מתשובה אחת גם לפעמים אחרת למה המערכת מאפשרת יותר מתשובה אחת?!?). כך למשל בכל מערכת שאלונים שפיתחתי היה צורך בנתון זה של סדר התשובות להצגה. ניתן לראות מערכת לדוגמה בקישור הבא
לכן הרעיון היה לתפוס 2 ציפורים במכה ולתת פתרון מעשי לבעיה על ידי הוספת עמודת "עדיפות לתצוגה". מדובר על הוספת עמודה INT. פתרון זה כמובן יעבוד בכל מסד נתונים מכל סוג צורה וצבע. פתרון זה מוסיף לנו תכונה חשובה על הפתרונות האחרים שהוצגו וזה שהוא מונע מצב של שאלה עם תשובות אבל בלי ברירת מחדל, מכיוון שתמיד יש דירוג. בפתרון זה ניתן בקלות להוסיף תנאי ייחודיות על [טור הדירוג] +[טור מספר השאלה] ובכך לא לאפשר דירוגים זהים. מאפשר עדכון מהיר של רשומה אחת בלבד כדי לשנות ברירת מחדל (פשוט מעדכנים את העמודה שרוצים שתדורג למספר יותר גדול). לא קימת בעיית ה אין את בעיית cyclic reference של שמירת הנתון בטבלת השאלות.
יש לציין שכבר עם הצגת הפתרון גם הצגתי את נקודות של בעיות הקשורות בו כגון מידע נוסף ועבודה עם טור מסוג INT לעומת ביט כמו בפתרון הראשון. כמו כן על מנת לבצע עדכון עלינו לבדוק תחילה מה הדירוג המקסימלי כדי לבחור דירוג גבוהה יותר (אלא אם עובדים במסד נתונים המאפשר עבודה עם Sequence כמו sql 2012)
ALTER TABLE Answers ADD AnswerRank int
GO
הפתרונות
הערה: לא מדובר בפתרון מיטבי בהכרח לבעיה או לפתרון שהייתי נבוחר להשתמש בו בהכרח. בחירת הפתרון כמו תמיד תלויה ספציפית בצרכים שלנו ובאפיון של מסד הנתונים והנתונים.
אני אציג בכלליות את דרך המחשבה וההגעה אל הפתרון, כשנתחיל מהרעיון הכללי שעומד מאחורי הפתרונות ונעבור להצגת 2 פתרונות המתבססים על רעיון זה.
הרעיון הבסיסי שצץ לי כשראיתי את השאלה היה זהה למעשה לרעיון 2. עם תחילת הדיון והעלאת הבעיה של cyclic reference חיפשתי דרך למנוע מצב כזה. הפתרון שהעלתי היה פשוט: שימוש בטבלה נוספת שתהיה טבלה מקשרת בין טבלת השאלות לטבלת התשובות ותכיל את הקשר של תשובת ברירת המחדל עבור כל שאלה.
מבחינת ה DDL מדובר למעשה בקשר דומה לזה של יחס רבים לרבים. יחס רבים לרבים ממומש באמצעות טבלה מקשרת. ההבדל היחיד הוא שאצלנו נוסיף מגבלה של ייחודיות (UNIQUE). כך למעשה אנחנו מנוונים את האפשרות להכניס רשומות כפולות ומייצרים קשר של 1-1. פתרון זה מאפשר לנו להימנע ממצב של cyclic reference. אם כן נוסיף טבלה בטבלה שלישית עם טור מפתח חיצוני של מספר שאלה (מתוך טבלת השאלות), וטור מפתח חיצוני של מספר תשובה (מתוך טבלת התשובות). ככה נמנע המצב של טבלה אחת תלויה בטבלה 2 וטבלה 2 תלויה בטבלה 1.
החור הבא שנוצר לנו באפיון הוא כיצד נוכל למנוע בחירה של תשובה של שאלה מסויימת כאילו היא תשובה של שאלה אחרת. למשל במקרה של הנתונים שלנו אנחנו רוצים להימנע ממצב שבו מכניסים לטבלת ברירת המחדל (הטבלה המקשרת) את תשובה 3 כברירת מחדל לשאלה 1, מכיוון שתשובה 3 היא תשובה לשאלה 2.
הפתרון הראשון שאני מציג לבעיה זו נעשה על ידי הוספה של עמודה שלישית בטבלת הקשר שלנו. במקום לקחת מטבלת התשובות רק את מספר התשובה ניקח גם את מספר השאלה. נקודה זו היה לי קשה להבהיר בפורום אז אני רוצה להבהיר שוב את מבנה הטבלה שלנו ואת המקור של הנתונים שאנו נכנסי אליה
עמודה ראשונה: D_Q_ID
D מסמל Default
Q_ID מסמל שאנחנו נכניס לעמודה זו את הנתון של ה ID מתוך טבלת Questions
עמודה שנייה: D_A_ID
D מסמל Default
A_ID מסמל שאנחנו נכניס לעמודה זו את נתון של ה ID מתוך טבלת Answers
הערה: עד כאן אלו שני העמודות הרגילו בטבלת קשר של יחס רבים לרבים. וכאן מגיע הרעיון שעליו כל הפתרון מתבסס והוא הוספת העמודה השלישית.
עמודה שלישית: D_A_Q_ID
D מסמל Default
A_Q_ID מסמל שאנחנו נכניס לעמודה זו את נתון של ה Q_ID (מספר הID של השאלה) אבל מתוך טבלת Answers
עתה נבנה לעצמנו את התונים של רשומה חדשה שאנחנו רוצים להכניס. את הבנייה המעשית של הנתונים מבצעת האפליקציה שלאחר ש"תשיג" את הנתונים תוכל להריץ את השאילתה של הוספת רשומה חדשה או עדכון רשומה קיימת בטבלת הקשר שלנו.
דוגמה חוקית: נניח שאנחנו רוצים להוסיף את תשובה 2 כבררית מחדל של שאלה 1.
- לעמודה ראשונה אנחנו רוצים להכניס את הנתון של מספר השאלה שלה אנחנו מגדירים את ברירת המחדל. נתון זה לקוח מטבלת השאלות. נבדוק מה ה ID של השאלה שלנו ונראה שהוא כמובן 1, ולכן עמודה ראשונה צריכה לקבל את הערך 1.
- עמודה שנייה צריכה לקבל את הערך של ה ID של התשובה שלנו. נתון זה אמרנו שאנחנו לוקחים מטבלת התשובות ולכן אם נבדוק מה ה ID של התשובה השנייה נראה שהוא 2 כרגע (בשלב זה ה ID שלנו רציפים אבל המצב יכול להיות כמובן שונה).
- העמודה השלישית היא זו שצריך להבין לעומק. אנחנו רוצים להכניס נתון של מספר השאלה אבל לא כמו בעמודה הראשונה אנחנו הפעם רוצים לקחת את הנתון מתוך טבלת התשובות. ז"א נבדוק בטבלת התשובות שלנו מה ה ID של השאלה של תשובה שנייה? תשובה שנייה משוייכת לשאלה עם ID=1 ולכן עלינו לקחת ערך 1.
קיבלנו אם כן שעלינו להכניס את הנתונים 1,2,1
דוגמה לא חוקית: נניח שאנחנו רוצים להוסיף את תשובה 3 כבררית מחדל של שאלה 1.
- לעמודה ראשונה אנחנו רוצים להכניס את הנתון של מספר השאלה שלה אנחנו מגדירים את ברירת המחדל. נתון זה לקוח מטבלת השאלות. נבדוק מה ה ID של השאלה שלנו ונראה שהוא כמובן 1, ולכן עמודה ראשונה צריכה לקבל את הערך 1.
- עמודה שנייה צריכה לקבל את הערך של ה ID של התשובה שלנו. נתון זה נלקח מטבלת התשובות ולכן אם נבדוק מה ה ID של התשובה השלישית נראה שהוא 3.
- בעמודה השלישית אנחנו רוצים להכניס נתון של מספר השאלה שנלקח מתוך טבלת התשובות. ז"א נבדוק בטבלת התשובות שלנו מה ה ID של השאלה של תשובה שלישית? תשובה שלישית משוייכת לשאלה עם ID=2 ולכן עלינו לקחת ערך 2.
קיבלנו אם כן שעלינו להכניס את הנתונים 1,3,2
עתה אפשר כבר להבין אולי את הרעיון של העמודה השלישית. עתה כל מה שאנחנו צריכים כדי למנוע הכנסת תשובה לא מתאימה הוא לבדוק האם הערך בטור הראשון זהה לערך בטור השלישי. אם הערך זהה אז התשובה חוקית. המקור שלה הוא מתשובה של שאלה מתאימה.
אם כן בפתרון הראשון שלי אני מוסיף "טבלת קשר" בין 2 הטבלאות הקיימות ומבצע את הפעולות הבאות על טבלת הקשר:
- עמודה ראשנה עבור מספר השאלה מתוך טבלת השאלות על ידי הוספת מפתח חיצוני לעמודה המתאימה בטבלת השאלות
- עמודה שנייה עבור מספר התשובה מתוך טבלת התשובות ע"י הוספת מפתח חיצוני לעמודה המתאימה בטבלת התשובות
- עמודה שלישית היא עמודת INT רגילה בה נכניס את הערך המתאים של מספר השאלה הלקוח מתוך טבלת התשובות
- נוסיף תנאי CONSTRAINT פשוט שיתקיים
D_Q_ID = D_A_Q_ID ובכך פתרנו את הבעיה של הכנסת תשובה של שאלה X כברירת מחדל של שאלה אחרת Y. UNIQUE על עמודה ראשונה וכן על עמודה שנייה על מנהת לוודא שלא יהיה לנו יותר מברירת מחדל אחת עבור לכ שאלה (למעשה התנאי על עמודה שנייה מיותר מכוון שצירוף שני התנאים מעל מבטיח שלא יהיה תשובה אחת רשומה פעמיים... אחרי הכל תשובה יכולה עתה להירשם רק בשאלה המתאימה ולא יכול להיות יותר מרשומה אחת לכל שאלה... נשאיר את הקוד כפי שהוצג על ידי במקור בלשב זה)
(
D_Q_ID int REFERENCES Questions(Q_ID) NOT NULL,
D_A_ID int REFERENCES Answers(A_ID) NOT NULL,
D_A_Q_ID int NOT NULL -- כאן נכניס את הערך של מספר השאלה המתאים לתשובה שבחרנו
);
ALTER TABLE Default_Q_A ADD CONSTRAINT chk_Default CHECK (D_Q_ID = D_A_Q_ID)
ALTER TABLE Default_Q_A ADD UNIQUE (D_Q_ID)
ALTER TABLE Default_Q_A ADD UNIQUE (D_A_ID)
GO
נבדוק את מה שביצענו על ידי ניסיון להגדיר תשובות ברירת מחדל חוקיות ותשובות שאינן חוקיות ונוודא שהכל עובד כמצופה :-)
-- נכניס נתון חוקי לדוגמה
-- נבחר את תשובה 2 כברירת מחדל של שאלה 1 ונוסיף אותה לטבלה שלנו
-- אם נעקוב אחרי הדוגמאות בהסברים אז עלינו להכניס את הנתונים 1,2,1 לטבלה
INSERT INTO Default_Q_A VALUES (1, 2, 1)
SELECT * FROM Default_Q_A
GO
-- עתה ננסה להכניס נתון לא חוקי
-- ננסה לבחור תשובת ברירת מחדל נוספת לאותה שאלה ולכן נקבל שגיאה. יכול להיות רקר ברירת מחדל אחת לכל שאלה
INSERT INTO Default_Q_A VALUES (1, 1, 1)
SELECT * FROM Default_Q_A
GO
/*
Msg 2627, Level 14, State 1, Line 1
Violation of UNIQUE KEY constraint 'UQ__Default___053C2203377B55FE'. Cannot insert duplicate key in object 'dbo.Default_Q_A'. The duplicate key value is (1).
The statement has been terminated.
*/
-- עתה ננסה להכניס נתון לא חוקי
-- ננקה תחילה את התשובה הקודמת
-- נבחר את תשובה מספר 3 כברירת מחדל של שאלה ראשונה
-- אבל תשובה 3 שייכת לשאלה 2 ולכן נקבל שגיאה
delete Default_Q_A
INSERT INTO Default_Q_A VALUES (1, 3, 2)
GO
/*
Msg 547, Level 16, State 0, Line 2
The INSERT statement conflicted with the CHECK constraint "chk_Default". The conflict occurred in database "QQ", table "dbo.Default_Q_A".
The statement has been terminated.
*/
-- אם כן פתרון ראשון נראה שעובד טוב
אני חושב שפתרון זה קל יחסית להבנה כשעוקבים אחרי דרך המחשבה (אם כי פחות נקי אולי כפי שנראה בהמשך בהשוואה לפתרון ההשני שאציג). זהו קוד פשוט יחסית בלי אינדקסים או תנאים מורכבים והוא תואם לתקן ANSI של SQLבלי בעיה. לוגיקה זו אמורה לבעוד על כל מסד נתונים סביר. הוא קצר וניתן להבנה בצורה ברורה ישירות מהקוד של הטבלאות. עם זה החיסרון הגדול שהפריע לי הוא שהשליטה על הנתונים הנכנסים מנוהלת חיצונית (האפליקציה שצריכה להכניס את הנתון או המשתמש), והן נשלפים למעשה מטבלת התשובות.
* ערה: ניתן לראות בהסברים למעלה כמה נקודות לגבי יתרון וחסרון השיטה.
ננקה את השינויים שביצענו לטובת הפתרון כדי להציג את הפתרון השני
--CLEAN
/*
drop table Default_Q_A
*/
נעבור לפתרון השני
הקוד הבא מהווה שיפור מבחינה ניהול הנתונים. הוא מתבסס לחלוטין על הלוגיקה של הקוד מעל. הרעיון הכללי כאן הוא שבמקום להוסיף עמודה שלישית עבור נתון של "מה מספר השאלה לפי טבלת התשובות" הפעם אנחנו נעזר בבדיקה דינאמית כחלק מהתנאים שלנו פשוט על ידי הפעלת שאילתת SELECT פשוטה כחלק מהתנאי (CONSTRAINT) שאנו מפעילים על טבלת הקשר. ז"א במקום להשוות את עמודה ראשונה לעמודה שלישית שהיא נתון קבוע בטבלה אנחנו נשווה את העמודה הראשונה לתוצאה של בדיקה ישירה מול טבלת התשובות.
הקוד הבא יותר "נקי" וכאמור אינו עושה שימוש בטור שלישי בטבלת ברירת המחדל, אבל מצד שני הקוד הזה עושה שימוש בשאילתה פעילה בזמן הפעלת התנאי בעוד בקוד למעלה עשינו שימוש בנתון שנשמר לנו מקומית בטבלה המקשרת.
CREATE TABLE Default_Q_A
(
D_Q_ID int REFERENCES Questions(Q_ID) NOT NULL,
D_A_ID int REFERENCES Answers(A_ID) NOT NULL,
);
GO
CREATE FUNCTION [CONSTRAINT_Default_Q_A]
(
@AnswerID int
)
RETURNS INT
AS
BEGIN
declare @Out as int
select @Out = A_Q_ID from Answers where A_ID = @AnswerID
RETURN @Out
END
GO
ALTER TABLE Default_Q_A ADD CONSTRAINT chk_Default CHECK (D_Q_ID = [dbo].[CONSTRAINT_Default_Q_A](D_A_ID))
ALTER TABLE Default_Q_A ADD UNIQUE (D_Q_ID)
ALTER TABLE Default_Q_A ADD UNIQUE (D_A_ID)
GO
ושוב ניתן לבצע את הבדיקות ולהנות מהתוצאה :-)
-- עתה ננסה להכניס נתון לא חוקי
-- נבחר תשובת ברירת מחדל לשאלה ראשונה על ידי הוספה של הרשומה לטבלת ברירת המחדל
INSERT INTO Default_Q_A VALUES (1, 2)
SELECT * FROM Default_Q_A
GO
-- עתה ננסה להכניס נתון לא חוקי
-- ננסה לבחור תשובת ברירת מחדל נוספת לאותה שאלה ולכן נקבל שגיאה. יכול להיות רקר ברירת מחדל אחת לכל שאלה
INSERT INTO Default_Q_A VALUES (1, 1)
SELECT * FROM Default_Q_A
GO
/*
Msg 2627, Level 14, State 1, Line 1
Violation of UNIQUE KEY constraint 'UQ__Default___053C22036A095B8E'. Cannot insert duplicate key in object 'dbo.Default_Q_A'. The duplicate key value is (1).
The statement has been terminated.
*/
-- עתה ננסה להכניס נתון לא חוקי
-- ננקה קודם את הנתון הקודם
-- נבחר את תשובה מספר 3 כברירת מחדל של שאלה ראשונה
-- אבל תשובה 3 שייכת לשאלה 2 ולכן נקבל שגיאה
delete Default_Q_A
INSERT INTO Default_Q_A VALUES (1, 3)
GO
/*
Msg 547, Level 16, State 0, Line 2
The INSERT statement conflicted with the CHECK constraint "chk_Default". The conflict occurred in database "QQ", table "dbo.Default_Q_A".
The statement has been terminated.
*/
אני מקווה שההסבר מובן
מי שרוצה להוריד שאילתות מלאות יכול להוריד אותם מכאן:
תרמו חלק בדיון
אני נעזר בכמה מהרעיונות שהועלו על ידי אנשים בפורום MSDN וכמובן הנקודות הבעיתות שהוצגו על ידי עמי, שהובילו את הדיון המורכב יותר. אז כדאי לציין את האנשים שלקחו חלק בדיון ברעיונות או בהצגת נקודות בעייתיו ולהודות להם כאן. יש לציין שאני לא מציג את כל הרעיונות או כל הדעות וזה לא מטרת הבלוג. אני מציג רק את מה שנראה בעיני כמרכזי, נכון ו/או תורם לדיון בבלוג זה והיה לי כוח להוסיף אותו וכמובן את הפתרונות שלי שהצגתי בדיון המקורי בקצרה.
* גרי רשף
* גיא גלנצטר
* חיים פישנר
* עמי לוין