הבלוג של גרי רשף

16/12/2010

השימוש ב-Surrogate Key (SK) – יתרונות וחסרונות

Filed under: Uncategorized — תגיות: , , , , — גרי רשף @ 15:05

יצרנו טבלת עובדים. באיזו עמודה נבחר בתור ה-Primary Key שלה, הנתון שיצביע באופן חד משמעי על העובד בטבלאות המקושרות כ-Foreign Key (ביחס 1:N) או בחיפוש בטבלה?

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

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

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

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

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

זו בקצרה אחת הסיבות להעדפת השימוש ב-SK מלאכותי על פני מפתח טבעי, כשהכוונה בדרך כלל למספרים שנוצרים אוטומטית בעמודת Identity בטבלה הנ"ל: מנגנון יצירת מספרי העובדים הפנימיים למערכת המידע שלנו יהיה עצמאי ובלתי תלוי בשגיונותיהם של משרד הפנים המנפיק תעודות זהות או של אגף משאבי האנוש בארגון. כמובן שנקצה בטבלה עמודות מתאימות למספר תעודות הזהות ולמספר העובד בארגון, אבל ה-Primary Key יהיה בשליטתנו המלאה.

דוגמה נוספת – יש לנו טבלת תאריכים מ-01/01/2000 ועד 31/12/2999 כמקובל במערכות OLAP. מה יהיה כאן ה-Primary Key?

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

האם גם כאן עלולות לצוץ בעיות? כנראה שכן: מה יקרה אם נרצה משיקולים שונים להוסיף לטבלה תאריכים כמו "ינואר 2010" (זה לא בדיוק תאריך אבל יש פעילויות שנמדדות ברזולוציה של חודשים ולא של ימים)? מה יקרה אם נרצה לצרף חלקי יממה? מה יקרה אם מיקרוסופט תשנה את ניהול התאריכים במערכות שלה וכעת הם לא יהיו מספרים שלמים?

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

גם כאן הפתרון יכול להיות עמודת Identity בטבלת התאריכים, ואז לא תהיה בעייה להוסיף לטבלה שורה עבור ינואר 2010 או עבור 16/12/2010 13:38:15, ובעמודות ה-Foreign Key בטבלת ה-Fact לא יופיעו תאריכים אלא מספרים הפונים לטבלת התאריכים.

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

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

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

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

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

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

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

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

מודעות פרסומת

להגיב »

עדיין אין תגובות.

RSS feed for comments on this post. TrackBack URI

להשאיר תגובה

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

הלוגו של WordPress.com

אתה מגיב באמצעות חשבון WordPress.com שלך. לצאת מהמערכת / לשנות )

תמונת Twitter

אתה מגיב באמצעות חשבון Twitter שלך. לצאת מהמערכת / לשנות )

תמונת Facebook

אתה מגיב באמצעות חשבון Facebook שלך. לצאת מהמערכת / לשנות )

תמונת גוגל פלוס

אתה מגיב באמצעות חשבון Google+ שלך. לצאת מהמערכת / לשנות )

מתחבר ל-%s

בלוג בוורדפרס.קום.

%d בלוגרים אהבו את זה: