Wie speichert ihr boolsche Werte in der DB?

[Codehunter]

Moin!

Ich mach mir immer wieder einen Kopf über die Frage, wie ich boolsche Werte am besten in einer DB-Tabelle speichere mit Blick auf Indizierbarkeit, Performance und lesbaren Delphi-Code. Einen dedizierten Bool-Typ gibt es bei MariaDB (MySQL) ja nicht.

Drei Varianten fallen mir ein:

Delphi-Quellcode:

BoolVar:= (Fields.FieldByName('TheName').AsInteger > 0); { INT(1) }
BoolVar:= (Fields.FieldByName('TheName').AsString <> '0'); { ENUM('0','1') }
BoolVar:= (Fields.FieldByName('TheName').AsInteger > 0); { TINYINT(1) }

Alle drei Varianten werden innerhalb der DB als Integer abgebildet und lassen sich indizieren.

Daneben gäbe es noch den Feldtyp BIT(1), wobei ich keine Ahnung habe wie der intern abgebildet wird in der DB.

Darum mal die Frage in die Runde: Wie macht ihr das so?

Grüße
Cody

[Jumpy]

Oft wird das ja so gemacht, das ein Wert True entspricht und alles andere ist False bzw. umgekehrt, also:

0 oder 1 oder -1 = True, alles andere False

bzw.

0 oder 1 oder -1 = False, alles andere True


Ich kann mir nie merken wie das jetzt genau war.



Nur mal generelle Frage, macht ein Index der ja über einen bool-Wert gehen soll grundsätzlich Sinn (gibt ja nur zwei Werte)?
Und falls das Sinn macht, würde ein solcher Index dann Sinn machen, wenn man das mit einem Integer nachbildet und sagt z.B. True alles <=0, False alles >0 oder so ähnlich? Denn dann können da ja alle möglichen Integer drin stehen, die Interpretation als True,False erfolgt dann ja nur in Anwendung? Was hilft da der Index?

[Codehunter]

Sagen wir mal so: In der Praxis macht sich ein Index über die "Pseudo-Bool-Felder" bemerkbar wenn ich eine WHERE-Klausel auf dieses Feld lege und eine größere Ergebnismenge bekomme. Man erkauft sich das aber mit dem Preis höherer Anforderungen der DB an das Hostsystem.

In meinem Fall speichere ich die Bool-Werte ja alle selber und kann da für mich festlegen, dass '0'=FALSE und '1'=TRUE ist. Zur Sicherheit mache ich aber (X<>'0')=TRUE.

[jobo]

Worauf willst Du denn mit der Frage hinaus?
Performance? Ressourcenverbrauch? ..?
Das Thema Indizierung bspw. ist ein mageres Feld by Boolean Werten.
Verwendet man normale Indizes, ergibt sich kaum ein Nutzen, da die Selektivität mehr oder weniger = 0 ist.
1 Mio Datensätze mit einem gleichverteilten Anteil von Booleanwerten ergeben als Selektionskriterium 500T Sätze True und 500T False. Das ergibt in der Praxis dann trotzdem einen Fullscan und damit keinen Performancegewinn, wohl aber Overhead bei der Indexverwaltung, nämlich Speicherplatz und Kosten bei Insert/Delete.
Wichtiger scheint mir in dem Bereich eine einheitliche Handhabung, ideal systemübergreifend. Dann nimmt man sich aber u.U. wieder die Möglichkeit proprietäre Verfahren einzelner Hersteller nutzbringend einzusetzen.

[Blup]

Im Interbase habe ich für Boolean eine Domain angelegt und den Feldern zugewiesen:

Code:

CREATE DOMAIN DBOOLEAN AS SMALLINT DEFAULT 0 NOT NULL CHECK (value in (0,1))

Damit sind nur die Werte 0 oder 1 möglich.

Ein Index auf Boolean-Felder macht nur Sinn, wenn die Gesamtmenge der auszuwertenden Daten damit wirklich erheblich eingeschränkt wird.
z.B.
Die Tabelle enthält 10000 Datensätze, davon 100 neue Datensätze (mit einem Boolean-Feld gekennzeichnet), die weiter verarbeitet werden müssen.
Hier ist es aber wahrscheinlich sinnvoller auf das Boolean-Feld zu verzichten und dafür eine weitere Tabelle anzulegen, in der nur die ID der 100 Datensätze gespeichert wird.
Für beliebig viele unterschiedliche Boolean-Felder müsste man diese Tabelle um ein Feld TYP ergänzen.

[joachimd]

in einer Online-Vorlesung zu In-Memory Datenbanken am HPI Potsdam hat Hasso Plattner (SAP) zu diesem Thema referiert und präferiert die Lösung, statt eines Status (true/false) lieber einen Zeitstempel zu speichern. Dann hat man zusätzlich noch die Information, wann sich der Status geändert hat. Verbunden mit einem INSERT ONLY (=>http://joachimduerr.blogspot.de/2013...sert-only.html) Konzept erhält man eine komplette Historie des Datensatzes.

[p80286]

Nun ganz so platt ist das Beispiel ja nicht, und wenn man ehrlich ist, dann wird über das vorhandensein eines Datums ein Bool-Wert erzeugt. Wobei das Konzept für Daten mit einem zeitlichen Gültigkeitsbereich durchaus seinen Reiz hat.

Gruß
K-H

[Jumpy]

Zitat:

Zitat von joachimd (Beitrag 1252490)

in einer Online-Vorlesung zu In-Memory Datenbanken am HPI Potsdam hat Hasso Plattner (SAP) zu diesem Thema referiert und präferiert die Lösung, statt eines Status (true/false) lieber einen Zeitstempel zu speichern. Dann hat man zusätzlich noch die Information, wann sich der Status geändert hat. Verbunden mit einem INSERT ONLY (=>http://joachimduerr.blogspot.de/2013...sert-only.html) Konzept erhält man eine komplette Historie des Datensatzes.

Nur das ich das richtig verstehe: Das bezieht sich auf den Post von Blup vorher, über den möglichen Verwendungszweck des boolschen Feldes, oder? Nicht auf boolsche Felder generell?

P.S.: Hab mir auch mal deinen Blogeintrag durchgelesen. Das erinnert mich doch sehr an die Historienverwaltung, in einer Payroll-Software um die herum wir viel arbeiten.

[joachimd]

Zitat:

Zitat von Jumpy (Beitrag 1252494)

Nur das ich das richtig verstehe: Das bezieht sich auf den Post von Blup vorher, über den möglichen Verwendungszweck des boolschen Feldes, oder? Nicht auf boolsche Felder generell?

Eigentlich schon auf boolsche Felder generell. Die Kernaussage ist, dass Boolen-Felder (true/false, 1/0, ...) eigentlich nicht verwendet werden sollen, da man mit einem Zeitstempel statt Boolean noch zusätzliche Information vorhalten kann. Zeitstempel=NULL heisst dann eben FALSE, Zeitstempel<>FALSE ist TRUE mit Zusatz-Info.

[Mavarik]

hmm

Bei SQLite auf iOS & Android habe ich mir eine Classe erzeugt die alle Bool-Werte kapselt und in einem einzigen String speichert. (weniger Speicher)

Nach dem Motto "JJJNNNJJJNJNJNJNJNJNJJJJJJ"

Die Felddefinition erzeugt ein Dictionary mit den Feldnamen und kennt die Position im String.

Sehr praktisch.

Checkbox.Checked := MySQLite.GetBool('Einstellung1');

Mavarik

[Furtbichler]

SQL-Server bietet bit-Felder an, die intern als Byte abgelegt werden, allerdings so, das immer 8 bit-Felder eines Datensatzes in ein Byte passen. Man bekommt also 7 bits geschenkt, wenn man ein bit anlegt. Wieso man einen Mehrwert haben sollte, wenn ich das als Zeitstempel umsetze, erschließt sich mir nicht. Wenn ich eine Historienverwaltung brauche, lege ich sie an.

Was hab ich davon, das ich weiß, das die Option 'HatdieHosenAn' gestern um 17:30 gesetzt wurde, aber die Information 'Durchmesser des Nudelholzes' zwar auf 36cm steht (echt ne fiese Sache, so eine Ehe), aber ich dummerweise nicht weiss, wann diese doch sozusagen systemimmanente Wert von vormals 3cm hochgesetzt wurde.

Dessenungeachtet ist es schon etwas merkwürdig, beim Feldnamen 'IstNachtaktiv' einen Zeitstempel zu sehen: Bedeutet das: 'Ist nachtaktiv ab ....?' oder bis? Nee, also Ich kann mir auch von hinten durch die Brust ins Auge schießen.

Zurück zum Thema: Wenn die DB keine Bits unterstützt, dann würde ich die ansonsten kleinste Einheit nehmen, also char(1), smallint etc. Die Sache mit den Constraints ist nett, frist zwar ein wenig Performance, aber WTF, außerdem ist das dann so sauber.

Nebenbei: Ebenso wie in der Programmierung ist es häufig so, das aus zwei Zuständen rasch mehr werden... Und insofern ist ein smallint gar nicht mal so blöd. Fängt schon beim Geschlecht an. Vor 30 Jahren reichte ein Bit: IstWeiblich Ja/nein (Wir sind ja schön gequotet). Aber heutzutage benötigt man schon etwas mehr Optionen, um das Geschlecht anzugeben (Weiblich, Männlioh, weiß noch nicht, bin gerade am umbiegen, beides, weder noch, da Politiker etc.).

[Bummi]

@Furtbichler sorry für OT, aber bei Deiner Antwort merkt man dass es Frühling wird ...

[DeddyH]

Das Timestamp-Konzept leuchtet mir allerdings auch nicht wirklich ein, vor allem, wenn NULL als false interpretiert werden soll. Auf diese Weise kann man dann zwar ermitteln, wann der Status zu true gewechselt ist, aber nicht andersherum, da ja dann NULL im Feld steht. Ich selbst mache das meist so wie von Blup skizziert, indem ich eine Boolean-Domäne anlege, die benutzt dann den kleinsten ganzzahligen Typen, den die DB anbietet.

[FBrust]

Hallo,

Zitat:

Zeitstempel=NULL heisst dann eben FALSE, Zeitstempel<>FALSE ist TRUE mit Zusatz-Info.

Das bedeutet also, um ein Feld wieder auf die Information "False" zu setzen, setze ich den Inhalt auf Null? Wo ist dann die Zusatzinformation? Die hätte ich doch nur, solange das Feld auf "True" steht, also ein Zeitstempel vorhanden ist....

Mir geht es da wie DeddyH, erschließt sich mir nicht wirklich....

Ich verwende für Boole'sche Informationen Smallint-Felder mit 0 für False und 1 für True, wobei nur auf <> 0 geprüft wird (Firebird 2.5.2).

Firebird 3 soll ja Boole'sche Felder direkt unterstützen, aber bis dahin ist wohl noch lang hin.


Grüße
Frank

[joachimd]

Zitat:

Zitat von FBrust (Beitrag 1252528)

Das bedeutet also, um ein Feld wieder auf die Information "False" zu setzen, setze ich den Inhalt auf Null? Wo ist dann die Zusatzinformation? Die hätte ich doch nur, solange das Feld auf "True" steht, also ein Zeitstempel vorhanden ist....

Mir geht es da wie DeddyH, erschließt sich mir nicht wirklich....

Wenn man den Kontext mit Hasso Plattner/SAP betrachtet, dann macht es etwas mehr Sinn. Eine Rechnung ist bezahlt am ... oder offen. Dann noch ein INSERT ONLY und die Historie ist gegeben (bezahlte Rechnung plötzlich wieder unbezahlt). Ich gebe zu, das macht nicht bei jedem Boolean-Feld Sinn - aber wenn eine Zusatzinformation vorhanden ist, dann lieber so als mit zwei Feldern (bezahlt, bezahlt_am) - und lach nicht, solche Konstrukte sehe ich fast täglich;)

[p80286]

Nemen wir das Nudelholz, das ist so schön schief
id name Durchmesser durchmesser_alt_erledigt

1. Nudelholz 8 null
2. Marmor 10 10
3. Birke 12 null
4. Stahl null null

Wenn Du ein Nudelholz hast, gibt es auch einen Durchmesser (<>null), ohne Durchmesser soll es erst noch gekauft werden. Ist Durchmesser_alt_erledigt belegt, dann gibt es das Nudelholz nicht mehr (frag Furtbichler wo's ist)

Wenn man dafür dann ein Datumsfeld/Timestamp nimmt, dann weiß man von wann bis wann man das Nudelholz gehabt hat.
Oder anders herum: IchbinNudelholzeigentümer:=(durchmesser <> null) and (durchmesser_alt_erledigt = null)
ich_hatte_mal_EIN_nudelholz:=(durchmesser<>null) and (durchmesser_alt_erledigt <> null)


Gruß
K-H

[Olli73]

Zitat:

Zitat von joachimd (Beitrag 1252537)

Eine Rechnung ist bezahlt am ... oder offen. Dann noch ein INSERT ONLY und die Historie ist gegeben (bezahlte Rechnung plötzlich wieder unbezahlt).

Und dann wird festgestellt, dass eine Zahlung falsch zugeordnet wurde und umgebucht werden muss ;)

Da wäre ein Test "sum(alleBuchungsbeträge) = 0" besser, und wenn es mehrere Teilzahlungen waren, braucht man sowieso bei jeder Zahlung einen Timestamp.

[Phoenix]

Zitat:

Zitat von joachimd (Beitrag 1252495)

da man mit einem Zeitstempel statt Boolean noch zusätzliche Information vorhalten kann. Zeitstempel=NULL heisst dann eben FALSE, Zeitstempel<>FALSE ist TRUE mit Zusatz-Info.

An sich ne coole Idee, geht aber leider nicht, wenn man tatsächlich einen "3-State Boolean" braucht:

• true = true
• false = false
• null = undefined / default

Und ja, es kommt in der Praxis durchaus mal vor, das man einen bool explizit noch nicht gesetzt haben darf, da der Business-Case z.B. sowohl für das Ja als auch das Nein eine bewusste Entscheidung des Users voraussetzt. Solange die nicht da ist, ist es eben undefined.

[sx2008]

Also nach meinen Erfahrungen nach kommt es recht häufig vor, dass später weitere Zustände ausser True und False benötigt werden.
Deshalb verwende ich immer meistens ein BYTE (0..255) als Booleanfeld.
Beispiel 1:
Splashscreen_aktive: True oder False.
Später fällt mir dann ein, dass ich den Splashscreen nicht aktivieren möchte, wenn die Anwendung auf einem Terminalserver läuft; es soll aber einstellbar sein. Und schon sind es 3 Zustände...
Beispiel 2:
Kundensperre (z.B. weil der Kunde im Zahlungsrückstand ist).
Könnte man ja meinen dass True oder False ausreichend sind.
Später möchte man aber genauer wissen weshalb der Kunde gesperrt ist (Insolvent, Zahlungsrückstand, Gerichtsverfahren anhängig, ...) und schon braucht man wieder mehr Zustände.

Deshalb jedes Booleanfeld genau überprüfen und vorrausplanen ob nicht ein Bytefeld besser passt.

[FBrust]

Hallo,

für mich ergibt das nur Sinn, wenn sichergestellt ist, dass das Feld mit dem Timestamp nie wieder geändert werden kann (da ja sonst die Information verlorenginge), insofern hat joachimd das schon richtiggestellt.

Wenn das Feld wieder geändert werden kann, muss man eben die bisher bekannten Datentypen verwenden (smallint, char, usw. usf).

Gruß
Frank

[Perlsau]

Nicht in allen Fällen ist es ratsam, als Repräsentant für ein Boolean-Feld die kleinste verfügbare Einheit zu nehmen. So erwarten die IbDac-Komponenten von Devart einen Integer. Mit einem SmallInt, der z.B. bei den FibPlus-Komponenten funktionierte, tritt bei Verwendung von IbDac eine Fehlermeldung auf bzw. wird dieses Feld nicht als Boolean erkannt. Der String "Boolean" muß im Feldnamen vorkommen, bei Firebird z.B.:

Code:

CREATE DOMAIN INTBOOLEAN AS
INTEGER
DEFAULT 0
NOT NULL
CHECK (VALUE IN (0,1));

COMMENT ON DOMAIN INTBOOLEAN IS 
'Boolean False oder True';

[Sir Rufo]

Zitat:

Zitat von sx2008 (Beitrag 1252569)

Also nach meinen Erfahrungen nach kommt es recht häufig vor, dass später weitere Zustände ausser True und False benötigt werden.
Deshalb verwende ich immer meistens ein BYTE (0..255) als Booleanfeld.
Beispiel 1:
Splashscreen_aktive: True oder False.
Später fällt mir dann ein, dass ich den Splashscreen nicht aktivieren möchte, wenn die Anwendung auf einem Terminalserver läuft; es soll aber einstellbar sein. Und schon sind es 3 Zustände...
Beispiel 2:
Kundensperre (z.B. weil der Kunde im Zahlungsrückstand ist).
Könnte man ja meinen dass True oder False ausreichend sind.
Später möchte man aber genauer wissen weshalb der Kunde gesperrt ist (Insolvent, Zahlungsrückstand, Gerichtsverfahren anhängig, ...) und schon braucht man wieder mehr Zustände.

Deshalb jedes Booleanfeld genau überprüfen und vorrausplanen ob nicht ein Bytefeld besser passt.

• SplashScreen aktiv ist immer ein Boolean => Entweder aktiv oder nicht aktiv
• Kundensperre aktiv ist immer ein Boolean => Entweder aktiv oder nicht aktiv

allerdings ist diese Information das Resultat von mehreren Umständen.

Also legt man diese Zusatzinformationen in eine eigene Tabelle und prüft, ob es dort Einträge gibt, und wenn ja, ob diese auf die aktuelle Situation passen.

SplashDisable

OSType

TerminalServer

Kunden

ID	Name
1	Peter
2	Paul

KundeSperren

KundeId	Grund	Seit
1	Insolvenz	01.01.2014

Das Beispiel mit den Rechnungen verhält sich ähnlich
Rechnungen

ID	KundeId	Nummer	Datum	Betrag
1	1	12345	01.01.2014	100,00
2	1	12346	02.01.2014	200,00

Zahlungen

ID	KundeId	Datum	Betrag
1	1	10.01.2014	250,00

RechnungZahlungen

RechnungId	ZahlungId	Betrag
1	1	100,00
2	1	150,00

Löscht man aus der Tabelle RechnungZahlung den Satz 2|1, dann verbleibt bei der Zahlung ein noch nicht zugeordneter Betrag von 150,00 und die Rechnung 2 ist wieder komplett offen.

Den Boolean-Wert für RechnungBezahlt bekommt man also durch die Analyse der entsprechenden Tabelle.

[FBrust]

Hallo,

Perlsau meint vermutlich, dass Smallint in Verbindung mit Domäne in Firebird über IBDAC nicht funktioniert.

Als normale Spalte vom Typ "Smallint", in der 0 oder 1/-1 gespeichert werden, gibt es keine Probleme.


Gruß
Frank

[Furtbichler]

Zitat:

Zitat von Sir Rufo (Beitrag 1252580)

Den Boolean-Wert für RechnungBezahlt bekommt man also durch die Analyse der entsprechenden Tabelle.

Nicht sonderlich performant (als berechnetes Feld), oder würdest Du das über einen Trigger lösen?

[sx2008]

Zitat:

Zitat von Sir Rufo (Beitrag 1252580)

Den Boolean-Wert für RechnungBezahlt bekommt man also durch die Analyse der entsprechenden Tabelle.

Wie soll dann ein Kunde gesperrt werden weil der Rechnungsempfänger z.B. insolvent ist?
Manche Versender geben Kunden mit negativer Schufaauskunft auch eine eingeschränkte Kundensperre und verschicken z.B. nur per Nachnahme.
Ein Sperrfeld in der Kundentabelle ist absolut sinnvoll und muss mehr Informationen tragen können als nur True oder False.

[DateTimeError]

Sorry, wenn ich mich hier als völlig DB.Außenstehender einmische:

Wer ein "Falsch" nicht als Nil/Null/0 wegschreibt, sollte seine Existenzbeschreibung überdenken.

Ob man ein plus1 oder minus1 als "Zutreffend/Richtig/Wahr" interpretiert, scheint mir im Auge des *perversen* Betrachters zu liegen:

Frage: "Sammeln Sie Kinderpornos?"
Antwort: "Nie und Nimmer!"
Also minus1 statt null?

Alles klar?

[Sir Rufo]

Zitat:

Zitat von Furtbichler (Beitrag 1252583)

Zitat:

Zitat von Sir Rufo (Beitrag 1252580)

Den Boolean-Wert für RechnungBezahlt bekommt man also durch die Analyse der entsprechenden Tabelle.

Nicht sonderlich performant (als berechnetes Feld), oder würdest Du das über einen Trigger lösen?

Darum ging es bei dieser Betrachtung zwar nicht, aber den gesamten Status (wieviel und komplett) zur Rechnung in einer separaten Tabelle und Update per Trigger.

[Sir Rufo]

Zitat:

Zitat von sx2008 (Beitrag 1252585)

Zitat:

Zitat von Sir Rufo (Beitrag 1252580)

Den Boolean-Wert für RechnungBezahlt bekommt man also durch die Analyse der entsprechenden Tabelle.

Wie soll dann ein Kunde gesperrt werden weil der Rechnungsempfänger z.B. insolvent ist?
Manche Versender geben Kunden mit negativer Schufaauskunft auch eine eingeschränkte Kundensperre und verschicken z.B. nur per Nachnahme.
Ein Sperrfeld in der Kundentabelle ist absolut sinnvoll und muss mehr Informationen tragen können als nur True oder False.

Indem man die Kundensperre entsprechend der Meta-Informationen interpretiert?
Insolvenz, Schufa, etc. -> Bar, Nachnahme
Doofmann -> gar nicht

In der Kundentabelle selber fehlt auch die Möglichkeit einer reinen Sperr-Historie.
Irgendwie machbar, aber umständlich und gehört irgendwie nicht so zusammen wie die Firmierung und die Handelsregister-Nummer.

[Perlsau]

Zitat:

Zitat von FBrust (Beitrag 1252582)

Perlsau meint vermutlich, dass Smallint in Verbindung mit Domäne in Firebird über IBDAC nicht funktioniert. Als normale Spalte vom Typ "Smallint", in der 0 oder 1/-1 gespeichert werden, gibt es keine Probleme.

Genau! :thumb:

17.13.1.8.2.2 BooleanDomainFields Property
Used to create TBooleanField for fields that have domain of the integer data type, and the domain name contains 'BOOLEAN'.
Class: TIBCDataSetOptions
Syntax: property BooleanDomainFields: boolean default False;
Remarks: If the BooleanDomainFields property is set to True, TBooleanField objects are created for fields that have domain of the integer data type, and the domain name contains 'BOOLEAN'.

[mirage228]

Da Du ja MySQL bzw. MariaDB verwendest, kann ich Dir vllt. sagen wie ich das bei MySQL 5.1 mit UniDAC handhabe.

Ich erstelle meine DB-Modelle mit MySQL Workbench. Da kannst Du durchaus "BOOLEAN"-Typen anlegen. Die werden in der Datenbank auf TINYINT(1) Felder gemappt. Das finde ich legitim und habe das bisher immer so verwendet. Wenn Du BIT(1) verwendest ist das Anzeigen der Werte schwierig, da Bitwerte nicht-druckbare Zeichen sind.

Mit UniDAC sollte auch ein

Delphi-Quellcode:

Field.AsBoolean

kein Problem sein. EnableBoolean bei TUniQuery ist bei MySQL standardmäßig auf True, womit TINYINT(1) als TBooleanField angesprochen werden kann.

Viele Grüße