Διαφορετικοί τύποι δεδομένων σε μια στήλη πίνακα ΒΔ.

[nikolaos_]

Δουλεύω σε μια ΒΔ σε MySQL. Έχω έναν πίνακα της μορφής:

 

GROUP_VALUES (gid, category*, value_if_int, value_if_char, value_if_decimal)

 

όπου αποθηκεύεται σε μια από τις τρεις στήλες  value_if_int, value_if_char, value_if_decimal η τιμή που καθορίζει η στήλη category. Το category είναι ξένο κλειδί προς αυτόν τον πίνακα:

 

CATEGORIES (catid, category_name, value_datatype)

 

Οι καταχωρήσεις (οι rows) του πίνακα CATEGORIES είναι καμιά 100στή.

 

Στον GROUP_VALUES καταχωρείται από μια διαφορετική τιμή της κάθε category.

 

Για παράδειγμα:

 

Αν η category αντιστοιχεί στην γραμμή με κάποιο catid=91 και category_name="ποσότητα", τότε στο value_id μπαίνει η επιθυμητή τιμή π.χ. 15, οπότε η γραμμή του πίνακα GROUP_VALUES γίνεται 

 

(122, 91, 15, NULL, NULL)

 

Αν η category_name ισούται με το "θερμοκρασίες" (στο catid 98) τότε η τιμή 13.5 (βαθμοί) πάει στο value_if_decimal, εννοείται βάζω τον catid στη στήλη category. Έτσι π.χ. θα έχω τη γραμμή του GROUP_VALUES σε αυτή την περίπτωση να είναι

 

(123, 98, NULL, NULL, 13.5)

 

Αν η category_name ισούται με "μονάδα_θερμοκρασιών" (catid π.χ. 107) τότε η τιμή "Κελσίου" πάει στο value_if_char, ενώ οι τιμές value_if_integer και value_if_decimal κενές:

 

(124, 107, NULL, "Κελσίου", NULL)

 

Επίσης μπορεί να υπάρχει μια ακόμη γραμμή στον GROUP_VALUES που να έχει category_name πάλι "μονάδα_θερμοκρασιών" αλλά η τιμή στο value_if_char να είναι "Φαρενάιτ". Η μοναδικότητα μιας γραμμής του GROUP_VALUES είναι στο συνδυασμό (category, value_if_τάδε), μοναδικό κλειδί δηλαδή είναι οι δυο από τις τέσσερις στήλες:

 

(125, 107, NULL, "Φαρενάιτ", NULL)

 

Και ο πίνακας CATEGORIES θα περιλαμβάνει τις εξής καταχωρήσεις, για να είναι συνεπής με το παράδειγμα:

...

(91, "ποσότητα", "INTEGER")

...

(98, "θερμοκρασίες", "DECIMAL")

...

(107, "μονάδα_θερμοκρασιών", "CHAR")

...

 

Ποιο είναι το πρόβλημα: Τα πολλά NULL του πίνακα GROUP_VALUES, αντί να συντηρώ μια ωφέλιμη στήλη, συντηρώ τρεις άχρηστες κατά τα δύο τρίτα.

 

Επίσης, δεν ξέρω πώς να φτιάξω ένα constraint που να απαγορεύει και τις τρεις στήλες να είναι ταυτόχρονα NULL.

 

Σκέφτηκα, για να το παρακάμψω, δυο λύσεις:

 

(α) Να φτιάξω μια "μικτή" στήλη value που να είναι VARCHAR και εκεί να βάζω και να βγάζω τις αριθμητικές τιμές με casting. Δεν θέλω όμως να εφαρμόζω συνέχεια συναρτήσεις casting, ενώ θα έχω πρόβλημα όταν θα θέλω να μεταχειριστώ aggregate (group by) functions π.χ. μέση τιμή, άθροισμα κλπ.

 

(β) Να φτιάξω τρεις πίνακες INTEGER_VALUES, DECIMAL_VALUES, CHAR_VALUES και να έχω μια στήλη με "ξένο κλειδί" στον πίνακα GROUP_VALUES. Όμως αυτό θα μου προσθέσει πολυπλοκότητα κάθε φορά που εισάγεται μια νέα τιμή.

 

Υποψιάζομαι πως η θεωρία και οι δυνατότητες της MySQL έχουν μια τρίτη λύση (γ) πιο άμεση που δεν ξέρω.

 

Μπορεί κανείς να βοηθήσει;

[ParhsG]

Ψαξε EAV  model.Σε σχεσιακη βαση δεν εχεις πολλες επιλογες.

 

Δεν ειναι τοσο προβλημα θεωρω οπως το εχεις τωρα. Δε βλαπτουν τα null.

[bnvdarklord]

Δεν ξέρω την βάση σου, αλλα υπάρχει λόγος οι τιμές να είναι έτσι αόριστα σε έναν πίνακα group_values; 

 

Απλα σκέφτομαι οτι αυτές οι τιμές λογικα αφορούν κάποια αντικείμενα τα οποία μπορει να έχουν δικό τους πίνακα. Σε αυτή την περίπτωση τα values μπορούν να πάνε εκεί. Πχ λες παράδειγμα την θερμοκρασία. Αν το χρησιμοποιείς σε κάποιο πίνακα με μετρήσεις ας πούμε, θα εχεις στήλη με θερμοκρασία, και θα ξέρεις και την μονάδα μέτρησης.

Έτσι αόριστα που το έχεις, σε τι εξυπηρετεί;

 

Με βάση την δομή που έχεις πάντως αν θες να μειώσεις τις στήλες μπορεις να βάλεις το int μέσα στο decimal στην καλύτερη και να έχεις 2 στήλες

[nikolaos_]

Δεν ξέρω την βάση σου, αλλα υπάρχει λόγος οι τιμές να είναι έτσι αόριστα σε έναν πίνακα group_values; 

 

Απλα σκέφτομαι οτι αυτές οι τιμές λογικα αφορούν κάποια αντικείμενα τα οποία μπορει να έχουν δικό τους πίνακα. Σε αυτή την περίπτωση τα values μπορούν να πάνε εκεί. Πχ λες παράδειγμα την θερμοκρασία. Αν το χρησιμοποιείς σε κάποιο πίνακα με μετρήσεις ας πούμε, θα εχεις στήλη με θερμοκρασία, και θα ξέρεις και την μονάδα μέτρησης.

Έτσι αόριστα που το έχεις, σε τι εξυπηρετεί;

 

Με βάση την δομή που έχεις πάντως αν θες να μειώσεις τις στήλες μπορεις να βάλεις το int μέσα στο decimal στην καλύτερη και να έχεις 2 στήλες

Η γρήγορη απάντηση είναι ότι υπάρχουν καμιά 100στή κατηγορίες.

 

Οι τιμές κάθε μιας κατηγορίας είναι γενικά 2-3 τυποποιημένες τιμές. Π.χ. "Κελσίου"-"Φαρενάιτ" για τη "θερμοκρασία".

Άλλες κατηγορίες έχουν λιγότερο τυποποιημένες τιμές αλλά έχουν σχετικά μικρό σύνολο τιμών, π.χ. οι θερμοκρασίες.

 

Τα αντικείμενα που περιγράφονται είναι ετερόκλητα. Άλλα έχουν θερμοκρασία, άλλα όχι, κλπ. Καθένα έχει διαφορετικά χαρακτηριστικά (τα οποία ονόμασα "κατηγορίες")  Ουσιαστικά μετράει περισσότερο να ξέρω ποιες από τις 100 κατηγορίες περιλαμβάνει ένα αντικείμενο, το πολύ να έχει 6-7, παρά ποιες τιμές.

 

 

 

Ψαξε EAV  model.Σε σχεσιακη βαση δεν εχεις πολλες επιλογες.

 

Δεν ειναι τοσο προβλημα θεωρω οπως το εχεις τωρα. Δε βλαπτουν τα null.

 

Δεν έχεις άδικο, ωστόσο υπάρχει και ένας τύπος δεδομένων χρόνου στις κατηγορίες και έχω αρχίσει να αναρωτιέμαι μήπως να βάλω και τέταρτη στήλη. Υπάρχει μια κατηγορία π.χ. "ημερομηνία_έναρξης", "ημερομηνία_λήξης", κλπ. Μέχρι στιγμής κουτσοβολεύει το value_int, αλλά αν θέλω να κάνω πράξεις μετά θα κάνω casting και δεν ξέρω πόσο θα χάσω σε απόδοση.

 

Από ό,τι κατάλαβα, έχουμε προβλήματα με την EAV-μοντελοποίηση αυτών των χαρακτηριστικών στην αναζήτηση.

http://karwin.blogspot.gr/2009/05/eav-fail.html

[ParhsG]

Ναι αλλα ειναι μονοδρομος καμια φορα αρκει να ξερεις τα προβληματα. Χωρια που θα πηξεις σε joins. Σε εμπορικα προγραμματα εχω δει και 100 στηλες . Δεν ειναι προβλημα . Το null εξαλλου σε οσες βασεις ξερω νομιζω δε τρωει καθολου χωρο.

 

Επισης υπάρχουν βασεις που κανουν τετοια πραγματα πιο ευκολα οπως mongodb κτλ  μπορει να ειναι καλυτερη λυση αν θες τοσο πολυ performance με submilisecond reads

[nikolaos_]

Χρειάζομαι όσο το δυνατόν γρηγορότερη διαθεσιμότητα των τιμών. Το να φτιάξω έναν αραιό πίνακα 100 στηλών γεμάτο NULLs δεν είναι το μόνο πρόβλημα, γιατί στη συνέχεια μπορεί να θέλω να προσθέτω στήλες.

 

Η ΒΔ λαμβάνει την ημέρα γύρω στα 6000 μηνύματα, τα οποία περιέχουν καθένα 1-10 ομάδες, η κάθε ομάδα έχει 1-10 κατηγορίες πληροφορίας σχηματίζοντας περίπου 100 διαφορετικές κατηγορίες. Οι τιμές που λαμβάνουν πάντως είναι αρκετά τυποποιημένες.

 

Ο πίνακας-κορμός είναι μια σχέση πολλά-προς-πολλά όπου τα μηνύματα σχετίζονται με τις ομάδες.

 

MESSAGE_GROUP (message, groupkey)

 

Κάθε ζεύγος (message, groupkey) είναι μοναδικό.

 

Δοκίμασα στην αρχή να φτιάξω πίνακα για κάθε ομάδα με 10 στήλες, μια στήλη για κάθε κατηγορία, αλλά είχα τρομερά χαμηλή απόδοση.

 

Είχα πίνακες όπως

 

ΘΕΡΜΟΚΡΑΣΙΕΣ(th_id, τιμή, μονάδα, ύψος, τύπος_μέτρησης, ..., reference),

ΑΝΕΜΟΙ (an_id, ...10 στήλες..., reference) κλπ. 

 

Εννοείται πως οι στήλες των ανέμων με αυτές των θερμοκρασιών ήταν διαφορετικές.

 

Αν το μήνυμα είχε θερμοκρασίες μέσα, έκανα έλεγχο αν ταίριαζε με κάποιες γραμμές του πίνακα ΘΕΡΜΟΚΡΑΣΙΕΣ. Π.χ. μια θερμοκρασία 20 βαθμών μέγιστη την έβρισκα σε 150 μηνύματα, αντί να τη βάζω 150 φορές στον πίνακα θερμοκρασιών, έβρισκα τη γραμμή που περιείχε την καταχώρηση

 

(κάποιο-id, 20, "Κελσίου", "μέγιστο", ..., 8172) δηλ. το reference=8172

 

και έβαζα στον MESSAGE_GROUP  τα 150 ζεύγη (x_1, 8172), (x_2, 8172), ..., (x_150, 8172) που x_1,... , x_150 τα IDs των μηνυμάτων, reference=groupkey=8172.

 

Όλα τα πεδία reference από τους πίνακες περιείχαν διαφορετικές τιμές, δηλ. το groupkey=8172 θα το έβρισκα μόνο στις θερμοκρασίες με reference=8172. Μόνο που δεν το δεχόταν η MySQL σαν ξένο κλειδί προς τον πίνακα MESSAGE_GROUP φυσικά. Τα INNER JOINS έτσι δούλευαν πάρα πολύ αργά. 

 

Ύστερα μου πρότειναν το EAV-model.

[ParhsG]

Οταν λες χαμηλη αποδοση;;;

Τα δεδομένα σου μπορουν να μπουν και σε αλλους τυπους βασης οπως προειπα και εγω πιθανον να μην ειχα sql για αυτο.

 

ισως αν μας εδινες ενα πααδειγμα των data  να βρισκαμε καμια λυση.

 

παντως στο αρχικο σου μηνυμα δε βλεπω ΠΡΑΚΤΙΚΑ που εχεις προβλημα τι προσπαθεις να κανεις και δε σου βγαινει. Απο τη στιγμη που δουλευει το αφηνεις ετσι. Αν δεν ειναι open source κανεις δε θα ασχοληθει πως το εχεις κανει.

[Timonkaipumpa]

Χρησιμοποίησε PostgreSQL και βάλε composite type.

 

Δες εδώ:

 

http://www.postgresql.org/docs/8.4/static/rowtypes.html

[nikolaos_]

Ευχαριστώ για τις απαντήσεις. Τελικά διαπίστωσα ότι ο κεντρικός πίνακας πολλά-προς-πολλά είχε κάποια λαθάκια στις εγγραφές του (δεν έβγαιναν μοναδικά ζεύγη, όπως θα έπρεπε) και έκανα ένα αντίγραφο με SELECT DISTINCT κλπ. Στη συνέχεια μπόρεσα να ορίσω ένα primary key index στο ζεύγος· οπότε και να κάνω foreign keys στους επιμέρους πίνακες θερμοκρασιών, ανέμων κλπ. Αυτά δεν θα τα πρόσεχα άμα δεν άνοιγα συζήτηση εδώ.

 

"Ανέβασε ταχύτητα" άμεσα.

 

Θα κρατήσω παράλληλα και την φιλοσοφία EAV πάντως, στους πίνακες που έφτιαξα, για να συγκρίνω αποδόσεις. Όταν χρειαστεί το παραδοτέο θα κάνω το ανάλογο κλάδεμα.

 

Ευχαριστώ επίσης για την υπόδειξη σε PostgreSQL, ίσως σε μερικούς μήνες επαναλάβω την υλοποίηση με την ίδια σχεσιακή δομή σε PostgresSQL, γιατί και εκεί θα συγκριθεί. Υπάρχουν μικροδιαφορές στον κώδικα που πρέπει να παρακάμψω. Προς το παρόν πρέπει να αξιοποιήσω μόνο MySQL.

[ParhsG]

Αποδοση που λες ειναι πολυ σχετικό.  Εξαρτάται απο το σκληρο δισκο και παρα πολλες παραμέτρους. 

Απο το αν εχεις ορισει σωστα ευρετηρια , μνημη στη βαση για διαφορα operation. Στη postgresql δλδ οταν κανεις εκγατασταση ειναι σαν να ηταν σε μηχανημα με 64mb μνημη τα default. Ειναι πολλοι οι παραμετροι που αλλαζεις. 

Πολλες φορες αν χωραει η βαση στη μνημη εισαι πολυ οκ.

 

Επισης τα insert πρεπει να τα κανεις σε μια transaction. Το κανεις αυτο η οχι; 

[nikolaos_]

Να επισημάνω ότι η δομή είναι πλέον σχεσιακή, απέρριψα το EAV που ξεκίνησα στην αρχή του νήματος. Κάθε ομάδα π.χ. θερμοκρασιών έχει τον δικό της πίνακα.

 

Υπάρχει ένας "συγκεντρωτικός" πίνακας μηνυμάτων (αυτός που έλεγα 6000 την ημέρα). Το μήνυμα περιέχει ένα τυποποιημένο κείμενο. Έρχονται σε ένα directory σαν αρχεία (μάλιστα υπάρχουν αρχεία που περιέχουν 14-15 μηνύματα) και ένα perl από τα αρχεία σπάει τα μηνύματα στις επιμέρους ομάδες, αφού πρώτα βάλει το μήνυμα σαν κείμενο στο "συγκεντρωτικό" πίνακα (MESSAGES).

 

INSERT INTO MESSAGES(message_text, ώρα_άφιξης κλπ.) VALUES (κείμενο, ώρα άφιξης κλπ.)

όπου το primary key "messages" είναι auto_increment και λαμβάνει μόνο του τιμή, ενώ τότε εκτελείται κι ένα trigger. Τα μηνύματα σβήνουν (αν πρέπει) κάθε 48 ώρες.

 

Μετά για κάθε ομάδα γίνεται, αν χρειαστεί, ένα insert στον κάθε πίνακα και ένα στον MESSAGE_GROUP. Αν το μήνυμα έχει Ν ομάδες, χρειάζομαι να εισάγω το πολύ σε Ν+2 πίνακες. Αν ο πίνακας μιας ομάδας ήδη περιέχει τις τιμές που προέρχονται από το καινούργιο μήνυμα, τότε απλά αποθηκεύω μόνο το κλειδί message του MESSAGES και το reference (ξένο κλειδί) της γραμμής από τον πίνακα της ομάδας και αποθηκεύω στο MESSAGE_GROUP. Προηγείται δηλ. μια διερεύνηση (σκιαγραφώ τον κώδικα, δεν είναι ακριβώς έτσι) :

 

SET @παλιό_νούμερο=(SELECT reference FROM πίνακα_ΘΕΡΜΟΚΡΑΣΙΩΝ WHERE τιμή=26 AND λοιπές_συνθήκες=τάδε)

 

πριν κάνω εισαγωγή

 

INSERT INTO πίνακας_ΘΕΡΜΟΚΡΑΣΙΩΝ (τιμή, λοιπές_συνθήκες, reference) VALUES (26, τάδε, @νέο_νούμερο) 

 

για να δω αν χρειάζεται. Αν χρειάζεται, υπάρχει ένας πίνακας για να πάρει τιμή το @νέο_νούμερο. Αλλιώς δεν γίνεται INSERT στον πίνακας_ΘΕΡΜΟΚΡΑΣΙΩΝ

Κατόπιν το σημαντικό είναι να γίνει αυτό

 

INSERT INTO MESSAGE_GROUPS (message, groupkey) VALUES (message, reference)

όπου reference = @νέο_νούμερο ή reference = @παλιό_νούμερο, ανάλογα τι προέκυψε.

 

Εδώ βέβαια ίσως μπορώ να κάνω κάποιο optimization, συγκεντρώνοντας τις Ν τιμές από τις Ν ομάδες.

 

Αναγκαστικά δεν μπορώ να κάνω ένα transaction με μια εντολή SQL που να τα κάνει όλα μια κι έξω. Έχω σκεφτεί μήπως κάνω ένα κλείδωμα καθώς κάνει τους ελέγχους και τις εισαγωγές για κάθε μήνυμα ξεχωριστά, μέχρι το μήνυμα να αποδελτιωθεί και η πληροφορία του να εισαχθεί σε όλους τους πίνακες που χρειάζεται, για να μην "μολύνει" τη διαδικασία το επόμενο μήνυμα που τυχόν να έρθει πριν ολοκληρωθεί η επεξεργασία του τρέχοντος. Αλλά αφενός δεν ξέρω πώς ακριβώς, η perl δεν παίρνει BEGIN TRANSACTION...COMMIT, αφετέρου έχω έναν χωριστό πίνακα μεταβλητών για να τηρεί τις αριθμητικές τιμές globally και πάτησα πάνω σε αυτόν για να γράψω το υπόλοιπο perl script.

 

Μέχρι στιγμής χρησιμοποιώ trigger για να μπορώ να τραβάω την τιμή του primary key (των MESSAGES συγκεκριμένα) σε μεταβλητή και να την εισάγω μετά σαν τιμή ξένου κλειδιού στον πίνακα των MESSAGE_GROUPS.

 

Οποιαδήποτε πρόταση βελτίωσης είναι ευπρόσδεκτη.