Planung von Tabellen und Spalteneinträgen

P

Papp Nase

Aktiver Benutzer
Beiträge
48
Wie plant man geschickt eine Datenbank?

Nehmen wir mal an, wir haben 20 Fütterungsautomanten, in denen Haus- und Wildtiere gefüttert werden. Die Fütterungsautomaten bekommen die Namen F1, F2 ... F20. Der Datensatz in allen Fütterungsautomaten ist identisch

Zeitstempel, Futtermenge, Wassermenge

Die Futterautomaten geben Wasser und Futter stets zum gleichen Zeitpunkt ab, sagen wir mal zu jeder vollen Stunde. Jeder Futterautomat sendet getrennt vom anderen Automaten seine Daten an die Datenbank, die Zeitstempel sind also pro Intervall identisch.

Wie plant man jetzt geschickt die Tabellen mit Spalten?

In der späteren Auswertungen möchte ich z.B. sehen, wann zu welcher Uhrzeit wie viel Wasser und wieviel Futter jeder Automat ausgegeben hat und ich will auch Summen von einzelnen Gruppen bilden könnnen oder auch die Gesamtmengen aller Automaten.

Variante 1:
Jetzt könnte ich für jeden Futterautomaten eine eigene Tabelle erstellen mit den drei Spalten
Zeitstempel, Futtermenge, Wassermenge

Variante 2: Eine Tabelle mit vielen Spalten:
Zeitstempel, Futtermenge F1, Wassermenge F2, Futtermenge F2, Wassermenge F2...

Vorteil Variante 1:

Ich kann schnell die Werte in die Tabellen schreiben. Jeder Futterautomat sendet seine Daten unabhängig an den Server. Ich brauch nicht darauf achten, ob eine Zeile mit dem Zeitstempel schon vorhanden ist und dann die Tabellenwerte des fehlenden Automaten anfüngen.
Bei Variante 2 muss ich prüfen, ob bereits ein Zeitstempel schon vorhanden ist und dann die Spaltenwerte an den Stellen ergänzen.

Vorteil Variante 2:

Für die spätere Auswertung habe ich nun bereits alle Spaltenwerte in einer Tabelle zusammengefasst. Möchte ich Werte aus dem Beispiel der Variante 1 erzeugen, dann muss ich erst die Werte aus den einzelnen Tabellen in einer Tabelle zusmmenfassen.
Außerdem wird hier etwas Speicherplatz gespart. Der Zeitstempel wird nur einmal gespeichert, bei 20 Einzeltabellen 20mal.

Irgendwie hat jede der beiden Varianten ihre Vor- und Nachteile. Gibt es noch weitere Möglichkeiten für dieses Modellbeispiel?
 
Werbung:
Variante 3:
R= (Automat, Zeitstempel, Futtermenge, Wassermenge)

Du schreibst alle Datensätze in eine Tabelle mit einem zusammengesetzten Primärschlüssel. So ist es egal ob du einen oder 200 Automaten hast. Ebenso ist es egal zu welcher genauen Urzeit die Daten ankommen.

Filtern kannst du später problemlos nach Automat, Zeit oder beiden Werten.
 
Genau. Ein Schlüssel muss ein Tupel eindeutig kennzeichnen. Aus wie vielen Attributen ein Schlüssel besteht ist dabei Nebensache.
 
Leider funktioniert es noch nicht. Ich habe mit dem phpmyadmin-tool eine Tabelle erzeugt mit

AutomatenID, Zeitstempel, Wasser, Futter.

Dabei habe ich AutomatenID und Zeitstempel beide als Primärschlüssel angewählt (siehe Bild datenbankbild.JPG)

Dann habe ich einmal den Befehl ausgeführt:
Code: 
 INSERT INTO `futtermengen`(`AutomatID`, `Zeitstempel`, `Wasser`, `Futter`) VALUES (100, 10, 30, 40)

Den gleichen Befehl habe ich dann in dieser Form ausgeführt:
Code: 
 INSERT INTO `futtermengen`(`AutomatID`, `Zeitstempel`, `Wasser`, `Futter`) VALUES (100, 110, 30, 40)

Ich habe also dort den Zeitstempel verändert. Leider kam die Fehlermeldung, dass der Primärschlüssel bei AutomatID bereits vorhanden sei. Ich dachte aber dadurch, dass ich beide Spalten als Primärschlüssel auswählte, würde nur dann ein Fehler auftreten, wenn beide Spalten identisch sind.
 

Anhänge

  • datenbankbild.JPG
    datenbankbild.JPG
    143,9 KB · Aufrufe: 7
Papp Nase schrieb:
Ich habe also dort den Zeitstempel verändert. Leider kam die Fehlermeldung, dass der Primärschlüssel bei AutomatID bereits vorhanden sei. Ich dachte aber dadurch, dass ich beide Spalten als Primärschlüssel auswählte, würde nur dann ein Fehler auftreten, wenn beide Spalten identisch sind.
Zum Vergrößern anklicken....

Da denkst du auch richtig. Ich vermute einen Fehler in der Tabellendefinition.
Code: 
CREATE TABLE IF NOT EXISTS futtermengen (
  AutomatID int(11) NOT NULL,
  Zeitstempel int(11) NOT NULL,
  Wasser int(11) NOT NULL,
  Futter int(11) NOT NULL,
  PRIMARY KEY (AutomatID,Zeitstempel)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO futtermengen (AutomatID, Zeitstempel, Wasser, Futter) VALUES(100, 10, 30, 40);
INSERT INTO futtermengen (AutomatID, Zeitstempel, Wasser, Futter) VALUES(100, 110, 30, 40);

Das hier funktioniert bei mir ohne Probleme.

Nachtrag:
Für Zeitstempel gibt es sinnvollere Datentypen als (BIG)INTEGER. Für diesen Fall bietet sich TIMESTAMP an. Das Feld kann dann mit einer Funktion wie NOW() gefüllt werden.
 
Zuletzt bearbeitet:
Bei mir funktioniert es leider nicht. Ich habe jetzt die alte Tabelle mal gelöscht und sie mit den Befeheln von Dir neu eingerichtet:

Hony% schrieb:
Code: 
CREATE TABLE IF NOT EXISTS futtermengen (
  AutomatID int(11) NOT NULL,
  Zeitstempel int(11) NOT NULL,
  Wasser int(11) NOT NULL,
  Futter int(11) NOT NULL,
  PRIMARY KEY (AutomatID,Zeitstempel)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Zum Vergrößern anklicken....

Das geht:
Code: 
INSERT INTO futtermengen (AutomatID, Zeitstempel, Wasser, Futter) VALUES(100, 10, 30, 40);

Das geht nicht mehr:
Code: 
 INSERT INTO futtermengen (AutomatID, Zeitstempel, Wasser, Futter) VALUES(100, 110, 30, 40);

Fehlermeldung:
Code: 
#1062 - Duplicate entry '100' for key 'AutomatID'
 
Ich hab die Version 5.6.11.

Ich bekomme nämlich zwei unterschiedliche Fehlermeldungen.

Schreibe ich zweimal hintereinander:
Code: 
 INSERT INTO futtermengen (AutomatID, Zeitstempel, Wasser, Futter) VALUES(100, 110, 30, 40)

Dann kommt die Meldung:
Code: 
 #1062 - Duplicate entry '100-110' for key 'PRIMARY'

Mach ich das:

Code: 
INSERT INTO futtermengen (AutomatID, Zeitstempel, Wasser, Futter) VALUES(100, 110, 30, 40) ;
INSERT INTO futtermengen (AutomatID, Zeitstempel, Wasser, Futter) VALUES(100, 111, 30, 40);

Fehlermeldung:
Code: 
 #1062 - Duplicate entry '100' for key 'AutomatID'

Beim zweiten Versuch sucht er nur nach doppelten Einträgen in AutomatID.
 
Papp Nase schrieb:
Ich hab die Version 5.6.11.
Zum Vergrößern anklicken....
Ich kann das Problem weder mit einer 5.5 Version noch mit 5.6.16 nachvollziehen. Eine 5.6.11 hatte ich auf die Schnelle leider nicht verfügbar.

Mir fehlt ehrlich gesagt jede Erklärung für dieses Verhalten. Die Fehlermeldungen sind bei MySQL auch nicht sonderlich aussagekräftig.

MySQL verhält sich leider in einigen Dingen ein wenig seltsam. Ich würde dir ja empfehlen SQLite oder PostgreSQL zu benutzen. SQLite ist eine sehr kompakte lokale Datenbank während PostgreSQL ein vollwertiges Client/Server System ist.

FEHLER: doppelter Schlüsselwert verletzt Unique-Constraint "futtermengen_pkey"
DETAIL: Schlüssel "(automatid, zeitstempel)=(100, 10)" existiert bereits.
Zum Vergrößern anklicken....

So sieht zum Beispiel die Fehlermeldung in PostgreSQL aus. Als Oberfläche gibt es da pgAdmin III.
 
Ich hab den Fehler gefunden. Ich habe in dem phpmyadmin-Tool aus versehen auf den Button "Index" geklickt immer nach dem Erstellen. Ich hatte also zwei Indizes - PRIMARY, der auf AutomatID und Zeitstempel verwies - und dann nochmal einen Index auf AutomatID. Ich hatte später nochmal die Tabelle mit anderem Namen neu erstellt und dann klappte es plötzlich.

Das phpmyadmin-Tool zeigte in der Tabelle wie auf dem Bild von mir nicht an, wo zusätzliche Schlüssel vergeben wurden. Aber dann gab es die Möglichkeit, eine Druckansicht über den Aufbau der Tabelle zu erstellen, und da gibt es dann eine Tabelle, in der alle Indizes aufgeführt wurden. Bei der ersten von mir waren dann mehr Indizes drinnen, bei der neuen, die ich dann nochmal mit Deinem Code erstellte, aber mit anderem Namen, war dann der andere Index auf AutomatiD nicht mehr vorhanden - plötzlich klappte es dann auch mit dem Einfügen - es muss also an diesem zustäzlichen Index gelegen haben.
 
Papp Nase schrieb:
Deinem Code erstellte, aber mit anderem Namen, war dann der andere Index auf AutomatiD nicht mehr vorhanden - plötzlich klappte es dann auch mit dem Einfügen - es muss also an diesem zustäzlichen Index gelegen haben.
Zum Vergrößern anklicken....

Freut mich zu lesen. Mit PostgreSQL wäre dir die Fehlersuche erspart geblieben, da der verletzte Constraint angezeigt wird. :)
 
Ich bin auf den Befehl INSERT DELAYED gestoßen.

Bezogen auf das Beispiel mit den 20 Futterautomaten - wenn jeder Futterautomat separat seine Daten an die Datenbank sendet macht es dabei Sinn, grundstäzlich die INSERT-Anweisung mit DELAYED zu erweitern, damit die Daten in der Warteschlange zwischengespeichert werden?
 
Hony% schrieb:
Freut mich zu lesen. Mit PostgreSQL wäre dir die Fehlersuche erspart geblieben, da der verletzte Constraint angezeigt wird. :)
Zum Vergrößern anklicken....
Naja, es ist nur - jetzt hab ich erstmal mit der MySQL-Datenbank angefangen, die kennenzulernen und damit ein bißchen herumzuspielen. Ich hab das jetzt grad alles so eingerichtet, dass das System auf einem Server läuft und ich vom Netzwerk aus drauf zugreifen kann - und jetzt wieder nochmal mit einer anderen Datenbanksprache anzufangen, möcht ich erstmal versuchen zu vermeiden.
 
Werbung:
Papp Nase schrieb:
Ich bin auf den Befehl INSERT DELAYED gestoßen.
Zum Vergrößern anklicken....
Das wird nur benötigt wenn die Anfragezeiten sehr lange dauern weil der Server unter hoher Last steht. Bei gerade mal 20 Automaten wird das aber kaum der Fall sein. Bei so geringer Last wirkt sich DELAYED vermutlich eher negativ aus.

Papp Nase schrieb:
und jetzt wieder nochmal mit einer anderen Datenbanksprache anzufangen, möcht ich erstmal versuchen zu vermeiden.
Zum Vergrößern anklicken....
Kann ich verstehen. Ich will dir auch nicht das DBMS vorschreiben. Meine Empfehlung ist einfach wenn möglich von MySQL Abstand zu nehmen. MySQL hat einfach einige Eigenarten die sich so bei keinem anderen System finden lassen.
 
Zurück
Oben