בסיס נתונים עם עמודות דינאמיות בדומה ל CRM

[חוקר]

יוצא לי לא אחת שאני צריך לתת ללקוח אפשרות של עמודות דינאמיות, דהיינו לכל לקוח שדות שונות באותה טבלה.
עד כה הייתי משתמש בטבלה עם עשר עמודות נוספות מעבר לבסיס הקבוע, והם נקראו בישראל "שדה נוסף 1" וכן הלאה.
אבל אני מחפש כעת להבין איך זה עובד בכל חברות ה CRM שכל לקוח יכול לשנות ולהוסיף עמודות וטבלאות לכאורה כאוות נפשו, הוא צריך אולי להגדיר את סוגי השדות אבל בתכלית ניתן להוסיף עמודות כצרכיו.
זה נוגע לי כעת שלקוח ביקש לי מערכת שתצטרך להיות לכאורה די דינאמית ומשתנה בין צורך לצורך.
כידוע MYSQL הינה שדה טיפוסית וחייבת הגדרה ברורה מראש של כל העמודות, ולכן הדרך היחידה העולה בפני לבצע זאת באמצעות MYSQL הינה לכאורה 3 טבלאות, אחת של שמות הטבלאות, והשניה של העמודות לכל טבלה, והשלישית של הנתונים עצמם משהו בסגנון של ( כמובן מזהה שורה A I), מזהה מנוי, מזהה טבלה, מזהה עמודה, והתוכן.
ובכל הצגת טבלה זה בעצם לעשות איזה שהוא סוג PIVOT לנתונים.
אבל נשמע לי די הזוי, די זולל משאבים ודי עבודה. (וייתכן מאוד שאני לא צודק, ואותו מנוע MYSQL שמטפל בהצגת טבלאות ועמודות רגילות יכול בקלות לעשות זאת מתוך הנ"ל באמצעות אינדקסים טובים).
למען האמת אני לא מכיר עדיין בסיסי נתונים אחרים.
אך יותר נשמע לי שהם משתמשים בבסיסי נתונים מסוגי אחרים, ששם אין צורך להגדיר מראש את השדות וזה נשמר יותר כמו אובייקטים בדומה ל JSON ואין צורך להגדיר כלום מראש, ואז יש רק טבלת נתונים וטבלת שדות (לצורך תצוגה, לתת ללקוח את המידע מראש של השדות הקיימים) והנתונים עצמם שמורים כל שורה כל העמודות כמו בטבלה מוגדרת מראש.
אשמח מי שיכול לשפוך קצת אור על העניין, ובעיקר איך ניתן לעשות זאת באמצעות הסביבה המוכרת שלי MYSQL..
תודה

[dovid]

@חוקר SQL אכן לא מתאים לזה, ואפילו לאפשר שדה_נוסף זה פתרון צולע.
הדרך המקובלת (למיטב/אי מיטב ידיעתי) במקרה הזה להשתמש עם פתרונות לא-כ"כ-SQL (ביטוי שהמצאתי כעת), כמו EAV MODEL, שששומרים בטקסט רציף את הישות-תכונה-ערך, עם מפריד כל שהוא כמו המקף במקרה הזה, או בשלושה עמודות אם לא זונחים את SQL לגמרי.
למשל אם מדובר באנשי קשר במקרה של טקסט רציף, ויש תכונה של מספר נעליים זה נראה טבלה כזאת:

איש-קשר5123:מספר-נעליים:52
איש-קשר5428:מספר-נעליים:43

כשאתה רוצה לשלוף כל התכונות של איש קשר אתה עושה:

WHERE KEY LIKE "איש-קשר5128:%'

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

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

יש עוד פתרונות, כמו JSON מאונדקס וכדומה (נתמך במיוחד בNO-SQL אבל גם בPostgres ועוד כנראה). אבל אתה שאלת המסגרת MYSQL.

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

[חוקר]

@dovid כתב בבסיס נתונים עם עמודות דינאמיות בדומה ל CRM:

שלושה עמודות

לא מספיק הבנתי למה אתה מתכווין 3, האם כמו שכתבתי מזהה שורה, מזהה עמודה והנתון?
בכללי נשמע מדבריך צורת עבודה מסויימת, ולדבריך, אשמח להבין למה לא להשתמש בשדה json הקיים ב mysql ובעצם לשמור עמודה id של השורה ואז עמודה json עם מידע דינאמי?
זה יזלול יותר ביצועים מאשר שדה טקסט? יקשה לבצע סינונים? או עומס בדיסק?

@dovid כתב בבסיס נתונים עם עמודות דינאמיות בדומה ל CRM:

יש עוד פתרונות, כמו JSON מאונדקס וכדומה

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

[dovid]

שלושה עמודות הכוונה ישות/תכונה/ערך, ואכן עמודת ישות בד"כ תכלול או תהיה כולה מפתח של טבלה אחרת.

@חוקר כתב בבסיס נתונים עם עמודות דינאמיות בדומה ל CRM:

אשמח להבין למה לא להשתמש בשדה json הקיים ב mysql ובעצם לשמור עמודה id של השורה ואז עמודה json עם מידע דינאמי?

בהחלט אפשרות. הזכרתי את זה אבל אתה לפעמים רוצה לאנדקס את הערך או למצוא ברמה טבלאית את התכונות, בJSON זה לא הכל/תמיד נתמך.
בנסוף לתא JSON יש חסרונות ביחס לEAV זה מנפח שורות בטבלה, ומצריך להעביר הרבה מידע מהקליינט לסרבר פר שורה (אתה צריך לזכור לא לעשות * בSELECT כשהתא הזה לא נצרך), זה מחייב סריאליזציה בקליינט ולהיפך כל עדכון, זה עולה יקר יחסית כשרוצים לעשות עדכון כמותי.

[dovid]

@חוקר כתב בבסיס נתונים עם עמודות דינאמיות בדומה ל CRM:

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

קדימה, תלמד Mongo... אני מאוד מאוד אוהב את מונגו (ולא כ"כ מכיר ומשתמש בו..), ואני חושב שכדאי לראות את הזמן להכיר אותו.

[dovid]

@dovid כתב בבסיס נתונים עם עמודות דינאמיות בדומה ל CRM:

שלושה עמודות הכוונה ישות/תכונה/ערך, ואכן עמודת ישות בד"כ תכלול או תהיה כולה מפתח של טבלה אחרת.

@חוקר כתב בבסיס נתונים עם עמודות דינאמיות בדומה ל CRM:

אשמח להבין למה לא להשתמש בשדה json הקיים ב mysql ובעצם לשמור עמודה id של השורה ואז עמודה json עם מידע דינאמי?

בהחלט אפשרות. הזכרתי את זה אבל אתה לפעמים רוצה לאנדקס את הערך או למצוא ברמה טבלאית את התכונות, בJSON זה לא הכל/תמיד נתמך.
בנסוף לתא JSON יש חסרונות ביחס לEAV זה מנפח שורות בטבלה, ומצריך להעביר הרבה מידע מהקליינט לסרבר פר שורה (אתה צריך לזכור לא לעשות * בSELECT כשהתא הזה לא נצרך), זה מחייב סריאליזציה בקליינט ולהיפך כל עדכון, זה עולה יקר יחסית כשרוצים לעשות עדכון כמותי.

בהחלט ייתכן שJSON עדיף,
ראה מאמר בעניין https://coussej.github.io/2016/01/14/Replacing-EAV-with-JSONB-in-PostgreSQL/

[ivrtikshoret]

פוסט זה נמחק!