Frage zum DB-Design

[verkruemelt]

Hallo zusammen,

ich soll eine Webanwendung schreiben, bei der Mitarbeiter bestimmte Werte eintragen sollen, diese Werte werden dann für Auswertungen benötigt.
Dabei gibt es folgendes zu beachten:

• ein Mitarbeiter kann in einem oder mehreren Teams sein
• pro Team, Tag und Mitarbeiter gibt es einen Eintrag mit entsprechenden Werten
• ein Mitarbeiter kann in einem anderen Team eingesetzt werden (zu einem neuen Monat)
• die Werte unterscheiden sich von Team zu Team (ein paar Werte sind gleich)
• es können Werte für ein Team entfallen
• es können neue Werte für ein Team benötigt werden
• es können Teams entfallen
• es können neue Teams entstehen

Meine Überlegung sieht bisher so aus, für jedes Team eine eigene Tabelle zu erstellen (siehe Anhang - verkürztes Modell). Ist dies sinnvoll(er) - als die Daten in eine große Tabelle für alle Teams zu schrieben?

Die Ideallösung sähe so aus, die Teams und die Werte über die Webseite anpassen zu können, ohne dafür manuell in der DB etwas ändern zu müssen.

Oder gibt es für so etwas vielleicht schon etwas fertiges?

 

[akretschmer]

Meine Überlegung sieht bisher so aus, für jedes Team eine eigene Tabelle zu erstellen (siehe Anhang - verkürztes Modell). Ist dies sinnvoll(er) - als die Daten in eine große Tabelle für alle Teams zu schrieben?

Nein.

 

[verkruemelt]

Warum nein?
Dann entstehen aber mit der Zeit tote Attribute, die die DB zumüllen.

 

[akretschmer]

weil "es können neue Teams entstehen". Dann änderst Du das DB-Design und auch die Applikation. Das skaliert also nicht. Das ist also keine Kunst sondern Müll und kann daher weg.

 

[ukulele]

akretschmer hat schon recht, eine Tabelle bildet eine Entität ab und das wäre nunmal in diesem Fall Team.

Um die Datenhaltung für deine "Werte" sinnvoll zu konzipieren müsste man schon verstehen um was für Werte es sich da handelt. Es gibt durchaus Ansätze sehr unterschiedliche Atribute (auch unterschiedlich viele Atribute) zu Entitäten zu speichern. Ein Klassiker wäre EAV Entity–attribute–value model - Wikipedia . Es gibt aber durchaus modernere Möglichkeiten, stark abhängig von der eingesetzten DB.

 

[verkruemelt]

Danke für eure Antworten.

Die Werte um die es geht, sind jeweils eine Anzahl an Vorgängen, die ein Sachbearbeiter an dem Tag für ein Team abgeschlossen hat.

Dieses Projekt habe ich schon mal vor einigen Jahren umgesetzt, noch während meiner Ausbildung. Mit MySQL (bzw. aktuell MariaDB) und PHP 5.4 als Basis.
Damals habe ich es mit einer Tabelle umgesetzt und das Projekt hat über die Zeit sehr viele Anpassungen erfahren.

Dadurch gibt es im Quelltext viele Unzulänglichkeiten und Inkonsistenzen...
Daher will ich dies in "Version 2" alles berücksichtigen, um mir dann auch die Arbeit für zukünftige Anpassungen zu erleichtern.

Ich finde es nur nicht sehr elegant, wenn die Spalten dann größten Teils leer bleiben oder durch "0" aufgefüllt werden müssen. Aber dann muss ich wohl damit leben.

 

[akretschmer]

Ich finde es nur nicht sehr elegant, wenn die Spalten dann größten Teils leer bleiben oder durch "0" aufgefüllt werden müssen.

Wie kommst Du darauf?

 

[ukulele]

NULL-Werte (nicht 0-Werte) sind ganz legitim in Datenbanken.

 

[akretschmer]

Daher will ich dies in "Version 2" alles berücksichtigen, um mir dann auch die Arbeit für zukünftige Anpassungen zu erleichtern.

*vermutlich* hast Du je Team verschiedene Attribute, die Du (jetzt) noch gar nicht alle kennst und die womöglich auch noch dynamisch sich ändern können. Deine Felder mit dem namen "FeldXY" mit XY 01, 02, 03 ... belegen dies. Diese Struktur ist eh Müll, weil, das hast Du vermutlich gemerkt, Du die Tabellen immer mal wieder anpassen mußt - spricht, neue Felder anfügen, dann auch die Programmierung ändern etc.

Falls dem so ist: erst vor ein paar Tagen war hier im Forum ein Thread zu EAV, was Dein Problem besser abbilden kann. Und dort kam auch mein Hinweis, daß es mit anderen Datenbanken, PostgreSQL, dafür Datentypen (hier: JSONB) gibt, mit denen man das sehr elegant abbilden kann.

 

[verkruemelt]

NULL-Werte (nicht 0-Werte) sind ganz legitim in Datenbanken.

Ja, das ist mir klar, es kommt mir nur ziemlich ineffizient vor.


@akretschmer Ja, dem ist so. Das merke ich jedes mal - aber aktuell ist es nur eine Tabelle in der die Daten alle stehen. An PostgreSQL hatte ich auch schon mal gedacht, vielleicht sollte ich mich doch mal eingehender damit befassen.
Kann man da trotzdem die Werte vom gleichen Team einfach kumulieren oder ist das deutlich komplifizierter als wenn ich Spalten mit Integer-Werten habe?

Wieder was zum lernen.

 

[akretschmer]

Es geht. Mal ein kleines Beispiel, Du hast diese Tabelle:

test=*# \d verkruemelt
 Tabelle »public.verkruemelt«
 Spalte |  Typ  | Attribute
--------+---------+-----------
 team  | integer |
 data  | jsonb  |
Indexe:
  "idx_verkruemelt_2" gin (data)

test=*# select * from verkruemelt ;
 team |  data   
------+-----------------------------------------
  1 | {"farbe": "rot", "anzahl": 10}
  1 | {"farbe": "gelb", "anzahl": 20}
  2 | {"muster": "gestreift", "gewicht": 200}
  2 | {"muster": "karo", "gewicht": 250}
(4 Zeilen)

Auf den data - Feld ist ein GIN-Index, dazu kommen wir dann noch. Erst einmal eine Aggregation, je Team und die Summe von anzahl bzw. gewicht:

test=*# select team, sum((data->>'anzahl')::int) as anzahl, sum((data->>'gewicht')::int) as gewicht from verkruemelt group by team;
 team | anzahl | gewicht
------+--------+---------
  1 |  30 |   
  2 |  |  450
(2 Zeilen)

Angenommen, Du willst das einschränken auf Datensätze mit der Eigenschaft Frabe: rot:

test=*# select team, sum((data->>'anzahl')::int) as anzahl, sum((data->>'gewicht')::int) as gewicht from verkruemelt where data @> '{"farbe": "rot"}' group by team;
 team | anzahl | gewicht
------+--------+---------
  1 |  10 |   
(1 Zeile)

Und hier kommt auch der Index zum Einsatz, siehe Explain:

test=*# explain select team, sum((data->>'anzahl')::int) as anzahl, sum((data->>'gewicht')::int) as gewicht from verkruemelt where data @> '{"farbe": "rot"}' group by team;
  QUERY PLAN   
---------------------------------------------------------------------------------------
 HashAggregate  (cost=16.04..16.05 rows=1 width=36)
  Group Key: team
  ->  Bitmap Heap Scan on verkruemelt  (cost=12.00..16.01 rows=1 width=36)
  Recheck Cond: (data @> '{"farbe": "rot"}'::jsonb)
  ->  Bitmap Index Scan on idx_verkruemelt_2  (cost=0.00..12.00 rows=1 width=0)
  Index Cond: (data @> '{"farbe": "rot"}'::jsonb)
(6 Zeilen)

Wie Du siehst: da geht einiges.

 

[verkruemelt]

Das schaut alles ganz nett aus, ich habe auch selbst ein bisschen herumexperimentiert. Dabei ist mir aufgefallen, wenn ich über mehrere "Spalten" aus dem JSONB-Feld die Summe bilden will, klappt das nur, wenn in jeder Spalte auch ein Wert steht.

Zum Beispiel folgende Daten:

 teamid |   datum    |                     data
--------+------------+----------------------------------------------
      1 | 2016-12-21 | {"hkp": 5, "manuell": 10, "maschinell": 10}
      2 | 2016-12-20 | {"mahnung": 80}
      2 | 2016-12-20 | {"manuell": 20, "maschinell": 30}
      2 | 2016-12-21 | {"mahnung": 5, "manuell": 20}
      1 | 2016-12-21 | {"hkp": 35, "manuell": 25, "maschinell": 37}
(5 Zeilen)

Und als Abfrage:

select teamid, datum,
avg((data->>'manuell')::float) as manuellAVG,
sum((data->>'manuell')::int) as manuell,
sum((data->>'maschinell')::int) as maschinell,
sum((data->>'mahnung')::int) as mahnung,
sum((data->>'hkp')::int) as hkp,
sum((data->>'manuell')::int + (data->>'maschinell')::int) as gesamt
from daten group by datum, teamid;

Ausgabe:

 teamid |   datum    | manuellavg | manuell | maschinell | mahnung | hkp | gesamt
--------+------------+------------+---------+------------+---------+-----+--------
      1 | 2016-12-21 |       17.5 |      35 |         47 |         |  40 |     82
      2 | 2016-12-20 |         20 |      20 |         30 |      80 |     |     50
      2 | 2016-12-21 |         20 |      20 |            |       5 |     |
(3 Zeilen)

In der letzten Zeile der Ausgabe hätte ich für gesamt "20" erwartet und nicht - nichts!?
Gibt es eine Möglichkeit in PostgreSQL das zu summieren oder müsste ich dafür dann PHP bemühen?
Oder sollte ich die Werte über die Anwendung beim Eintragen mit "0" füllen?

 

[akretschmer]

coalesce() ist Dein Freund.

sum((coalesce(data->>'manuell')::int,0) + coalesce((data->>'maschinell')::int),0) as gesamt

 

[akretschmer]

Hat übrigens nichts mit JSONB zu tun.

test=*# select NULL + 5;
 ?column?
----------
   
(1 Zeile)

test=*# select coalesce(NULL,0) + 5;
 ?column?
----------
  5
(1 Zeile)

NULL ist nicht impliziet 0, sondern die vollständige Abwesenheit einer Information.

 

[verkruemelt]

Das NULL != 0 ist, ist mir klar. Mich hat nur verwirrt, dass 20 + NULL = NULL ist. Aber das mit coalesce leuchtet mir dann ein.

Mit folgender Abfrage klappt es dann auch:

sum(coalesce((data->>'manuell')::int, 0) + coalesce((data->>'maschinell')::int, 0)) as gesamt

die zweite "(" kommt nach coallesce.
Und mit

SELECT DISTINCT jsonb_object_keys(data) AS keys FROM daten

bekomme ich heraus, welche Keys eingetragen wurden.

Dann brauche ich auch nur die Keys zu speichern, für die ein Sachbearbeiter auch wirklich etwas eingetragen hat und nicht immer alle, die für das Team zur Verfügung stehen. Das würde noch mal Speicherplatz sparen.
Damit käme ich auch meinem Ziel näher, das ganze über eine Webseite verwalten zu können.

Vielen Dank für die Hilfe!