SQL kleine Datenbank designen

[urki]

Hi,

mit Datenbankabfragen hab ich nicht wirklich ein Problem, aber ich habe noch nie eine eigene erstellt. Dazu habe ich mir auch schon einiges durchgelesen, aber dadurch das es auf einer Wocheansicht und Wochenübersicht basieren soll, dass macht mir Probleme. Irgendwie habe ich da eine blockade und wenn ich eine neue Idee habe, hat es meist genausoviele nachteile.



Vom Prinzip her funktioniert es schon, allerdings werden die Abfragen immer unübersichtlicher mit jeder Funtion die hinzu kommt. Eigentlich war auch gar nicht geplant, dass es umfangreicher wird.

Im Prinzip geht es darum die Daten aus einem Wettbewerb zu speichern. Hinterher soll es im Programm eine Wochenübersicht über alle Wochen oder einen Zeitraum geben und eine Ansicht, in der man sich eine Woche genauer angucken kann. In der Wochen übersicht soll dann für alle Tage, die in der Woche liegen, "count" zusammen gerechnet und für jeden Benutzer als spalte angezeigt werden. In der Wochenansicht einfach "count" für jeden Tag pro Benutzer.

Funktionieren tut es auch so, aber ich denke es ist nicht elegant gelöst und da es vermutlich noch erweitert wird und noch nicht so groß ist, überlege ich über einen anderen/besseren Ansatz, aber stecke irgendwie fest.

- preafix_week enthält derzeit das start und enddatum einer Woche, ob noch etwas eingetragen werden darf und wer gewonnen hat.

- preafix_user enthält benutzer daten und wann er das letzte mal etwas eingetragen hat, der er alles danach nicht sehen soll.

-preafix_day_contest1 speichert den Tag, die Woche in der der Tag liegt, den zugehörigen User und was er gemacht hat.

-praefix_std_week ist eigentlich nur ein Wochengrüst, in dem einmal alle Wochentage (Montag bis Sonntag) und die Differenz zum ersten Wochentag steht. Das benutze ich nur um eine Wochenansicht aufzubauen/abzufragen. Sollte eigentlich auch über das Programm selber gehen.

Im mom habe ich für jeden Wettbewerb eine weitere Tabelle praefix_day_contestX erstellt, wobei ich beim erstellen des Diagramms selber gemerkt, dass es schwachsinnig ist. Hier wäre wohl eine weitere Tablle in der die verschiedenen Wettbewerbe gespeichert sind und einer id in praefix_day die auf diese Tabelle verweist wesentlich sinnvoller ...

Ich hoffe, ihr habt vielleicht ein paar Tips, bislang kommt mir mein Design ziehmlich beschi... vor. Bislang habe ich aber nichts ähnliches gefunden. Danke im voraus.

 

[captmcneil]

Also auf den ersten Blick ist mir folgendes aufgefallen:

- Wie Du schon selbst sagst, wenn es mehrere Wettbewerbstypen oder so gibt, ist das ja im Grunde eine Vererbungshierarchie. Das ist über eine RDB schwer (bzw. auf verschiedene Arten) darstellbar. Ich würde hier auch sehr dazu tendieren, eine Haupttabelle "Contest" zu erstellen mit einem Verweis auf den Tag, und dann weitere Tabellen für jeden "Untertyp" mit Foreign Key auf die Contest-Tabelle.

- Das Speichern von Datumswerten in der Week-Tabelle, nur um die Wochenintervalle hinzurkriegen, würde ich lassen. In der Relation willst Du ja nur wissen, ob die Woche abgeschlossen ist, und wenn ja, wer gewonnen hat. Dazu 2 Anmerkungen: 1.) Ich würde statt 2 Datumswerten lieber die Kalenderwoche und das Jahr speichern, und auf die zwei Spalten einen Unique-Constraint setzen. Ist aus Integritätssicht viel sicherer. Also eine Spalte "calendar_week" und eine Spalte "year", jeweils vom Typ int. Es stellt sich auch die Frage, ob Du das Flag "Closed" wirklich brauchst; ist ein Wettbewerb nicht automatisch geschlossen, wenn ein "Winner" feststeht? Wenn ja, diese Information nicht duplizieren, sonst muss Deine Anwendung ja auf den Fehlerfall reagieren, dass zB Closed = true ist, aber Winner = null. Da erzeugst nur unnötigen Aufwand.

- In dem Zusammenhang würd ich auch die Std_Week-Tabelle weglassen. In die Datenbank gehören nur Informationen, die ansonsten verloren wären. Nur in besonderen Fällen sollte man Ergebnisse, die man auch wieder errechnen kann, in der DB ablegen - nämlich, wenn die Berechnung sehr zeitaufwändig ist. Also wenn eine Anwendung bspw. tagelang eine Simulation durchrechnet, macht es natürlich Sinn, das Ergebnis zu speichern, auch wenn man es ohne probleme mit den Startparametern wieder errechnen könnte. Aber sowas wie Kalenderwochen ist eigentlich was, was Du in Deiner Anwendung lösen solltest. In Java gibts da zB die Calendar-Klasse. Ein guter Datenbankentwurf zeichnet sich halt in aller Regel dadurch aus, dass keine unnötigen Informationen enthalten sind, und dass die Informationen nicht doppelt vorliegen (auch nicht implizit).

 

[benneq]

Fang am Besten noch mal von vorne an und pack in die Datenbank NUR das, was man nicht wiederherstellen kann.
Z.B. verknüpfst du die einzelnen Tage mit einer Woche, dabei ist doch eindeutig definiert, welche Tage in welcher Woche liegen und andersherum. Diese Daten können und sollten weg!
Damit die Abfragen trotzdem so einfach bleiben nimmt man 'VIEW's. Das sind virtuelle Tabellen, die aus einer fest definierten Anfrage hervorgehen. Innerhalb der DB passieren dann wirre Dinge, aber von außen ist alles sauber und ordentlich

Und benutz für Daten auch die Datumsfunktionen der DB. Die sind sehr performant und die geben dir Antworten auf viele Dinge, die du im Programm erst berechnen müsstest. zB. Abstand zwischen 2 Tagen. Zeitstempel + Intervall. Liegt Tag im Intervall ? etc. pp.


Immer im Hinterkopf behalten:
- KEINE Redundanz! (egal in welcher Form -> vermeiden)
- Mach dir keine Gedanken, ob deine DB Abfrage kompliziert ist oder nicht. Das System hinter der eigentlichen DB, nimmt deine Anfrage eh erst auseinander und optimiert sie enorm.
- Selektiere nur die Zeilen und Spalten, die du wirklich brauchst. Denn Daten, die du später im Programm erst aussortieren musst benötigen doppelt Zeit: 1. DB-Abfrage, 2. Aussortieren im Programm
- Schlüsselattribute auch als echten Schlüssel behandeln und nur genau 1x in der DB definieren (aber beliebig oft referenzieren)



EDITH will es anhand deines Programms ein wenig verdeutlichen:
"- preafix_user enthält benutzer daten und wann er das letzte mal etwas eingetragen hat, der er alles danach nicht sehen soll." Das Datum muss weg. Es ist eindeutig definiert durch den Eintrag in der Contest-Tabelle mit der Benutzer-ID und dem größten Datum. Das ganze Seperat zu speichern macht nur Sinn, wenn man an/über die 1.000.000 Einträge pro Tabelle kommt UND zusätzlich einen Constraint anlegt, der dafür sorgt, dass die Werte immer stimmen.
praefix_week würde ich zu präfix_contest machen oder so ähnlich. Die Tage referenzieren auf einen Contest und anhand der Einträge ist klar von wann bis wann der Contest ging. Im 'praefix_contest' speichert man dann das 'closed'-Flag und den Winner. Wann und welche Woche genau kannst du entweder per SQL Abfrage herausfinden oder im Programm, das mit den Daten arbeitet. Genau so läufts auch anders herum. Das Programm sagt welche Woche und die Abfrage selektiert alle Tage in diesem Zeitraum.
Ob man das 'closed' streichen kann/darf/soll, darüber lässt sich streiten.
In prafix_week gibt's noch so ein 'count' keine Ahnung was es zählen soll. Wenn's um das zählen von Zeilen innerhalb der DB geht: Überlass den Job der DB


EDIT2:
Noch eine wichtige Frage: Du sagst ja, dass soweit alles funktioniert. Wie hast du denn die Datenstrukturen innerhalb des Programms umgesetzt? Meist kann man sein Model (falls du MVP benutzt oder MVC oder was ähnliches) 1:1 in die Datenbank stopfen und dann (falls du Java benutzt) so ein praktisches Werkzeug wie Hibernate benutzen. Das ist die bequeme Art und Weise. Aber auch hierfür muss man sein 'Model' sauber halten! Das ist besonders wichtig, da sonst die gesamte DB zumüllt und laaaaaangsam wird.

 

[urki]

Wenn man zuviel in eine Richtung denkt, kommt man manchmal auch nicht mehr davon weg . Das hilft mir schon sehr, danke.



Das Programm ist mit C++ und Qt geschreiben. Ich hab von QSqlQueryModel abgeleitet und das Modell an meine Bedürfnisse angepasst.

Also Count gibt eine Anzahl an, z.b. die Anzahl der Liegestütze ^^, wie im ersten Bild zu sehen. Das sind Daten aus den Testtabellen.

Bei der Wochenübersicht frage ich also die tabelle "week" ab und zähle für jeden User per Count die Anzahl pro Woche ab. Da ich auch die Wochen, in denen niemand etwas gemacht hat, anzeigen will, gefällt mir die Idee von captmcneil ,KW + Jahr zu speichern, ganz gut. Ansonsten müsste ich im Programm selber schauen, ob die Wochen fortlaufend sind und die Daten reinmogeln. Ich denke, dann müsst ich einen anderen Ansatz in Qt wählen.

Vereinfacht ist die Abfrage in der folgenden Art, nur das in der eigentlichen noch mehr Bedingungen dazu kommen.

SELECT @wid:=preafix_week.id, preafix_week.start, preafix_week.end, preafix_week.closed, 
(SELECT SUM(preafix_contest.count) AS count
FROM preafix_contest
WHERE preafix_contest.wid = @wid AND preafix_contest.user = 1) AS User1,
.
.
.
FROM wo_week
GROUP BY wo_week.start

Ich hab mir mal die Doku zu VIEW's angeguckt. Die Idee es damit zu machen gefällt mir gut. Das Problem dürfte sein, dass ich laut Doku und google, keine Variablen in der SELECT Abfrage des View benutzen darf. Ansonsten hatte ich mir überlegt, dass man für jeden User der dazu kommt, einmal die View neu erstellen muss. Dann eine ähnliche Abfrage, wie die von oben nimmt und eine allgemeine VIEW (die wegen count wohl automatisch ALGORITHM = TEMPTABLE ist) mit allen Informationen erstellt. Die Bedingungen müsste ich ja bei der SELECT Anweisung von der VIEW mit reinbekommen:

SELECT * FROM view WHERE bedingungen

Ohne VIEW sollte die Abfrage ja ähnlich wie bisher gehen. Ich habe bislang auch schon einges per Datumsfunktion direkt berechnet, aber ich denke, wenn ich die Tabellen abändere kann und muss ich sie eh noch mehr verwenden .

Eure Anmerkungen zu den Tabellen ansich hörten sich auch gut an, aber ich bin bislang noch nicht dazu gekommen mir darüber Gedanken zu machen. Das werde ich morgen nochmal angehen.

@captmcneil: Es kann auch passieren, dass niemand in der Woche etwas eingetragen hat, somit gäbe es auch keinen Gewinner oder 2 oder mehrere haben gleich viele Punkte. Man könnte es aber tatsächlich dort integrieren indem man z.B. -1 als Zustand dazu nimmt.

 

[benneq]

Immer wieder schön anderen weiter zu helfen

Jetzt noch mal zum VIEW: Man erstell die nicht dynamisch PUNKT(!) D.h. Man erstellt sie wenn man die Datenbank designed und nicht später. Natürlich kann man, aber das ist nicht sinnvoll. Ein VIEW ist halt ein virtueller TABLE. Und Tables erstellst du auch nicht dynamisch
Oder anders gesagt: Es gehört nicht zum guten SQL Stil. Ich denke mal, dass zu viele Views die DB Performance beeinträchtigen...
Sonst hast du das soweit richtig verstanden: Ein View ist nicht dynamisch. Mit einem View kann man z.B. 3 Tabellen verknüpfen, die man ziemlich oft zusammen braucht. Nehmen wir mal an Google hat in einer Tabelle einfach nur ID und URL stehen und in einer anderen KLICKS und ID (wobei diese ID auf die andere ID zeigt). Und statt jedes mal zu sagen JOIN und dann selektieren, erstellt man sich einen VIEW, der dieses JOIN beinhaltet und führt auf dem VIEW nur noch das SELECT aus.

Damit kommen wir zum nächsten Punkt: Wenn man Tabellen verknüpft kann(!!) man z.B. WHERE table1.user_id = table2.user_id und davor natürlich mit dem SELECT alles selektieren was man braucht. Man sollte(!!) aber die Operation JOIN nutzen. Die ist 1. effizienter und 2. kapselt man seine Abfrage: d.h. für die Übersicht stehen Tabellen-Verknüpfungen bei JOIN und alles was man wirklich selektiert, wo Variablen und so auftauchen steht beim WHERE Das mit der Effizienz ist aber eher irrelevant, da (wie oben schon gesagt) das System hinter der DB deine Anfrage eh in 1000 Teile zerpflückt und so zusammen baut, wie sie am schnellsten auszuführen ist
Aber JOIN gehört natürlich auch zum guten SQL Ton


Und wenn du mit dem offiziell besten Stil arbeiten willst, dann schau dir auf Wikipedia die Beschreibungen der Normalformen einer DB an: http://de.wikipedia.org/wiki/Normalisierung_(Datenbank)
Die BCNF ist immer sinnvoll einzuhalten. Die vierte und fünfte Normalform, naja... die erfüllen eigentlich keinen wirklichen Zweck: Bei einem großen Projekt hätte man danach so viele Tabellen, dass eine simple Abfrage ewig dauern kann, weil die Abfrage nicht mehr über 2 oder 3 (in BCNF), sondern dann über 8-10 Tabellen geht. Abgesehen davon ist es auch in Sachen Übersicht für'n A****...