Datenbankdesign - Zeitangabe als Eigenschaft mitnutzen?

Druckbare Version

14-02-2006, 18:02
BLUESCREEN3D

Datenbankdesign - Zeitangabe als Eigenschaft mitnutzen?

Angenommen ich habe in einer Datenbank eine Tabelle mit irgendwelchen Einträgen.
Jeder dieser Einträge ist entweder authorisiert oder noch nicht authorisiert.
In einer Spalte der Tabelle wird der Zeitpunkt der Authorisierung gespeichert.

Ist es besseres Datenbankdesign, eine zusätzliche Spalte "authorisiert" zu erstellen, die true/false enthält,
oder sollte man die schon vorhandene Spalte, die den Authorisierungszeitpunkt enthält, mitbenutzen und sobald diese den Wert 0 hat, davon ausgehen, dass der Eintrag noch nicht authorisiert ist?
14-02-2006, 19:34
quinte17

eine zusätzliche spalte braucht mehr platz..
ob du diese information brauchst, wenn das datumsfeld NULL ist? glaub ich nicht.
index auf das datumsfeld und gut genug sollte es sein.
wenn du allerdings die authorisierung mal deaktivieren willst, warum auch immer... dann ist so ein flag besser, weil dann hast du das erstaktivierungsdatum immernoch und musst nur des flag umstellen..

dies ist leider alles geschmacksache
greetz
16-02-2006, 15:14
elrond

ich schreibe an einer solchen stelle immer einen definierten nicht null-wert (1.1.1980) in eine solche spalte.

ist aber wie gesagt geschmackssache...
18-02-2006, 07:33
Turbohummel

Das eine ist platzsparend und für die meisten Anwendungen völlig ausreichend,
das andere entspricht mehr der Normalform (Atomarität von Daten - keine doppelten Informationen) und ist eben flexibler.
18-02-2006, 11:46
mwanaheri

Flexibler bist du, wenn du für die Autorisation ein int2-Feld mit Fremdschlüssel nimmst. Dann kannst du auf einen Wertebereich referieren, z.B. undefiniert, autorisiert, zurückgezogen. Bool kann schnell mal eng werden, Datum ja/nein ebenso.
20-02-2006, 14:42
Gaert

Hallo,

in dem Umfeld in dem ich arbeite wird in der Regel mit Gültigkeitszeiträumen gearbeitet, d.h. man legt ein Gültigkeitsdatum fest... z.B. 01.01.2006 - 31.12.9999 - Erlischt eine Gültigkeit wird das "bis" datum entsprechend terminiert. Du benötigst zwar ein weiteres Feld, aber du erhälst damit eine historisch korrekte Darstellung, d.h. du kannst zu bestimmten Zeitpunkten zurückspringen und nachsehen, ob der Eintrag damals gültig war (vorrausgesetzt du legst einen neuen Datensatz an, wenn du einen terminierten Eintrag reaktivierst).

Gruß,

Gaert
21-02-2006, 21:14
Jasper

Zitat:

Zitat von elrond

ich schreibe an einer solchen stelle immer einen definierten nicht null-wert (1.1.1980) in eine solche spalte.

ist aber wie gesagt geschmackssache...

nicht nur geschmackssache. solche pseudo-nullwerte sollte man tunlichst vermeiden weil sie dem optimizer (sofern das verwendete DBMS überhaupt einen hat) falsche informationen liefern.
ein entwickler von uns hatte die gleiche glorreiche idee und hat nicht genutzte datumswerte auf 3.3.3333 gesetzt, weil er das datum schick fand. problem ist nur, dass der optimizer für seine vorhersage low- und highvalues verwendet um den wertebereich zu bestimmen (aus diesem grund ist die oftmals von javaentwicklern praktizierte verwendung von millisekunden anstelle von datumsangaben mit sekundengenauigkeit ebenfalls eine ganz schlechte idee).
im normalfall war der wertebereich von 1980-2005, also 25 jahre. mit diesem pseudowert vergrösserte sich der bereich plötzlich um mehr als 1000 jahre, was den optimizer natürlich zu einer anderen strategie veranlasst hat (es ist halt ein gewaltiger unterschied, ob ich daten eines jahres aus 25 oder aus 1000 jahren auswähle), sehr zum nachteil der performance.
deshalb IMMER null verwenden, wenn die spalte keinen wert hat, dafür ist null da. damit kann der optimizer (richtig) rechnen.

-j
21-02-2006, 21:20
Jasper

Zitat:

Zitat von Gaert

in dem Umfeld in dem ich arbeite wird in der Regel mit Gültigkeitszeiträumen gearbeitet, d.h. man legt ein Gültigkeitsdatum fest... z.B. 01.01.2006 - 31.12.9999 - Erlischt eine Gültigkeit wird das "bis" datum entsprechend terminiert. Du benötigst zwar ein weiteres Feld, aber du erhälst damit eine historisch korrekte Darstellung, d.h. du kannst zu bestimmten Zeitpunkten zurückspringen und nachsehen, ob der Eintrag damals gültig war (vorrausgesetzt du legst einen neuen Datensatz an, wenn du einen terminierten Eintrag reaktivierst).

siehe meine antwort an zu elronds post. warum für das endedatum nicht null verwenden? das ist logischer (es gibt halt (noch) kein endedatum, ergo immer noch gültig) und dem optimizer werden keine falschen werte untergeschoben, die ihn zu falschen ergebnissen kommen lassen. wenn die betreffenden spalten natürlich _niemals_ als prädikat verwendet werden, sind sie dem optimizer egal, aber ich würde erst gar nicht mit so einer m.E. unsitte anfangen.

-j
23-02-2006, 12:36
Gaert

Zitat:

Zitat von Jasper

siehe meine antwort an zu elronds post. warum für das endedatum nicht null verwenden? das ist logischer (es gibt halt (noch) kein endedatum, ergo immer noch gültig) und dem optimizer werden keine falschen werte untergeschoben, die ihn zu falschen ergebnissen kommen lassen. wenn die betreffenden spalten natürlich _niemals_ als prädikat verwendet werden, sind sie dem optimizer egal, aber ich würde erst gar nicht mit so einer m.E. unsitte anfangen.

-j

Hallo Jasper,

ich kann dieses "Optimizier Problem" welches du beschreibst ehrlich gesagt nicht ganz nachvollziehen. Eventuell hast du hierzu ein Beispiel parat, oder kannst das ganze noch ein wenig ausführen?
Die von mir beschriebene Lösung wird im SAP Umfeld haufenweise eingesetzt (z.B. im Organisations Management um Personen auf Planstellen zu setzen). Hierbei hat sich eben eingebürgert 99991231 als Datum anzugeben wenn ein Endedatum nicht definiert ist...

Eine 0 als Enddatum finde ich übrigens nicht logisch, da damit Abfragen wie
"SELECT person FROM pstellen WHERE pstelle = 'Chef' AND gueltig_von <= '20060221' AND gueltig_bis >= '20060221'" (Wer war gestern der Chef?) nicht mehr so einfach und generisch möglich sind.

Gruß,

Gaert
23-02-2006, 15:06
mwanaheri

Zitat:

Zitat von Gaert

Eine 0 als Enddatum finde ich übrigens nicht logisch, da damit Abfragen wie

Äh, Jasper sprach von null, nicht von 0, wenn ich das richtig sehe.
Und im SAP-Umfeld sind die meisten Felder, die Zahlen enthalten, vom Typ N, ich denke wohl auch Datumsangaben. Das ist dann schon was anderes als ein Datumsfeld in der Datenbank.
23-02-2006, 15:07
elrond

das problem bleibt dasselbe: du musst dann immer explizit auf null prüfen
23-02-2006, 15:56
Jasper

Zitat:

Zitat von Gaert

ich kann dieses "Optimizier Problem" welches du beschreibst ehrlich gesagt nicht ganz nachvollziehen. Eventuell hast du hierzu ein Beispiel parat, oder kannst das ganze noch ein wenig ausführen?

wenn du so nett fragst, hier eine demo, mit der ich unsere entwickler geschockt habe:

erstmal eine kleine tabelle mit 24000 datumswerten verteilt über das jahr 2006:
SQL> create table jasper as select rownum num, to_date(mod(object_id,365)+1,'DDD') datum from all_objects where rownum <=24000;

dann werden 10% auf null gesetzt, das ist meine variante:
SQL> update jasper set datum=null where mod(num,10)=1;

deine hat statt null dein dummydatum:
SQL> create table gaert as select num,nvl(datum,'31-DEC-9999') datum from jasper;

analysieren ist pflicht wenn es um CBO geht:
SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'JASPER');
SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'GAERT');

statistiken kontrollieren:
SQL> select num_distinct, low_value, high_value, density, num_nulls from user_tab_columns where column_name='DATUM';

NUM_DISTINCT LOW_VALUE HIGH_VALUE DENSITY NUM_NULLS
------------ -------------------- -------------------- ---------- ----------
365 786A0101010101 786A0C1F010101 .002739726 2400
366 786A0101010101 C7C70C1F010101 .00273224 0

perfekt, stimmt alles. wieviele zeilen umfasst die ergebnismenge? überschlagsmässig wären das:

= (num_rows - num_nulls) / total_range * selected_range
= (24000 - 2400) / 12 Moinate * 3 Monate
= 5400 zeilen

die realität mit meiner variante:

SQL> select count(*) from jasper where datum >= '1-jun-2006' and datum <= '1-sep-2006';
COUNT(*)
----------
5525

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=16 Card=1 Bytes=8)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'JASPER' (Cost=16 Card=5578 Bytes=
44624)

geschätzte Kardinalität ist 5578, exakt sind es 5525. das ist ok, weil der CBO von normalverteilung ausgeht, die nicht gegeben ist.

nun deine variante (halt dich fest):

SQL> select count(*) from gaert where datum >= '1-jun-2006' and datum <= '1-sep-2006';
COUNT(*)
----------
5525

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=16 Card=1 Bytes=8)
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'GAERT' (Cost=16 Card=68 Byte
s=544)

ouch. 68 geschätzte vs. 5525 tatsächliche zeilen.
wird lustig wenn jetzt noch ein join hinzukommt. bei 68 wird dann vermutlich NL verwendet werden, allerdings loopt das dann nicht 68 sondern 5525 mal. und dabei haben wir schlappe 24000 zeilen in der tabelle.

Zitat:

Die von mir beschriebene Lösung wird im SAP Umfeld haufenweise eingesetzt (z.B. im Organisations Management um Personen auf Planstellen zu setzen). Hierbei hat sich eben eingebürgert 99991231 als Datum anzugeben wenn ein Endedatum nicht definiert ist...

AFAIK arbeitet SAP mit heftigsten HINTS und RBO, nix CBO. RBO ist aber mit 10g deprecated.

Zitat:

Eine 0 als Enddatum finde ich übrigens nicht logisch, da damit Abfragen wie
"SELECT person FROM pstellen WHERE pstelle = 'Chef' AND gueltig_von <= '20060221' AND gueltig_bis >= '20060221'" (Wer war gestern der Chef?) nicht mehr so einfach und generisch möglich sind.

generisch und performance schliessen sich generell aus. die frage ist, wieviel performance opfere ich meinem generischen ansatz? IMHO hat sich der code nach den daten zu richten und nicht umgekehrt.

aber zu deinem beispiel: sollte das nicht 'gueltig_von >= X and gueltig_bis <= Y' lauten?
was spricht gegen:
SELECT person FROM pstellen WHERE pstelle = 'Chef' AND gueltig_von >= '20060221' AND (gueltig_bis <= '20060221' or gueltig_bis is null)

-j
23-02-2006, 15:59
Gaert

Zitat:

Zitat von mwanaheri

Äh, Jasper sprach von null, nicht von 0, wenn ich das richtig sehe.
Und im SAP-Umfeld sind die meisten Felder, die Zahlen enthalten, vom Typ N, ich denke wohl auch Datumsangaben. Das ist dann schon was anderes als ein Datumsfeld in der Datenbank.

Wie Elrond schon sagte 0, oder null ... völlig wurscht - das von mir genannte query lässt sich nicht mehr anwenden, wenn "gültig bis unendlich" = null oder 0 ist.
Der Vollständigkeit halber - Typ N ist im SAP Umfeld nicht sooo häufig - das ist ein Numerischer Character - "echte" Zahlen werden als I, P oder F abgelegt... und Datumswerte werden als Datentyp DATS gespeichert hinter der die Domäne DATS liegt (bzw der eingebaute Datentyp D) , welcher schließlich als Char(8) auf der Datenbank gespeichert ist.
Siehe auch: http://help.sap.com/saphelp_46c/help...fe/content.htm

Gruß,

Gaert
23-02-2006, 16:43
Gaert

Hallo Jasper,

vielen Dank für die ausführliche Erklärung... habe verstanden was du meinst.
Allerdings muss man immer unterscheiden zwischen Performanz, Usability und Logik.
Ich muss dir recht geben, dass dein Ansatz sicherlich Performanter ist als meiner, jedoch ist hierbei auch logik und usability nicht zu vernachlässigen.

Du scheinst offensichtlich ein erfahrener Oracle Admin zu sein - sonst hättest du das nicht so auf die schnelle aus dem Hut gezaubert, doch versetze dich auch mal in deinen Feind (Entwickler wie mich, die mit ihren Selects über dein Heiligtum schrubbern).
Gerade bei offenen Systemen wie SAP, in denen es primär darauf ankommt Daten aus Tabellen zu lesen, die man nicht selbst designt hat (und teilweise seit Uhrzeiten existieren) sollte man Wert darauf legen, dass die Werte in den Tabellen logisch und selbstsprechend sind.
Mit gesundem Menschenverstand würde (soweit man von sowas bei einem ABAP Entwickler sprechen kann ;) ) ich als erstes auf einen Fehler tippen (dass der doofe SAP Entwickler etwas verbockt hat), wenn ein Datumsfeld welches einen "gültig_bis" Wert anzeigt nicht gefüllt ist. Desweiteren rutschen ja nicht nur SAP Entwickler auf der Datenbank rum, sondern auch die gemeinen Anwender machen gerne mal eine freche Abfrage über SE16 oder bauen sich ein QuickView Report zusammen - für diese Gruppe würde es sich zweimal nicht erschließen, warum man bis_datum = null selektieren sollte - abgesehen davon, dass von einigen (generischen!) Abfrage "Tools" unterstützt wird, z.b. über null Werte zu Joinen.

Letztendlich muss ich sagen, dass mich dein - zugegeben performaterer Ansatz - aus oben genannten Gründen nicht überzeugt hat.

Zitat:

Zitat von Jasper

was spricht gegen:
SELECT person FROM pstellen WHERE pstelle = 'Chef' AND gueltig_von >= '20060221' AND (gueltig_bis <= '20060221' or gueltig_bis is null)
-j

Dein Beispiel für die Abfrage ist sicherlich interessant - kann sich aber in einem gewachsenen System wie SAP sicherlich nicht durchsetzen - abgesehen davon fände ich es als jemand der es lesen und eventuell anfassen müsste nicht sonderlich prägnant und logisch - der Anruf beim Kollegen oder die Recherche in der Datenbanktabelle ist vorprogrammiert.

Abgesehen davon Vertrete ich die Meinung, dass ab einer gewissen Projektgröße das db design das coden erleichtern sollte sprich... das design richtet sich nach dem coding ;-)...

Zitat:

Zitat von Jasper

aber zu deinem beispiel: sollte das nicht 'gueltig_von >= X and gueltig_bis <= Y' lauten?

Nö, warum?

Gruß,

Gaert
23-02-2006, 18:49
Jasper

Zitat:

Zitat von Gaert

vielen Dank für die ausführliche Erklärung... habe verstanden was du meinst.
Allerdings muss man immer unterscheiden zwischen Performanz, Usability und Logik.
Ich muss dir recht geben, dass dein Ansatz sicherlich Performanter ist als meiner, jedoch ist hierbei auch logik und usability nicht zu vernachlässigen.

logik? gerade weil ein NULL-wert (no value) für sich selbst spricht, erschliesst sich mir nicht, wieso 31.12.9999 logischer oder auch nur logisch sein soll:
1. wenn in der spalte 31.12.9999 steht, gibt es kein endedatum.
2. wenn in der spalte kein datum steht, gibt es kein endedatum.
sorry, also beim besten willen sehe ich nicht, wie 1. logisch sein soll. die nächste software verwendet 3.3.3333 und wieder eine andere 1.1.1970. sieht so logik aus?

Zitat:

Desweiteren rutschen ja nicht nur SAP Entwickler auf der Datenbank rum, sondern auch die gemeinen Anwender machen gerne mal eine freche Abfrage über SE16 oder bauen sich ein QuickView Report zusammen - für diese Gruppe würde es sich zweimal nicht erschließen, warum man bis_datum = null selektieren sollte - abgesehen davon, dass von einigen (generischen!) Abfrage "Tools" unterstützt wird, z.b. über null Werte zu Joinen.

ich würde SAP nicht gerade als Massstab für gutes DB-Design hernehmen, gerade, weil es historisch gewachsen ist. Je nachdem, welcher entwickler gerade dienst hatte sieht das von exzellent bis grottenschlecht aus.
ich habe auch mit anwendern zu tun, die adhoc-queries absetzen und es hat keiner probleme mit 'is null'. das liegt aber auch daran, dass es zu dem datenmodell eine spezifikation gibt (ebenso wie kommentare zu den spalten), die erklären, was wie und warum belegt ist.

Zitat:

Dein Beispiel für die Abfrage ist sicherlich interessant - kann sich aber in einem gewachsenen System wie SAP sicherlich nicht durchsetzen - abgesehen davon fände ich es als jemand der es lesen und eventuell anfassen müsste nicht sonderlich prägnant und logisch - der Anruf beim Kollegen oder die Recherche in der Datenbanktabelle ist vorprogrammiert.

siehe oben. dafür gibt es dokumentation. das datenmodell aufzuweichen nur damit entwickler nicht in der dokumentation nachschlagen müssen halte ich für grundverkehrt.

Zitat:

Abgesehen davon Vertrete ich die Meinung, dass ab einer gewissen Projektgröße das db design das coden erleichtern sollte sprich... das design richtet sich nach dem coding ;-)...

das haben sich andere auch gesagt. das ergebnis sind solche fehlschläge wie INPOLneu.

-j