Einträge einer Tabelle pro Jahr splitten

[Forumsgast]

Hallo,

folgendes Szenario:

Ich habe eine Kalenderanwendung bei der jeder User für jeden Tag eine Uhrzeit (von, bis) und eine Notiz eintragen kann.

Die entscheidenden Tabellen sind im Entwicklungsstatus und sind wie folgt aufgebaut:

CREATE TABLE kalendereintraege (
  id INTEGER UNSIGNED  NOT NULL   AUTO_INCREMENT,
  datum DATETIME  NOT NULL  ,
  von TINYINT UNSIGNED  NOT NULL  ,
  bis TINYINT UNSIGNED  NOT NULL  ,
  tag_kuerzel TINYINT UNSIGNED  NOT NULL  ,
  notiz VARCHAR(255)  NULL    ,
PRIMARY KEY(id)  ,
INDEX kalendereintraege_datum_index(datum))
TYPE=InnoDB;

CREATE TABLE benutzer (
  id INTEGER UNSIGNED  NOT NULL   AUTO_INCREMENT,
  benutzername VARCHAR(20)  NOT NULL  ,
  passwort VARCHAR(20)  NOT NULL  ,
  geaendert DATETIME  NOT NULL  ,
  erstellt DATETIME  NOT NULL    ,
PRIMARY KEY(id)  ,
INDEX benutzer_benutzername_index(benutzername)
TYPE=InnoDB;

CREATE TABLE benutzer_hah_kalendereintraege (
  benutzer_id INTEGER UNSIGNED  NOT NULL  ,
  kalendereintraege_id INTEGER UNSIGNED  NOT NULL    ,
PRIMARY KEY(benutzer_id, kalendereintraege_id)  ,
INDEX benutzer_hat_kalendereintraege_FKIndex1(benutzer_id)  ,
INDEX benutzer_hat_kalendereintraege_FKIndex2(kalendereintraege_id),
  FOREIGN KEY(benutzer_id)
    REFERENCES benutzer(id)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION,
  FOREIGN KEY(kalendereintraege_id)
    REFERENCES kalendereintraege(id)
      ON DELETE NO ACTION
      ON UPDATE NO ACTION)
TYPE=InnoDB;

Nun meine Fragen.

Wie ist es für die Performance am Besten?
Lege ich für jedes Jahr eine neue Tabelle an oder kommen alle Einträgen von den nächsten x-Jahren in eine Tabelle?

Ist eine Abfrage wie z.B.

select * from kalendereintraege where DATE_SUB(CURDATE(), INTERVAL 30 DAY) <= datum;

mit einem Index auf das Datum noch für alle User schnell genug?
Denn die Tabelle "kalendereinträge" kann bei 3.000 Usern nach einem Jahr schnell mal über 1.000.000 Datensätze enthalten.

Vielen Dank für Eure Tipps, Ratschläge und Verbesserungsvorschläge.

Gruß
coding007.

[Forumsgast]

Hallo coding007, Willkommen hier im Forum und danke für Deine vorbildliche Fragestellung

Inwieweit sind denn alte Kalendereinträge von Nutzern noch relevant. Käme eine Archivierung in Frage (alte Einträge nur noch im - bspw. langsameren - Archiv verfügbar)? Prinzipiell halte ich verschiedene Tabellen für wenig sinnvoll.

[Forumsgast]

Ich würde auch alle Jahre in einer Tabelle lassen. Bisher noch nicht angedachte Suchvorgänge oder Relationen sind sicher einfacher, wenn man nicht tabellenübergreifend kodieren muss.


Nicht verstanden habe ich dein Datenmodell. Kann man die benutzer_hah_kalendereintraege nicht einsparen?

Edit/Nachtrag: ... wenn man in die Tabelle kalendereinträge noch die Userid als Referenzfeld aufnimmt.

[Forumsgast]

Danke schon mal...

Ok, eine Archivierung ist auf jeden Fall sinnvoll. Sehr gute Idee.
Jahre die älter sind als das letzte Jahre, fliegen in die Archiv-Tabelle.
Kann sich meine API drum kümmern

Bei den Datenbankdesign ist ein Fehler. Ich wollte nur einen Teil (leichter zum Verständnis) meiner DB aufzeigen und dabei ist mir ein Fehler unterlaufen.
Natürlich bekommt die Tabelle "kalendereintraege" noch einen Fremdschlüssel auf die Tabelle "benutzer".

Die Archivierung würde ich dann am Besten mittels CronJob starten und die Archiv-Tabelle befüllen? Müsste dann die Seite am 01.01.xxxx 0:00:01 immer kurz offline sein, um die Verschiebung der Daten durchzuführen? Oder wie macht Ihr so was?

Wer noch Tipps für die Perfomance hat, nur her damit...

[Forumsgast]

Da die Beziehung vom Benutzer zu den Einträgen eine 1:n-Beziehung ist, muss ich Wolla recht geben. Ausserdem gibt es für das Datum den DATE- und für von/bis den TIME-Datentyp. Das Tag-Kürzel ist überflüssig. Es lässt sich jederzeit berechnen, und berechnete Daten haben in der DB nichts verloren.
Das Passwort sollte eigentlich nicht im Klartext gespeichert werden, deshalb werden VARCHAR(20) nicht reichen, je nach Codierung brauchst du 32 (md5) bis 40 (sha1) oder mehr Stellen.

[Forumsgast]

Da die Beziehung vom Benutzer zu den Einträgen eine 1:n-Beziehung ist, muss ich Wolla recht geben.

Habe ich schon zu Stellung genommen, Danke.

Ausserdem gibt es für das Datum den DATE- und für von/bis den TIME-Datentyp. Das Tag-Kürzel ist überflüssig. Es lässt sich jederzeit berechnen, und berechnete Daten haben in der DB nichts verloren.

Stimmt, den Datentyp TIME muss ich hier unbedingt einsetzen.
Tag-Kürzel ist ein Dienstkürzel, Bezeichnung eventuell für euch irreführend.

Das Passwort sollte eigentlich nicht im Klartext gespeichert werden, deshalb werden VARCHAR(20) nicht reichen, je nach Codierung brauchst du 32 (md5) bis 40 (sha1) oder mehr Stellen.

Da hast du auf jeden Fall Recht. Es ist jetzt ein CHAR(40).

Vielen Dank!

Ich werde die DB die nächsten 1-2 Wochen komplett aufbauen und dann mal die Struktur inkl. DB-Design mal posten...

[Forumsgast]

Ergänzungsfrage:

Wofür setzt man sinnvollerweise den time-Feldtyp ein? Mir fällt da grade nichts zu ein.

[Forumsgast]

Das Beispiel hat dir coding007 ja eben geliefert: zwei Zeitpunkte am gleichen Tag => einmal DATE und zwei mal TIME

[Forumsgast]

Jap. Wenn man speichern will, dass man von 13:15 bis 14:17 in der Mittagspausee war, dann ist mir das auch klar. Da ich time bis grade nicht kannte, hätte ich wohl zwei datetime-Stempel gebaut.
Ich hatte nur im MySQL-Handbuch gelesen, dass time auch Zeiten von +/- 840 Stunden verarbeitet, und ich suchte nach einem Anwendungsbeispiel dafür.