DB Modellierung mit variablen Attributen in der Tabelle

[GRudiD]

Hallo,

die Überschrift ist vielleicht etwas irreführend. Das bitte ich zu entschuldigen. Es geht um folgendes:
Ich bin dabei eine Verwaltungssoftware für Museumsbestände zu schreiben. Jetzt hat ein Museum welches Bücher katalogisieren will andere Einträge als ein Museum welches Kronkorken archiviert (soll nur als Beispiel dienen )

Nun sind mir 2 Möglichkeiten eingefallen, wie man das lösen kann.
1. Eine Tabelle für jede "Museumsart".
Das hat den Vorteil, dass man relativ einfach eine spezialisierte Anfrage stellen kann (Bsp: "titel = 'su und so' AND nummer > 20"). Ein großer Nachteil ist allerdings, dass man diese Art nur relativ schwer erweitern kann (Tabelle erweitern/ändern, Programm anpassen). Zudem kommt für jede "Museumsart" eine neue Tabelle hinzu.

CREATE TABLE elements_for_museum_1 (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `groupID` int(11) DEFAULT '0',
  `number` int(10) DEFAULT NULL,
  `title` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
);

2. Alle Werte eines Eintrags als Key-Value-Pair speichern. Also eine Tabelle der folgenden Art [eintrag_id, key, value].
Vorteil ist, dass man für ein neues Attribut nur ein neuen Key einfügen muss (die GUI könnte ja dynamisch erzeugt werden). Auch eine Suchanfrage eines Wortes in mehreren Attributen ist so einfacher.
Nachteile sind allerdings, dass nur ein Datentyp verwendet werden kann und dass spezialisierte Suchanfragen nicht mit SQL (zumindest habe ich noch keine Idee) realisiert werden können.

CREATE TABLE `element_data` (
  `element_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `key` varchar(255) DEFAULT NULL,
  `value` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`element_id`)
);

2.1 Das Problem mit den unterschiedlichen Datentypen könnte man vielleicht durch mehrere 'element_data' Tabellen lösen.

CREATE TABLE `element_data_char` (
  `element_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `key` VARCHAR(255) DEFAULT NULL,
  `value` VARCHAR(255) DEFAULT NULL,
  PRIMARY KEY (`element_id`)
);

CREATE TABLE `element_data_int` (
  `element_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `key` VARCHAR(255) DEFAULT NULL,
  `value` INTEGER(10) DEFAULT NULL,
  PRIMARY KEY (`element_id`)
);

So, lange Rede, wenig Sinn. Kommen wir nun zu meiner Frage: Welche der Herangehensweisen ist sinnvoll? (Ich tendiere momentan zu 2.1) Habt ihr noch andere Lösungsideen?

PS: Die Beispiele dienen nur zur Veranschaulichung. Implementiert werden soll das später mindestens für MySQL, MS-SQL und PostgreSQL.

 

[sliwalker]

Wargh.

Wir haben eine Applikation mit dynamischer Attributierung umgesetzt und es ist ein graus.
Sprich, es gibt eine Tabelle mit Key-Value-Paaren, der Datentyp ist in der value-Spalte immer String und es gibt Metadaten, was denn der String eigentlich ist. Jeder Key wird also durch metadaten in einer anderen Tabelle ergänzt. Was sind die Probleme?

- Bau mal eine View, die Dir Ergebnislisten zusammenträgt. Bei 1000 Attributen hast Du auch mindestens 1000 Zeilen in der View (wer es kürzer kann, nehme ich gerne )
- Du hebelst Datenbankmechanismen aus. Keine ForeignKey-Beziehungen mehr möglich
- Du musst die value-Spalte komplett indexieren, damit Du überhaupt performant suchen kannst inder Mega-View. Ein Index auf ein Varchar ist aber nicht gerade das was man tun sollte. Macht sich beim Einfügen bemerkbar
- Da Du alle Daten eines Objekts ja auch dieser MegaView beziehst, hast Du im Umgang mit Beans Probleme. Das Bean zeigt auf die View. Ändert sich in einem langen Prozess ein Wert in der View, wird er Dir trotz gleicher Transaktion nicht im Bean geändert vorgehalten. Probier es aus... Beans und Views machen bei uns nur Probleme (Java 1.5, JBoss 4)

Wie könnte man es sonst machen?
Deine Idee geht in die richtige Richtung. Wir haben das Verfahren so geändert, dass wir nun für jedes Attribut eine eigene Tabelle anlegen. Ja richtig, für jedes Attribut. So kommen zwar zig tausend Tabellen zustande, aber wir haben Constraints, indexierung von ID und Wertespalten (manchmal auch Varchar, aber eben nicht IMMER) und die Möglichkeits Tabellen in Beans zu benutzen. Tablespaces lassen sich wunderbar skalieren...usw
Eine Gesamtview ist mit JOINS realisierbar und muss nicht mehr mit 1000 Subselects erledigt werden.

Alles in allem sind wir momentan sehr zufrieden mit der Lösung.

 

[GRudiD]

Danke für die schnelle Antwort.

Da sind auch ein paar Dinge dabei, an die ich noch nicht gedacht habe.
Ich werde wohl die erste Variante implementieren. Ich sehe allerdings keinen Grund für jedes Attribut eine eigene Tabelle zu erstellen. Oder gibt es dafür doch gute Gründe?

Bis denne

 

[sliwalker]

Außer die 6 die ich genannt habe?

 

[GRudiD]

Hast ja recht. Allein die sollten schon reichen