Besseres Design für eine riesige Tabelle

A_Hofmann · 12. Februar 2007 um 17:48

Hallo zusammen,

ich betreue eine Datenbank für einen Katalog. Das ganze ist wild gewachsen und sah am ende so aus:
Es gab eine große Tabelle (MySQL MyISAM) mit 260 Feldern, Varchar, enums, blob und text wild gemischt. mysql 4.0 hat irgendwann aufgegeben weil die spaltendefinitionen über 64k groß geworden sind. Daran hab ich schon gemerkt das dieser aufbau suboptimal ist.
Die tabelle hat übrigens nur 1000 Einträge und wird auf höchstens 3000 oder 6000 anwachsen.

Ich habe mir also überlegt zwei Tables zu machen: einer der die Felddefinitionen beinhaltet und einer der die Daten vorhält. Das ganze fürt zu einem Table der im schlimmsten Fall 6000 * 260 Einträge hat, aber ist ja kein Ding für SQL. Aber ein riesiger Nachteil bleibt, ich kann kaum noch Funktionen von SQL benutzen z.B. muß ich alle Feldtypen im Programm per Hand behandeln. Das ganze sieht gerade so aus:

CREATE TABLE fieldtypes (
uid int(11) NOT NULL auto_increment,
name varchar(200) collate utf8_unicode_ci NOT NULL default ‚‘,
datatype enum(‚varchar‘,‚text‘,‚int‘,‚double‘,‚enum‘,‚undefined‘) collate utf8_unicode_ci NOT NULL default ‚undefined‘,
datarules text collate utf8_unicode_ci,
allow_null enum(‚true‘,‚false‘) collate utf8_unicode_ci NOT NULL default ‚false‘,
default_value varchar(255) collate utf8_unicode_ci default NULL,
comment varchar(250) collate utf8_unicode_ci default NULL,
nice_name varchar(50) collate utf8_unicode_ci NOT NULL default ‚‘,
group_id smallint(6) NOT NULL default ‚0‘,
sort_order int(11) NOT NULL default ‚-1‘,
PRIMARY KEY (uid),
UNIQUE KEY name (name),
UNIQUE KEY group_id (group_id,sort_order)
) ENGINE=InnoDB

Daran sieht man ja schon, dass das ganze irgendwie eklig ist.

Jetzt ist mir beim Kaffeeschlürfen spontan eingefallen das ich ja auch für jeden Datentyp eine eigene Tabelle machen kann. Da kann ich dann ja wieder alle Felddefinitionen von SQL benutzen. Einzig bei den ENUM sachen wirds wieder eklig, weil ich ja für jedes unterschiedliche ENUM eine eigene Tabelle brauche. Allerdings werden das auch nur ca. 5 oder 6 Tables.

Trotzdem. Irgendwie ist alles davon eklig und ich glaube es gibt eine elegante Lösung dafür. Vielleicht ist das eleganteste ja sogar den riesentable in meherere teile zu zerschneiden und per 1-1 Verbindung zu verknüpfen?

Ich bin um jeden hilfreichen Post dankbar.

Georg_V · 12. Februar 2007 um 22:05

Hallo zusammen,

ich betreue eine Datenbank für einen Katalog. Das ganze ist
wild gewachsen und sah am ende so aus:
Es gab eine große Tabelle (MySQL MyISAM) mit 260 Feldern,
Varchar, enums, blob und text wild gemischt. mysql 4.0 hat
irgendwann aufgegeben weil die spaltendefinitionen über 64k
groß geworden sind. Daran hab ich schon gemerkt das dieser
aufbau suboptimal ist.
Die tabelle hat übrigens nur 1000 Einträge und wird auf
höchstens 3000 oder 6000 anwachsen.

gelöscht :

Ich bin um jeden hilfreichen Post dankbar.

Hallo A,

willst Du wirklich erzählen, dass für jeden Deiner 1000 Einträge alle 260 Spalten gefüllt hast? Eine Aufteilung nach entsprechenden Gruppen wäre sinnvoller.

MfG Georg V.

Ulrich_Vaid · 13. Februar 2007 um 07:23

Hallo zusammen,

ich betreue eine Datenbank für einen Katalog. Das ganze ist
wild gewachsen und sah am ende so aus:

Bei einem Katalog fällt mir spontan folgende Aufteilung ein :
–> jeweils 1 Tabelle für Prod-Beschreibungen,Bilder und Preise, jeewils über FK’s verknüpft. Von deiner Idee einer „Feldtypentablle“ halte ich gar nichts, das tönt meistens nach einer Vergewaltigung der Datenbank

Gruss

A_Hofmann · 13. Februar 2007 um 08:01

Bei einem Katalog fällt mir spontan folgende Aufteilung ein

–> jeweils 1 Tabelle für Prod-Beschreibungen,Bilder und
Preise, jeewils über FK’s verknüpft. Von deiner Idee einer
„Feldtypentablle“ halte ich gar nichts, das tönt meistens nach
einer Vergewaltigung der Datenbank

Gruss

Das verstehe ich nicht. Das sind am Ende drei Tabellen oder? Eine für Stammdaten, eine für Bilder und eine für Preise?
Das würde mein Problem nicht lösen, denn dann hätte ich statt einem Table mit 280 Feldern, drei Tables mit einmal 278 und zweimal jeweils einem Feld, wo ist da die Verbesserung?

A_Hofmann · 13. Februar 2007 um 08:04

Hallo A,

willst Du wirklich erzählen, dass für jeden Deiner 1000
Einträge alle 260 Spalten gefüllt hast? Eine Aufteilung nach
entsprechenden Gruppen wäre sinnvoller.

MfG Georg V.

Was soll daran ungewöhnlich sein? Es ist ein Katalog, da ist natürlich soviel wie es geht ausgefüllt.
Wenn ich dich richtig verstehe, würdest du den einen großen Table in mehrere kleine „zerschneiden“ (und das natürlich sinnvoll, also nach Gruppen unterteilen) und alle per 1-1 Verbindung verknüpfen?

gismo · 13. Februar 2007 um 10:08

Hallo zusammen,

ich betreue eine Datenbank für einen Katalog. Das ganze ist
wild gewachsen und sah am ende so aus:
Es gab eine große Tabelle (MySQL MyISAM) mit 260 Feldern,

…

Hallo,

in einem Katalog musst Du doch bestimmt funktionale/logische Gruppen haben, welche du in einem anderen Table mit x:x Beziehung auslagern kannst. Besonders bei 260 Eigenschaften.
Ich kenne Deinen Kenntnisstand nicht, also nimms mir nicht übel wenn ich Dir jetzt was sag was Du schon weisst: google mal nach 3ter Normalform für einen DB. Bei 260 Feldern kann ich mir nicht vorstellen, dass Du keine Redundanzen hast.

Gruß Jakob

Ulrich_Vaid · 13. Februar 2007 um 10:43

Nun, genauer kann ich es mit deinen Angaben auch nicht machen. Versuche einfach, eine Normalisierung durchzuführen ? Was sind das den für 2xx Felder ? Wenn du eine Tabellenbeschreibung posten könntest, wäre schon viel geholfen

Gruss

[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]

Ulrich_Vaid · 13. Februar 2007 um 10:47

Was soll daran ungewöhnlich sein? Es ist ein Katalog, da ist
natürlich soviel wie es geht ausgefüllt.

Ja, ein Katalog kann 3 Felder oder 1000 haben, das hängt davon ab, welche Detailinfo’s du brauchst. Du hast folgendes Problem --> zuviele Spalten (offensichtlich) in EINER Tabelle --> Lösung --> a) redundante Daten finden --> Normalisierung, b) Funktionale Einheiten finden und jeweils als Entität ablegen-- > Führt zu N Tabellen, welche du über Referenzen verbindst

Gruss

Eillicht_zu_Vensre_ac055a · 13. Februar 2007 um 11:38

Tach.

Was soll daran ungewöhnlich sein? Es ist ein Katalog, da ist
natürlich soviel wie es geht ausgefüllt.

Wenn der Katalog wirklich 1000 oder mehr völlig gleichartige Objekte enthält, besitzen natürlich alle Objekte die gleichen Eigenschaften. Das kann ich mir aber bei 260 Einzeleigenschaften nicht denken.

Sagen wir mal, mein Katalog enthält Schuhe, Schrauben und Autoreifen. Wenn ich alle diese Objekte in eine Tabelle würge, bekomme ich - ohne Anspruch auf Vollständigkeit - folgende Einzelfelder : Schuhgröße, Farbe, Herren/Damen/Kinder, Material Ober"leder", Material Sohle, Länge, Schlüsselweite, Gewindeart, Kopfform, Links/Rechtsgewinde, Oberfläche, DIN/ISO, Hersteller, Zähigkeitsklasse, Winter/Sommer/Ganzjahr, Profilart, Felgengröße, Einpresstiefe, Felgenart, Geschwindigkeitsklasse.

Die Tabelle kann ich natürlich füllen - mit dem Effekt, dass ich immer nur ein Drittel der angebotenen Felder nutze und außerdem mächtig aufpassen muss bei der Datenpflege.

Genau deswegen bildet man Objekte mit unterschiedlichen eigenschaften in verschiedenen Tabellen ab. Ohne Deinen Anwendungsfall zu kennen, kann Dir aber niemand verraten, ob Du im Endeffekt 10 Tabellen brauchst oder vielleicht 50.

Gruß Eillicht zu Vensre

A_Hofmann · 13. Februar 2007 um 11:58

Naja ich poste mal einen Dump der Tabellenstruktur, vielleicht hilft es ja was (und ja: da wurden teilweise heftige Böcke geschossen z.B. sind alle CHAR(2) eigentlich ja/nein enums und so ziemlich alle varchar(250) können alles mögliche sein, mal wäre es besser wenn es int(10) wären, mal einfach nur varchar(100). Ich bin nur bisher immer davon ausgegangen dass das gesamte „Design“ der Tabelle schwachsinn ist und hab mich um solche optimierungen garnicht erst gekümmert).
Ich habe teilweise kräftig zensieren müssen, da stehen jetzt also nicht 280 Felder. Aber es ist alles relevante noch da, ich habe auch ganze gruppen einfach ge’xxx’t, damit man sieht wie das gedacht ist.

Das mit dem Normalisieren mache ich eigentlich, aber mir ist sehr unklar wie ich z.B. die ganzen ja/nein ENUMs zusammenfassen könnte, oder die zig varchars.

In dieser Tabelle ist nach meinem Verständnis so ziemlich alles atomisiert und auch nicht redundant gespeichert. Die 280 Felder stehen einfach für 280 Einzelinformationen die es zu jedem Datensatz geben kann.

Also hier jetzt mal der dump:

CREATE TABLE katalog (
xxx_id int(11) NOT NULL auto_increment,
insert_time varchar(19) default NULL,
call_time varchar(19) NOT NULL default ‚unknown‘,
intern_name varchar(250) default NULL,
intern_member enum(‚true‘,‚false‘) default NULL,
intern_notizen text,
intern_selbstbeschreibung text,
intern_geloescht enum(‚ja‘,‚nein‘) NOT NULL default ‚nein‘,
korrektur_text text,
korrektur_absender varchar(100) default NULL,
korrektur_alles_korrekt char(2) default NULL,
korrektur_copyright enum(‚ja‘,‚nein‘) default NULL,
korrektur_eintragung text,
korrektur_mail_gesendet text,
korrektur_eingetragen enum(‚ja‘,‚nein‘) NOT NULL default ‚nein‘,
st_name varchar(250) default NULL,
st_ort varchar(250) default NULL,
st_plz varchar(250) default NULL,
st_bundesland varchar(100) default NULL,
st_strasse varchar(250) default NULL,
xxx_member enum(‚ja‘,‚nein‘) default NULL,
xxx_get_member enum(‚ja‘,‚nein‘) default NULL,
yyy_teilnahme enum(‚ja‘,‚nein‘) default NULL,
yyy_xxxxx enum(‚ja‘,‚nein‘) default NULL,
yyy_10personen enum(‚ja‘,‚nein‘) default NULL,
yyy_vorsaison text,
yyy_nachsaison text,
yyy_pauschalgeb varchar(250) default NULL,
yyy_sonstige text,
yyy_xxxstufe enum(‚R0‘,‚R1‘,‚R2‘,‚R3‘) default NULL,
yyy_internet enum(‚ja‘,‚nein‘) default NULL,
org_leitung varchar(250) default NULL,
org_eigentuemer varchar(250) default NULL,
org_flaeche varchar(250) default NULL,
org_gpskoords varchar(250) default NULL,
intern_koords varchar(50) default NULL,
org_oeffnungszeiten text,
org_tel varchar(100) default NULL,
org_fax varchar(50) default NULL,
org_email varchar(250) default NULL,
org_url varchar(250) default NULL,
org_xxxx enum(‚ja‘,‚nein‘) default NULL,
org_xxxxbedingungen text,
org_xxxxbedingungen_neu varchar(250) default NULL,
org_xxxxine enum(‚ja‘,‚nein‘) default NULL,
org_xxxxbesonderheiten enum(‚ja‘,‚nein‘) default NULL,
org_mittagspause_anfang varchar(5) default NULL,
org_mittagspause_ende varchar(5) default NULL,
org_xxxxruhe varchar(250) default NULL,
org_motor enum(‚ja‘,‚nein‘) default NULL,
org_ec enum(‚ja‘,‚nein‘) default NULL,
vvvvv_mobilheime varchar(250) default NULL,
vvvvv_zelte varchar(250) default NULL,
vvvvv_bungalows varchar(250) default NULL,
vvvvv_wohnwagen varchar(250) default NULL,
vvvvv_sonstiges varchar(250) default NULL,
bbb_dauerstand varchar(250) default NULL,
bbb_mobil varchar(250) default NULL,
bbb_cee varchar(250) default NULL,
bbb_ceeampere varchar(250) default NULL,
bbb_220v varchar(250) default NULL,
bbb_220vampere varchar(250) default NULL,
bbb_gas varchar(250) default NULL,
bbb_tv varchar(250) default NULL,
bbb_internet varchar(250) default NULL,
sorg_shop enum(‚ja‘,‚nein‘) default NULL,
sorg_shop_offen varchar(250) default NULL,
sorg_shop_entf varchar(250) default NULL,
sorg_markt enum(‚ja‘,‚nein‘) default NULL,
sorg_markt_offen varchar(250) default NULL,
sorg_markt_entf varchar(250) default NULL,
sorg_camp enum(‚ja‘,‚nein‘) default NULL,
sorg_camp_offen varchar(250) default NULL,
sorg_camp_entf varchar(250) default NULL,
sorg_imbiss enum(‚ja‘,‚nein‘) default NULL,
sorg_imbiss_offen varchar(250) default NULL,
sorg_imbiss_entf varchar(250) default NULL,
sorg_gast enum(‚ja‘,‚nein‘) default NULL,
sorg_gast_offen varchar(250) default NULL,
sorg_gast_entf varchar(250) default NULL,
sorg_wtrock enum(‚ja‘,‚nein‘) default NULL,
sorg_wtrock_offen varchar(250) default NULL,
sorg_wtrock_entf varchar(250) default NULL,
sorg_wmasch enum(‚ja‘,‚nein‘) default NULL,
sorg_wmasch_offen varchar(250) default NULL,
sorg_gas enum(‚ja‘,‚nein‘) default NULL,
sorg_gas_offen varchar(250) default NULL,
sorg_gas_entf varchar(250) default NULL,
sorg_fahrrad enum(‚ja‘,‚nein‘) default NULL,
sorg_fahrrad_offen varchar(250) default NULL,
sorg_fahrrad_entf varchar(250) default NULL,
sorg_baby enum(‚ja‘,‚nein‘) default NULL,
sorg_baby_offen varchar(250) default NULL,
freiz_kaltschwimm enum(‚ja‘,‚nein‘) default NULL,
freiz_kaltschwimm_offen varchar(250) default NULL,
freiz_kaltschwimm_entf varchar(250) default NULL,
freiz_kaltschwimm_freiluft enum(‚ja‘,‚nein‘) default NULL,
freiz_heizschwimm enum(‚ja‘,‚nein‘) default NULL,
freiz_heizschwimm_offen varchar(250) default NULL,
freiz_heizschwimm_entf varchar(250) default NULL,
freiz_heizschwimm_freiluft enum(‚ja‘,‚nein‘) default NULL,
freiz_hallbad enum(‚ja‘,‚nein‘) default NULL,
freiz_hallbad_offen varchar(250) default NULL,
freiz_hallbad_entf varchar(250) default NULL,
freiz_erlbad enum(‚ja‘,‚nein‘) default NULL,
freiz_erlbad_offen varchar(250) default NULL,
freiz_erlbad_entf varchar(250) default NULL,
freiz_erlbad_freiluft enum(‚ja‘,‚nein‘) default NULL,
freiz_strand enum(‚ja‘,‚nein‘) default NULL,
freiz_strand_offen varchar(250) default NULL,
freiz_strand_entf varchar(250) default NULL,
freiz_fkk enum(‚ja‘,‚nein‘) default NULL,
freiz_fkk_offen varchar(250) default NULL,
freiz_fkk_entf varchar(250) default NULL,
golf_angren enum(‚ja‘,‚nein‘) default NULL,
golf_entf varchar(250) default NULL,
golf_loecher varchar(250) default NULL,
golf_ermaessigung varchar(250) default NULL,
anim_kinder enum(‚ja‘,‚nein‘) default NULL,
anim_kinder_von varchar(250) default NULL,
anim_kinder_bis varchar(250) default NULL,
anim_youth enum(‚ja‘,‚nein‘) default NULL,
anim_youth_von varchar(250) default NULL,
anim_youth_bis varchar(250) default NULL,
anim_adult enum(‚ja‘,‚nein‘) default NULL,
anim_adult_von varchar(250) default NULL,
anim_adult_bis varchar(250) default NULL,
anim_abends enum(‚ja‘,‚nein‘) default NULL,
anim_abends_von varchar(250) default NULL,
anim_abends_bis varchar(250) default NULL,
winter_loipe enum(‚ja‘,‚nein‘) default NULL,
winter_loipe_entf varchar(250) default NULL,
winter_lift enum(‚ja‘,‚nein‘) default NULL,
winter_lift_entf varchar(250) default NULL,
winter_schule enum(‚ja‘,‚nein‘) default NULL,
winter_schule_entf varchar(250) default NULL,
winter_bus enum(‚ja‘,‚nein‘) default NULL,
winter_bus_entf varchar(250) default NULL,
winter_bus_kostenlos enum(‚ja‘,‚nein‘) default NULL,
kur_gym enum(‚ja‘,‚nein‘) default NULL,
kur_gym_entf varchar(250) default NULL,
kur_arzt enum(‚ja‘,‚nein‘) default NULL,
kur_arzt_entf varchar(250) default NULL,
kur_therme enum(‚ja‘,‚nein‘) default NULL,
kur_therme_entf varchar(250) default NULL,
kur_massage enum(‚ja‘,‚nein‘) default NULL,
kur_massage_entf varchar(250) default NULL,
preis_erwachs varchar(250) default NULL,
preis_kind varchar(250) default NULL,
preis_kind_von varchar(250) default NULL,
preis_kind_bis varchar(250) default NULL,
preis_hund varchar(250) default NULL,
preis_jahr varchar(4) default NULL,
intern_preis_text varchar(100) default NULL,
strom_gebuehr varchar(250) default NULL,
strom_kwh varchar(250) default NULL,
strom_anschluss varchar(250) default NULL,
umwelt_wertstoff enum(‚ja‘,‚nein‘) default NULL,
umwelt_freiz enum(‚ja‘,‚nein‘) default NULL,
umwelt_oekostrom enum(‚ja‘,‚nein‘) default NULL,
umwelt_umwzert enum(‚ja‘,‚nein‘) default NULL,
umwelt_qualzert enum(‚ja‘,‚nein‘) default NULL,
selbst_beschr text,
buchung varchar(15) default NULL,
rueck_ansprech varchar(250) default NULL,
rueck_kontakt varchar(250) default NULL,
source varchar(250) NOT NULL default ‚unbekannt‘,
xxxxxx_bild_size varchar(50) default NULL,
xxxxxx_bild_type varchar(50) default NULL,
xxxxxx_bildnotizen text,
xxxxxx_notizen text,
text_korrigiert enum(‚ja‘,‚nein‘) default ‚nein‘,
datensatz_korrigiert enum(‚ja‘,‚nein‘) default ‚nein‘,
nnnnnn_geht_nicht enum(‚ja‘,‚nein‘) default NULL,
intern_bl_cache char(3) default NULL,
intern_bl_cache_date varchar(40) default NULL,
yy_xx_strasse varchar(200) default NULL,
yy_xx_plz varchar(5) default NULL,
yy_xx_ort varchar(100) default NULL,
yy_koordinaten varchar(30) default NULL,
intern_lon double(12,10) default NULL,
intern_lat double(12,10) default NULL,
intern_typ enum(‚manuell‘,‚aus XXXX‘,‚unbekannt‘) default ‚unbekannt‘,
intern_belong_to_poly_nr char(2) default NULL,
intern_poly_sort_nr int(11) default NULL,
intern_poly_xxxx varchar(50) default NULL,
intern_xx_seite smallint(3) default ‚-1‘,
intern_id_hash varchar(32) default NULL,
intern_be_not_gen enum(‚ja‘,‚nein‘) NOT NULL default ‚nein‘,
PRIMARY KEY (xxx_id)
) TYPE=MyISAM;

[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]

A_Hofmann · 13. Februar 2007 um 12:14

Tach.

Was soll daran ungewöhnlich sein? Es ist ein Katalog, da ist
natürlich soviel wie es geht ausgefüllt.

Wenn der Katalog wirklich 1000 oder mehr völlig gleichartige
Objekte enthält, besitzen natürlich alle Objekte die gleichen
Eigenschaften. Das kann ich mir aber bei 260
Einzeleigenschaften nicht denken.

Der Begriff Katalog ist vielleicht irreführend gewählt. Das Problem ist, das diese Datenbank Geschäftskritische Informationen enthält und ich nicht sagen kann was drin gespeichert wird. Aber es ist vom Prinzip her ein Katalog. Es ist eine Tabelle in der zu einer Gruppe von Dingen (z.B. Schuhe) lauter Einzelinformationen gespeichert werden. Natürlich hat ein Schuh nicht 280 relevante Einzelinformationen, aber die Gruppe die hier behandelt werden soll, hat sie.

Sagen wir mal, mein Katalog enthält Schuhe, Schrauben und
Autoreifen. Wenn ich alle diese Objekte in eine Tabelle würge,
bekomme ich - ohne Anspruch auf Vollständigkeit - folgende
Einzelfelder : Schuhgröße, Farbe, Herren/Damen/Kinder,
Material Ober"leder", Material Sohle, Länge, Schlüsselweite,
Gewindeart, Kopfform, Links/Rechtsgewinde, Oberfläche,
DIN/ISO, Hersteller, Zähigkeitsklasse, Winter/Sommer/Ganzjahr,
Profilart, Felgengröße, Einpresstiefe, Felgenart,
Geschwindigkeitsklasse.

„Meine“ Tabelle funktioniert nicht so. Stell dir einfach vor es werden nur Schuhe darin gespeichert, es gibt also keine unterschiedlichen „Warengruppen“ und jede Zeile hat in dieser Tabelle ca. 99% ausgefüllte Felder, „NULL“ kommt kaum drin vor.

Die Tabelle kann ich natürlich füllen - mit dem Effekt, dass
ich immer nur ein Drittel der angebotenen Felder nutze und
außerdem mächtig aufpassen muss bei der Datenpflege.

Das kann hier zum Glück nicht passieren.

Genau deswegen bildet man Objekte mit unterschiedlichen
eigenschaften in verschiedenen Tabellen ab. Ohne Deinen
Anwendungsfall zu kennen, kann Dir aber niemand verraten, ob
Du im Endeffekt 10 Tabellen brauchst oder vielleicht 50.

Gruß Eillicht zu Vensre

Ja das kann ich verstehen, tut mir leid wenn ich so geheimnisvoll rumtue. Ich habe im Thread weiter oben einen Dump der Tabellenstruktur gepostet, vielleicht kannst du daran mehr erkennen?
Man muß sich das auch so vorstellen: Das ganze war mal im Excel, bis man drauf kam das niemand garantieren kann das nicht irgendein Depp in der Tabelle irgendwo rumschreibt. Deswegen kam das ganze nach MySQL. Dann kamen immer neue Felder dazu und jetzt stehe ich vor dem Haufen und soll daraus etwas machen, das die nächsten 15 Jahre benutz- und erweiterbar bleibt. Meine Idee dazu habe ich im Ausganspost beschrieben (zwei tables, einer für die Daten, der andere für die Datendefinition), aber ich vermute, dass das eine total unperformate Lösung ist, die auch dem Programmierer zuviel Verantwortung überlässt (weil der durch keine Bugs die gesamte DB schrotten kann) und ziemlich viele SQL Features damit unterlaufen werden.

Eillicht_zu_Vensre_ac055a · 13. Februar 2007 um 14:36

Alas, you green nine!
Hallo.

Ich habe im Thread weiter oben einen Dump der Tabellenstruktur
gepostet, vielleicht kannst du daran mehr erkennen?

Ja, in der Tat *tüt*. Das erste, was ich sehe, ist, dass Du diese „Tabelle“ in den Harz kicken kannst … :-[

Das zweite ist, dass ich mir ungefähr vorstellen kann, was für Dateninhalte dahinter stecken. Aber wenn das geheim sein soll, sprechen wir einfach mal von Dingsbümsen.

[…] Felder dazu und jetzt stehe ich vor dem Haufen und soll daraus
etwas machen, das die nächsten 15 Jahre benutz- und erweiterbar
bleibt. […]

Diese Sorte Aufgabe ist mir nicht ganz unbekannt. ∋:-/

Ernsthaft : Mit einer Wartung des Modells ist niemandem gedient - hier muss eine vollständig normalisierte Struktur her, sonst finden zehn Katzen keine Maus. Wenn die Dateninhalte so homogen sind, wie Du sagst, müssen wir andere Kriterien zur Aufteilung finden.

Als erstes bietet sich an, festzulegen, welche Daten sozusagen „gusseiserner“ Stamm sind, also über die Lebensdauer eines Datensatzes unverändert bleiben, und wenn der ganze Schnee verbrennt. All diese Daten kommen in eine tbl_Dingsbums_Stamm. Bei Personen ist das z.B. das Geburtsdatum …

Ad Zwei : Welche Daten beschreiben das Objekt „an sich“, enthalten also grundsätzlich Stammdaten, die aber einer gelegentlichen Änderung unterliegen? Bei Personen ist das z.B. der Nachname. Diese Daten kommen in eine tbl_Dingsbums_Stamm_variabel, und zwar mit einem Bezug auf die Lebensdauer , d.h. es gibt eine 1:n- Beziehung
auf den Stamm, zum definierten Zeitpunkt aber nur einen gültigen Detailsatz …

Die dritte Hierarchieebene bei diesem Ansatz sind klassische Bewegungsdaten, die ebenfalls nur eine Ausprägung zum definierten Zeitpunkt, aber n Ausprägungen über die Lebensdauer aufweisen. Bei Personen ist das z.B. der Wohnsitz, die Telefonnummer … wieder 1:n zum Stamm, aber zum Zeitpunkt x nur ein gültiger Detailsatz.

Auf der vierten Ebene finden sich dann Bewegungsdaten, die mehrere Ausprägungen zum definierten Zeitpunkt aufweisen können. Bei Personen wären das z.B. Kinder. Beziehung 1:n zum Stamm.

Das wäre des Dramas erster Akt gewesen. Wir gehen als Nächstes an die Analyse eventueller Abhängigkeiten und versuchen daraus Nutzen für unsere Datenstruktur zu ziehen. So wäre es z.B. Unfug, Postleitzahl, Ort und Bundesland voneinander unabhängig zu speichern - Du verstehst, was ich meine? Wo kein Hallenbad ist, brauche ich keine Bahnlänge …

Dann gibt es natürlich an den Datenformaten so allerlei zu meckern : Ja/Nein ist schlicht BIT. VARCHAR (250) für Währungsfelder ist Schwachfug. VARCHAR (200) für eine Straße?

"An der Nordseeküste, am plattdeutschen Strand, wo der Großknecht am
 1234567890123456789012345678901234567890123456789012345678901234567
Deiche im Misthaufen stand"
89012345678901234567890123

sind erst 93 Zeichen … Na gut : VARCHAR frisst kein Brot; aber richtig gesund ist das trotzdem nicht.

Ich empfehle Dir dringend, Dich mit all diesen Argumenten und den Punkten, die Du selbst herausgefunden hast, an Deinen Auftraggeber zu wenden. Wenn der wirklich etwas Gescheites haben will, muss er einer vollständigen Neuentwicklung zustimmen. Und das ist nicht mit zwei durchgehackten Nächten erledigt, sondern erfordert erst mal eine ganze Menge konzeptioneller Arbeit.

Gruß Eillicht zu Vensre

A_Hofmann · 13. Februar 2007 um 15:26

Ernsthaft : Mit einer Wartung des Modells ist niemandem
gedient - hier muss eine vollständig normalisierte
Struktur her, sonst finden zehn Katzen keine Maus. Wenn die
Dateninhalte so homogen sind, wie Du sagst, müssen wir andere
Kriterien zur Aufteilung finden.

Das klingt schonmal super

Als erstes bietet sich an, festzulegen, welche Daten sozusagen
„gusseiserner“ Stamm sind, also über die Lebensdauer eines
Datensatzes unverändert bleiben, und wenn der ganze Schnee
verbrennt. All diese Daten kommen in eine
tbl_Dingsbums_Stamm. Bei Personen ist das z.B. das
Geburtsdatum …

Ich weiß nicht ob ich alles richtig verstanden habe, deswegen schreibe ich einfach mal wie ich das verstanden habe und wie es umsetzbar wäre auf meine Daten. Vielleicht lässt sich dann weiteres klären.

Also rein theoretisch kann sich bei jedem Datenaktualisierungslauf alles ändern, selbst der Name, Inhaber, Adresse. Somit alles, außer die unique ID der Spalte

Darüber habe ich mir auch Anfangs gedanken gemacht. Ich wollte alles aufteilen in eine Stammdatentabelle und eine wo erstmal der Rest reinkommt, oder eben nach logischen Gruppen unterteilt. Aber da hab ich mir gedacht das ich das Problem einfach nur nach hinten verschiebe, denn es kann trotzdem passieren das irgendwann eine der Tabellen zuviele Felder beinhaltet.

Deswegen hatte ich dann eine ganz andere Idee: Es gibt keine Stammdaten bis auf die ID. Also mache ich zwei Tables.

Einen für die Datendefinition:
CREATE TABLE fieldtypes (
uid int(11) NOT NULL auto_increment,
name varchar(200) NOT NULL default ‚‘,
datatype enum(‚varchar‘,‚text‘,‚int‘,‚double‘,‚enum‘,‚undefined‘) NOT NULL default ‚undefined‘,
datarules text, //hier kommt ein serialisierter Array rein der zeug definiert wie lang z.B. das feld sein darf oder welche optionen das enum feld hat. Ist eklig, weil nicht 2. normalform, ich weiß. Aber kann man ja noch verbessern.
allow_null enum(‚true‘,‚false‘) NOT NULL default ‚false‘,
default_value varchar(255) default NULL,
comment varchar(250) default NULL, //Kommentare für die User
group_id smallint(6) NOT NULL default ‚0‘, //zur optischen Gruppierung
sort_order int(11) NOT NULL default ‚-1‘,
PRIMARY KEY (uid),
UNIQUE KEY name (name),
UNIQUE KEY group_id (group_id,sort_order)
)

Und einen zweiten für die Daten an sich:

CREATE TABLE data (
uid int(11) NOT NULL auto_increment,
place_id int(11) NOT NULL default ‚0‘, //das ist der einzige Stammwert den ein Datensatz haben kann, alles andere kann sich ändern
type_id int(11) NOT NULL default ‚0‘, //hier kommt die uid von fieldtypes rein
data mediumtext NOT NULL,

//Der Rest dient einem Revisionsystem für arme: Änderungen werden nie per update gemacht, sondern per insert und es wird immer nur das aktuellste Feld der Daten geholt.
inserted timestamp NOT NULL,
editor_id smallint(6) NOT NULL default ‚0‘,
edit_id smallint(6) NOT NULL default ‚0‘,
edit_comment varchar(255) default NULL,
PRIMARY KEY (uid),
UNIQUE KEY place_id (place_id,type_id),
FULLTEXT KEY data (data)
)

Das war das „intelligenteste“ das ich mir ausdenken konnte. Aber wie gesagt es hat einen eklatanten Nachteil: ich muß alle Feldtypprüfungen zu Fuß im programm machen und wenn ich irgendwo einen Bug reinhaue, kann ich ungültige Daten produzieren, muß also noch zusätzlich Funktionen schreiben die meine DB überwachen ob alles so sein kann, wie es drinsteht.
Aber mir fällt einfach nichts besseres ein.

Ad Zwei : Welche Daten beschreiben das Objekt „an sich“,
enthalten also grundsätzlich Stammdaten, die aber einer
gelegentlichen Änderung unterliegen? Bei Personen ist das z.B.
der Nachname. Diese Daten kommen in eine
tbl_Dingsbums_Stamm_variabel, und zwar mit einem
Bezug auf die Lebensdauer , d.h. es gibt eine 1:n-
Beziehung
auf den Stamm, zum definierten Zeitpunkt aber nur einen
gültigen Detailsatz …

Das trifft, wie schon oben beschrieben, auf alle Einzelinformationen in der Tabelle zu (wenn ich das richtig verstanden habe, was du meinst). Also mir ist z.B. unklar warum 1:n Beziehung, doch nur weil man eben mehrere Möglichkeiten speichert, aber dazu noch speichert welcher davon die richtige Info ist, oder?

Die dritte Hierarchieebene bei diesem Ansatz sind klassische
Bewegungsdaten, die ebenfalls nur eine Ausprägung zum
definierten Zeitpunkt, aber n Ausprägungen über die
Lebensdauer aufweisen. Bei Personen ist das z.B. der Wohnsitz,
die Telefonnummer … wieder 1:n zum Stamm, aber zum Zeitpunkt
x nur ein gültiger Detailsatz.

Hmm hier verstehe ich den Unterschied zu den einen Absatz weiter oben beschriebenen Informationstypen nicht. Also Die Telefonnummer ist halt variabel, warum sollte die in eine andere Tabelle? Weil sie sicher nicht von einem anderen Datensatz „mitbenutzt“ wird?

Auf der vierten Ebene finden sich dann Bewegungsdaten, die
mehrere Ausprägungen zum definierten Zeitpunkt aufweisen
können. Bei Personen wären das z.B. Kinder. Beziehung 1:n zum
Stamm.

Also wenn ich das richtig verstanden habe, gibt es solche Daten bei mir nicht. Also ein Datensatz hat entweder z.B. 100 Stellplätze, oder er hat 200. Aber es wird nie sinn machen zu notieren das er 100+200 oder so hat.

Das wäre des Dramas erster Akt gewesen. Wir gehen als Nächstes
an die Analyse eventueller Abhängigkeiten und versuchen daraus
Nutzen für unsere Datenstruktur zu ziehen. So wäre es z.B.
Unfug, Postleitzahl, Ort und Bundesland voneinander unabhängig
zu speichern - Du verstehst, was ich meine? Wo kein Hallenbad
ist, brauche ich keine Bahnlänge …

Ja das kann ich vom Prinzip her nachvollziehen, aber wie bilde ich denn so etwas in SQL ab? Also sobald ich irgendwo das Feld „Bahnlänge“ brauche existiert es doch in der DB und kann von allen benutzt werden.

Dann gibt es natürlich an den Datenformaten so allerlei zu
meckern : Ja/Nein ist schlicht BIT.

Ah, ich dachte MySQL macht das für mich wenn ich Enum benutze?

VARCHAR (250) für Währungsfelder ist Schwachfug.

Jo! zum glück war ich das nicht

VARCHAR (200) für eine Straße?

"An der Nordseeküste, am plattdeutschen Strand, wo der
Großknecht am

1234567890123456789012345678901234567890123456789012345678901234567
Deiche im Misthaufen stand"
89012345678901234567890123

sind erst 93 Zeichen …

Garnicht mal schlecht, das längste Straßenfeld in der DB ist exakt 94 Zeichen lang (hat mich jetzt aber auch überrascht)

Na gut
VARCHAR frisst kein Brot; aber richtig gesund ist das
trotzdem nicht.

Ich empfehle Dir dringend, Dich mit all diesen Argumenten und
den Punkten, die Du selbst herausgefunden hast, an Deinen
Auftraggeber zu wenden. Wenn der wirklich etwas Gescheites
haben will, muss er einer vollständigen Neuentwicklung
zustimmen. Und das ist nicht mit zwei durchgehackten Nächten
erledigt, sondern erfordert erst mal eine ganze Menge
konzeptioneller Arbeit.

Ja, der hat dem schon zugestimmt. Ich bin ja gerade dran das alles wegzuwerfen und neu zu machen. Aber ich habe jetzt eben ein paar Tage mein oben beschriebenes 2-Table Konzept ausporbiert und bin zu dem Schluß gekommen das es zwar funktioniert, ich mir damit aber keinen Gefallen tue.

Gruß Eillicht zu Vensre

Vielen Dank das du dich da so reingedacht hast!

A_Hofmann · 13. Februar 2007 um 15:40

Hallo,

in einem Katalog musst Du doch bestimmt funktionale/logische
Gruppen haben, welche du in einem anderen Table mit x:x
Beziehung auslagern kannst. Besonders bei 260 Eigenschaften.
Ich kenne Deinen Kenntnisstand nicht, also nimms mir nicht
übel wenn ich Dir jetzt was sag was Du schon weisst: google
mal nach 3ter Normalform für einen DB. Bei 260 Feldern kann
ich mir nicht vorstellen, dass Du keine Redundanzen hast.

Gruß Jakob

Hallo,

Zu meinem Kenntnisstand: ich programmiere seit ca. 7 Jahren PHP und baue mal weniger, mal mehr komplexe Programme um eine SQL Datenbank. Ich bin zwar wirklich kein Profi, aber mache das mit Erfahrung (und vielen gemachten Fehlern) wieder wett
Leider gibt es sowas wie Warengruppen nicht. Ich kannte zwar nicht die wörtliche Definition von der 3. Normalform, aber ich baue seit Jahren Datenbanken nach dem Prinzip zusammen und bin mir daher sicher das es bei dieser nicht vorgekommen ist. Ich würde ja auch nicht fragen, wenn diese Tabelle nicht ziemlich ungewöhnlich wäre.

Jens_dce009 · 15. Februar 2007 um 12:23

Hi,
also prinzipiell sind 260 Spalten in einer Tabelle kein Problem.
Wenn MySQL damit ein technisches Problem hat, kann man die Tabelle in N Teile (N ist eine kleine ganze Zahl) zerlegen und die mit einer 1:1 Beziehung versehen.

Oder man wechselt auf ein DB-System was sowas abkann. Bei deiner Beschreibung kommt mir Oracle XE in den Sinn. Das müsste wenigsten 1000Spalten abkönnen, für deine Zwecke ausreichend sein und ist meines Wissens Lizenskostenfrei.

Wenn ich mir den Dump anschaue, kommen schon einige Ideen, was man Designtechnisch verändern könnte. Es tauchen gleichartige Gruppen mehrfach auf, z.B.

sorg_shop enum(‚ja‘,‚nein‘) default NULL,
sorg_shop_offen varchar(250) default NULL,
sorg_shop_entf varchar(250) default NULL,

taucht mehrfach auf, wobei shop jeweils durch etwas anderes ersetzt ist. Da KÖNNTEN zwei separate Tabellen sinn machen:

Tabelle Infrastruktur_typ
id
name varchar2(20) (enthält ‚shop‘, ‚imbiss‘ …)

Tabelle Infrastruktur
typ_id, verweist of den infrastruktur typ
dingsbums_id – verweist auf deine ursprüngliche Tabelle
sorg_shop_offen varchar(250) default NULL,
sorg_shop_entf varchar(250) default NULL,

das feld sorg_shop entfällt, da es dadurch abgebildet wird, ob eine entsprechender Datensatz existiert oder nicht, so interpretier ich zumindest die Spalten in deiner Tabelle.

Vorteil: Wenn jemand entsprechende Informationen über einen weiteren Infrastrukturtyp pflegen will, braucht man nur einen neuen Eintrag in der Tabelle Infrastruktur_typ.

Nachteil: Wenn man die Daten zu allen Infrastrukturtypen in einer Zeile haben will, muss man übel joinen.

Was in deinem Fall sinnvoll ist kann man nicht entscheiden, ohne wesentlich mehr über die darauf aufbauende Anwendung zu wissen.

So ähnliche Änderungen bieten sich auch für viele andere Gruppen an, die in dem Dump zu erkennen sind.

HTH
Jens

Jens_dce009 · 15. Februar 2007 um 14:58

Deswegen hatte ich dann eine ganz andere Idee: Es gibt keine
Stammdaten bis auf die ID. Also mache ich zwei Tables.

Einen für die Datendefinition:
CREATE TABLE fieldtypes (
uid int(11) NOT NULL auto_increment,
name varchar(200) NOT NULL default ‚‘,
datatype
enum(‚varchar‘,‚text‘,‚int‘,‚double‘,‚enum‘,‚undefined‘) NOT
NULL default ‚undefined‘,
datarules text, //hier kommt ein serialisierter Array rein
der zeug definiert wie lang z.B. das feld sein darf oder
welche optionen das enum feld hat. Ist eklig, weil nicht 2.
normalform, ich weiß. Aber kann man ja noch verbessern.
allow_null enum(‚true‘,‚false‘) NOT NULL default ‚false‘,
default_value varchar(255) default NULL,
comment varchar(250) default NULL, //Kommentare für die
User
group_id smallint(6) NOT NULL default ‚0‘, //zur optischen
Gruppierung
sort_order int(11) NOT NULL default ‚-1‘,
PRIMARY KEY (uid),
UNIQUE KEY name (name),
UNIQUE KEY group_id (group_id,sort_order)
)

Und einen zweiten für die Daten an sich:

CREATE TABLE data (
uid int(11) NOT NULL auto_increment,
place_id int(11) NOT NULL default ‚0‘, //das ist der
einzige Stammwert den ein Datensatz haben kann, alles andere
kann sich ändern
type_id int(11) NOT NULL default ‚0‘, //hier kommt die uid
von fieldtypes rein
data mediumtext NOT NULL,

Und wenn du dir einen Datensatz anschauen willst machst du einen 250fach outer Join? Bitte, bitte lass das sein.
Was du da planst heisst ein RDBMS zu bauen, auf Basis eine RDBMS,
das ist wirklich keine gute Idee.

Jens

A_Hofmann · 15. Februar 2007 um 15:25

Und wenn du dir einen Datensatz anschauen willst machst du
einen 250fach outer Join? Bitte, bitte lass das sein.
Was du da planst heisst ein RDBMS zu bauen, auf Basis eine
RDBMS,
das ist wirklich keine gute Idee.

Jens

Ja, im grunde wird die Db zum Zettelkasten kastriert, das ist wirklich übel. Aber der Select zum anschauen der Daten ist ziemlich simpel:

SELECT d.data, t.*
FROM data AS d, fieldtypes AS t
WHERE d.place_id = 5 AND d.type_id = t.uid
ORDER BY t.group_id DESC, t.sort_order ASC

da fehlen natürlich noch die felder, die nicht ausgefüllt sind, das macht dann ein

SELECT * FROM fieldtypes WHERE uid
NOT IN (SELECT type_id FROM data WHERE place_id = 5)

Das Hauptproblem das ich kein besseres Design zustande bekomme ist einfach meine Dummheit. Auch wenn ich eure Vorschläge total prima finde, ich bin zu dumm um es umsetzen zu können und ich würde sicherlich irgendwann später an irgendeinem etwas komplexeren Query verrecken, das macht ja auch kein Sinn. Von daher nehme ich es jetzt in kauf das ich eine schlechte Lösung baue, solange ich am ende ein Produkt habe, das man benutzen kann. Wenn ich in ein paar Jahren schlauer bin, kann man das auch an einem Wochenende neuschreiben, die Software drumrum ist simpel.

Ulrich_Vaid · 16. Februar 2007 um 10:31

Ja, im grunde wird die Db zum Zettelkasten kastriert, das ist
wirklich übel. Aber der Select zum anschauen der Daten ist
ziemlich simpel:

Das Hauptproblem das ich kein besseres Design zustande bekomme
ist einfach meine Dummheit. Auch wenn ich eure Vorschläge
total prima finde, ich bin zu dumm um es umsetzen zu können
und ich würde sicherlich irgendwann später an irgendeinem
etwas komplexeren Query verrecken, das macht ja auch kein
Sinn. Von daher nehme ich es jetzt in kauf das ich eine
schlechte Lösung baue, solange ich am ende ein Produkt habe,
das man benutzen kann. Wenn ich in ein paar Jahren schlauer
bin, kann man das auch an einem Wochenende neuschreiben, die
Software drumrum ist simpel.

-Ich übersetzte mal den Begriff Dummheit mit fehlendem KnowHow. Aber wenn du so eine Bastellösung, wie dir vorschwebt, machen willst, wirst du das KnowHow auch nicht aufbauen können. Also investieren doch besser etwas Zeit und mach es richtig --> Deinen Applikaiton wird besser laufen, wenn jemand anders mal dran muss, wird er es dir danken und dein Wissen wird erweitert