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

[WWW]

אני משתמש ב MYSQL.
יש לי צורך ליצור טבלה שכמה עמודות שלו יהיו עם ערכים מרובים.
דוגמה:

מזהה	שם	ימי עבודה	משמרות	מקצועות
1	אבי	1,2,3,5	1	1,2
2	יוני	1,2,3,4,5,6	1,2	2
3	משה	1,2,4,5	2	2,3

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

תודה רבה!

[איש נחמד]

@www היה לי לאחרונה פרוייקט עם בעיה דומה, ב-stackoverflow ראיתי שממליצים להימנע ממבנה כזה.
בכל אופן יש כאן 2 חלקים. הכנסת נתונים - אתה יכול להעביר כל ערך לפונקציה שתבדוק אם הוא תקין להכנסה לשדה ורק אז לבצע את השאילתה. שליפת נתונים - like %2% (עם מרכאות כמובן)

ראה:
https://stackoverflow.com/questions/10686333/save-array-in-mysql-database/32664559

[WWW]

@איש-נחמד זה נראה לי לא כ"כ יעיל לביצועים.
אם כבר, JSON, לא?
או שיש משהו אחר, לכן אני שואל כאן.

[איש נחמד]

יש אופציה לחלק את הטבלה וליצור טבלאות נפרדות עבור 'ימי עבודה' 'משמרות' ו- 'מקצועות'.
כך לדוג' כל שורה בטבלת משמרות תכיל אינדקס, ID של משתמש, ויום עבודה בודד. בצורה הזו יש ל-SQL סל כלים רחב של שאילתות ופונקציות שיוכלו להחזיר לך את המידע שאתה צריך בלי שתצטרך לסנן אותו שוב בקוד.
אין לי מושג לגבי ההשפעה של זה על הביצועים.
ראה: https://stackoverflow.com/a/444268/11675758
אם יש למישהו פתרון טוב יותר אשמח לשמוע, יעזור לי מאד.

[WWW]

@איש-נחמד משום מה זה לא נראה לי הדרך לעשות את זה בכזה קצת ערכים.
אולי כן.

[איש נחמד]

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

@איש-נחמד משום מה זה לא נראה לי הדרך לעשות את זה בכזה קצת ערכים.
אולי כן.

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

[WWW]

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

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

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

[איש נחמד]

יש כאן תגובה ארוכה שמסבירה למה יצירת טבלאות מרובות עדיפה (אם כי מובאות שם דעות נוספות) :
https://stackoverflow.com/a/3070426/11675758

[chagold]

@www https://saveriomiroddi.github.io/Storage-and-indexed-access-of-denormalized-columns-arrays-on-mysql-8.0-via-multi-valued-indexes/

עריכה: שו"ר ש@איש-נחמד הביא כעי"ז.

[dovid]

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

[WWW]

@dovid
OK.
JSON לא בא בחשבון?

במקרה האמיתי, מדובר בעוד כמה עמודות שהם מרובי ערכים, לדוגמה:

מזהה	שם	ימי עבודה	משמרות	מקצועות	שפות
1	אבי	1,2,3,5	1	1,2	עברית
2	יוני	1,2,3,4,5,6	1,2	2	עברית, אנגלית
3	משה	1,2,4,5	2	2,3	עברית, אנגלית, צרפתית

ועוד...
זה מאד מסבך ליצור עוד 10 טבלאות...

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

שאלה נוספת, זה גם מה שהיית עושה במקרה של המשמרות? כשאני יודע שיש רק 2 משמרות, ובעצם יש 3 מצבים: 1, 2, 1+2. לא היית שומר BIT באורך 1 עם 3 ערכים אפשריים: 0/1/2?
או לחילופין: 2 עמודות בינאריות: משמרת ראשונה, משמרת שנייה. רק שכאן אסור להיות בשניהם 0, (או שניהם 1 או אחד מהם 1)?

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

[dovid]

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

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

[WWW]

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

@www מצטער, לא הבנתי מה ההבדל בין המקרה האמיתי למה שחשבתי.

אין כמעט הבדל, המקרים כמעט זהים.. אלא שיש עוד כמה עמודות כאלה, מה שהתכוונתי שלא מדובר בעוד 3 טבלאות, אלא בעוד 10...

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

אני גם לא מבין מה הסתירה בין ימי עבודה 8 ל3 משמרות,

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

[WWW]

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

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

הבנתי.

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

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

[dovid]

@www אם צפויים שינויים תכופים ברשימת השפות, פשיטא שיש ליצור להם טבלה משלהם.
אם מדובר ברשימה קבועה של כמה אתה יכול להשתמש בenums.
בעבר אכן יצרו טבלה על כל כזה דבר.

[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). אני מנחש שתגיד לי להגדיר אילוץ, אין משהו יותר פשוט?

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