SQL | ערכים מרובים בעמודה

[dovid]

@WWW נזכרתי בעוד דרך מקובלת כשמדובר בכמות גדולה של תכונות, ובפרט אם היא דינמית
לעשות טבלה אחת יחידה עם שלושה עמודות, עמודת סוג, עמודת ערך ועמודת מפתח זר לעובד. וככה אפשר להכניס בעצם אינסוף ערכים אפשריים, כשכל ערך ממלא תא שלם, וגם השאילתות מהירות.
קוראים לדרך הזו EAT
https://en.wikipedia.org/wiki/Entity–attribute–value_model
https://blog.greglow.com/2018/02/19/sql-design-entity-attribute-value-tables-part-2-pros-cons/
אגב, יש כאלה (וורדפרס למשל) שעושים את הטבלה הזאת עם עמודה אחת ומשרשרים בטקסט אחד את הכל, למשל
עובד157.משמרת.2
עובד157.משמרת.3

[avi rz]

@dovid אמר בSQL | ערכים מרובים בעמודה:

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

זה טבלה אחת בשביל הערכים,
ומה עם האפשרויות לכל סוג, כמו ש
@www אמר בSQL | ערכים מרובים בעמודה:

כי אחד יכתוב עבריית ואחד עברית,

לזה יצטרכו עוד טבלה לכאורה שיש בה סוג וערך אפשרות שהוא מגדיר מראש, טעיתי? אולי רעיון אחר?

[WWW]

@dovid אמר בSQL | ערכים מרובים בעמודה:

https://blog.greglow.com/2018/02/19/sql-design-entity-attribute-value-tables-part-2-pros-cons/

פה הוא כותב נגד זה, או שלא הבנתי?

[WWW]

@dovid אמר בSQL | ערכים מרובים בעמודה:

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

ומפתח חיצוני.

בסדר.
מה קורה לגבי לחייב עדכון יום עבודה 1 לפחות? (אילו זה היה בלי טבלה נוספת, אז פשוט הייתי עושה NOT NULL). אני מנחש שתגיד לי להגדיר אילוץ, אין משהו יותר פשוט?

[איש נחמד]

@www אמר בSQL | ערכים מרובים בעמודה:

@dovid אמר בSQL | ערכים מרובים בעמודה:

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

ומפתח חיצוני.

בסדר.
מה קורה לגבי לחייב עדכון יום עבודה 1 לפחות? (אילו זה היה בלי טבלה נוספת, אז פשוט הייתי עושה NOT NULL). אני מנחש שתגיד לי להגדיר אילוץ, אין משהו יותר פשוט?

יש לך את האפשרות לבדוק את זה בקוד לפני שאתה שולח את השאילתה.

[WWW]

@איש-נחמד אמר בSQL | ערכים מרובים בעמודה:

יש לך את האפשרות לבדוק את זה בקוד לפני שאתה שולח את השאילתה.

נו... זה יש לי אפשרות בכל דבר.
ראה בקישור ש @dovid הביא לעיל, מה שהוא כותב על זה.

[dovid]

@www אמר בSQL | ערכים מרובים בעמודה:

@dovid אמר בSQL | ערכים מרובים בעמודה:

https://blog.greglow.com/2018/02/19/sql-design-entity-attribute-value-tables-part-2-pros-cons/

פה הוא כותב נגד זה, או שלא הבנתי?

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

[WWW]

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

מה לגבי זה:

@www אמר בSQL | ערכים מרובים בעמודה:

מה קורה לגבי לחייב עדכון יום עבודה 1 לפחות? (אילו זה היה בלי טבלה נוספת, אז פשוט הייתי עושה NOT NULL). אני מנחש שתגיד לי להגדיר אילוץ, אין משהו יותר פשוט?

יש לך רעיון?

[dovid]

@WWW אני בפרקטיקה עושה מאוד מעט אימותים ובדיקות תקפות בצד המסד.
גם בגלל שאני הרבה יותר מתכנת מאשר DBA, גם בגלל שאני בעצמי המתכנת ולא מישהו זר, וגם בגלל שאני בד"כ צוות של איש אחד, וגם בגלל שלרוב הדברים זה לדעתי נכון יותר.
ראה גם:
https://www.red-gate.com/simple-talk/databases/sql-server/learn/where-in-the-application-should-data-validation-be-done/
https://stackoverflow.com/q/464042/1271037
https://stackoverflow.com/q/26851291/1271037

[dovid]

@www אמר בSQL | ערכים מרובים בעמודה:

@www אמר בSQL | ערכים מרובים בעמודה:

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

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

[WWW]

@dovid אמר בSQL | ערכים מרובים בעמודה:

בדיוק כתבתי לך על זה...

חשבתי שעל זה ענית...

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

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

@dovid אמר בSQL | ערכים מרובים בעמודה:

@WWW אני בפרקטיקה עושה מאוד מעט אימותים ובדיקות תקפות בצד המסד.
גם בגלל שאני הרבה יותר מתכנת מאשר DBA, גם בגלל שאני בעצמי המתכנת ולא מישהו זר, וגם בגלל שאני בד"כ צוות של איש אחד, וגם בגלל שלרוב הדברים זה לדעתי נכון יותר.
ראה גם:
https://www.red-gate.com/simple-talk/databases/sql-server/learn/where-in-the-application-should-data-validation-be-done/
https://stackoverflow.com/q/464042/1271037
https://stackoverflow.com/q/26851291/1271037

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

[dovid]

@www אמר בSQL | ערכים מרובים בעמודה:

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

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

[WWW]

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

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

[WWW]

@dovid יש דרך לקבל בקלות תצוגה של הטבלה עם ההקשרים שיהיה לדוגמה עמודה "שפות", עם השפות מופרדים באמצעות פסיק?
ניסיתי את זה, זה עובד נהדר על עמודה אחת כזאת, ברגע שיש לי כמה GOINים, הוא משכפל לי את הערכים בכל עמודה...

[dovid]

@www מה שהבאת לא הכרתי, זה יפה מאוד.
יש מצב שאתה מביא דמי של השאילתה שלא עובדת? כי מסברא זה אמור לעבוד מעולה.

[WWW]

@dovid

SELECT A.*,
       GROUP_CONCAT(A_languages.language) as `language`,
       GROUP_CONCAT(A_workdays.workday) as workdays
FROM A 
LEFT JOIN
     A_languages
     ON A.id = A_languages.A_id 
LEFT JOIN
     A_workdays
     ON A.id = A_workdays.A_id 
GROUP BY A.id;

[dovid]

@www
כעת קלטתי את הבעיה, בעת JOIN לולי הGROUPING יש לכל שורת עובד את כל השורות של השפה ואת כל השורות של הימים, וGROUP_CONCAT מאחד אותם.
אני רואה פה שיש לזה מילת מפתח DISTINCT.
כפי שאתה רואה יש שמה גם אפשרות מיון ובחירת תו מפריד, זה ממש מלהיב, לא הכרתי את התכונה הזו והיא נראית לי לא קיימת בSQL SERVER עריכה: יש STRING_AGG החל מ2017.

[WWW]

@dovid עובד פיקס ב"ה.

רק חבל שMYSQL כ"כ מאחור לגבי views, אני רוצה לעשות לזה view שיהיה קל לאחזר (ואולי גם לעדכן, לא יודע איך זה יעבוד במרובה ערכים?) את הטבלה.
אבל בגלל שיש שמה GROUP BY, הוא משתמש באלגוריתם של טבלה זמנית, והשאילתא לוקחת המון זמן.

[dovid]

@www נושא חדש.