Familienbeziehungen per SQL abfragen

Post by Marco Schmidt
Nun kann man sich für Familienbeziehungen wie Bruder / Schwester,
Tante / Onkel, Großvater / Großmutter u.s.w. einer Person jeweils alle
möglichen SQL-Statements ausdenken. Allerdings braucht man dann für
jede Person und jede denkbare Beziehung ein einzelnes Statement.

Ganz so schlimm ist es wohl nicht: Eine View-Definition
pro Beziehung (nicht noch "für jede Person") reicht.

Wenn die zu untersuchenden Beziehungen einem allgemeinen
Schema folgen, kann man die SQL-Anweisungen zudem noch
programmatisch generieren lassen.

Um Effiziens würde ich mir da erst einmal keine Gedanken
machen. Durch Einführung redundanter Tabellen mit Zwischen-
ergebnissen könnte man sich mehr Probleme schaffen und
ein guter SQL-Abfrageoptimierer ist durch "Handoptimierung"
auch nicht immer zu schlagen.

Ich würde erst einmal die Views schreiben und die
Effizienz zunächst vergessen. Erst einmal mit einfachen
Views anfangen (Bruder, Schwester) und diese dann für
kompliziertere Views verwenden. Dadurch wird das eigentlich
relativ einfach. Zu jedem View die Semantik in einem
Kommentar spezifizieren.

Marco Schmidt

2003-12-10 19:06:51 UTC

Post by Stefan Ram
Ganz so schlimm ist es wohl nicht: Eine View-Definition
pro Beziehung (nicht noch "für jede Person") reicht.

Ich meinte auch: für jede Person, für die man die Beziehungen wissen
möchte. Views sind leider keine Option für mich, da ich MySQL benutze.
Dort werden Views wohl erst in 5.0 oder 5.1. Vielleicht sollte ich
zumindest mal auf 4.1 umstellen, damit ich den auf
<http://www.mysql.com/doc/en/ANSI_diff_Views.html> beschriebenen
Ansatz ausprobieren kann.

[...]

Post by Stefan Ram
Um Effiziens würde ich mir da erst einmal keine Gedanken
machen. Durch Einführung redundanter Tabellen mit Zwischen-
ergebnissen könnte man sich mehr Probleme schaffen und
ein guter SQL-Abfrageoptimierer ist durch "Handoptimierung"
auch nicht immer zu schlagen.

Ich dachte halt, daß das vielleicht ein bekanntes Problem mit
existierender eleganter Lösung wäre.

Nach ein paar Praxistests muß ich allerdings sagen, daß man mit einer
Anfrage bereits mehrere Verwandte geliefert bekommen kann, z.B. Eltern
und Großeltern.

[...]

Gruß,
Marco

Stefan Ram

2003-12-10 22:16:11 UTC

Post by Stefan Ram
Ganz so schlimm ist es wohl nicht: Eine View-Definition
pro Beziehung (nicht noch "für jede Person") reicht.

Ich meinte auch: für jede Person, für die man die Beziehungen wissen
möchte. Views sind leider keine Option für mich, da ich MySQL benutze.

Und was hat das mit dem im Betreff erwähnten SQL zu tun?

Post by Marco Schmidt
Ich dachte halt, daß das vielleicht ein bekanntes Problem mit
existierender eleganter Lösung wäre.

Ich finde die Lösung mit SQL-Views elegant. Die Basistabelle sei

Person Tabelle
i künstlicher numerischer Primärschlüssel, Zahl
vater Vater, Fremdschlüssel auf Person.i
mutter Mutter, Fremdschlüssel auf Person.i
weiblich weiblich, Wahrheitswert

Dann kann man aufeinander aufbauende Views erzeugen.
Ein View einer Schicht verwendet nur Views einer niedrigeren
Schicht. Das ist inhaltlich praktisch eine Ontologie, die
unter Verwendung definierter Begriffe (wie Mutter und Elter)
dann weitere Begriffe (wie Tante oder Großvater) definiert.

Schicht 1

Mutter = die Mutter einer Person (falls definiert)

SELECT Person.i AS person, Person.mutter AS mutter FROM Person
WHERE Person.mutter Is Not Null

Vater = der Vater einer Person (falls definiert)

SELECT Person.i AS person, Person.vater AS vater FROM Person
WHERE Person.vater Is Not Null

Geschwister = ein Geschwister einer Person

SELECT Person.i AS person, Andere.i AS geschwister
FROM Person INNER JOIN Person AS Andere ON
Person.vater = Andere.vater AND
Person.mutter = Andere.mutter
WHERE Person.i <> Andere.i

Schicht 2

Elter = Abstraktion: der Vater oder die Mutter einer Person

SELECT person, vater as elter from vater UNION
SELECT person, mutter as elter from mutter

Bruder = ein männliches Geschwister einer Person

SELECT Geschwister.person AS person_, Geschwister.geschwister AS bruder
FROM Geschwister INNER JOIN Person ON Geschwister.geschwister = Person.i
WHERE Person.weiblich = False

Schwester = ein weibliches Geschwister einer Person

SELECT Geschwister.person AS person_, Geschwister.geschwister AS schwester
FROM Geschwister INNER JOIN Person ON Geschwister.geschwister = Person.i
WHERE Person.weiblich = True

Schicht 3

Tante = eine Schwester eines Elters

SELECT Elter.person AS person_, Schwester.schwester AS tante
FROM Elter INNER JOIN Schwester ON Elter.elter = Schwester.person_
GROUP BY Elter.person, Schwester.schwester

Onkel = ein Bruder eines Elters

SELECT Elter.person AS person_, Bruder.bruder AS onkel
FROM Elter INNER JOIN Bruder ON Elter.elter=Bruder.person_
GROUP BY Elter.person, Bruder.bruder

Großmutter = Mutter eines Elters

SELECT Elter.person AS person_, Mutter.mutter AS grossmutter
FROM Elter INNER JOIN Mutter ON Elter.elter = Mutter.person
GROUP BY Elter.person, Mutter.mutter

Großvater = Vater eines Elters

SELECT Elter.person AS person_, Vater.vater AS grossvater
FROM Elter INNER JOIN Vater ON Elter.elter = Vater.person
GROUP BY Elter.person, Vater.vater

Georg Verweyen

2003-12-10 13:02:48 UTC

Hallo Macro,

wenn es Dir nicht um das SQL geht, sondern um die Verwandschaft: Dazu
gibt es Spezial-Programme z.B.: GeneWeb.

MfG Georg V.v

Post by Marco Schmidt
In einer Personentabelle habe ich neben einem eindeutigen numerischen
Schlüssel id (als Primärschlüssel) für jede Person auch noch Felder
father_id und mother_id, die optional ausgefüllt werden können (falls
eben Mutter oder Vater auch Einträge in der Tabelle sind).
Nun kann man sich für Familienbeziehungen wie Bruder / Schwester,
Tante / Onkel, Großvater / Großmutter u.s.w. einer Person jeweils alle
möglichen SQL-Statements ausdenken. Allerdings braucht man dann für
jede Person und jede denkbare Beziehung ein einzelnes Statement.
Kennt jemand eine Möglichkeit, wie man mit weniger Anfragen pro Person
auskommt? Muß man dafür eine andere, eventuell redundantere
Speicherung oder sogar Extratabellen in Kauf nehmen?
Ich habe darüber nachgedacht, wie Nested-Set wiederverwendet werden
kann, aber da eine Person nun mal zwei statt nur einem direkten
"Vorgänger" im Baum hat fiel mir bislang nichts ein.
Gruß,
Marco

Volker Helm

2003-12-11 17:58:40 UTC

Hi,

Klingt leicht kompliziert. Da muss man sich schon etwas wie einen Parser
schreiben, um die ganzen Verwandtschaften herauszufiltern :-(

Am einfachsten wäre es, diese Daten in eine weitere Tabelle
auszugliedern, in der 'Person1_Id', 'Person2_id' und 'Verwandtschaft'
steht.
Es wird zwar etwas mehr Pflegeaufwand sein, allerdings ist die Abfrage
der Verwandtschaften sehr viel schneller.

Post by Marco Schmidt
Kennt jemand eine Möglichkeit, wie man mit weniger Anfragen pro Person
auskommt? Muß man dafür eine andere, eventuell redundantere
Speicherung oder sogar Extratabellen in Kauf nehmen?

Wäre am einfachsten von der SQL-Syntax der Abfrage, s.o..

Viel Vergnügen weiterhin,

Volker

Timo Stöckigt

2003-12-11 20:59:24 UTC

Warum erinnert mich diese Fragestellung nur an multidimensionale
Fachbegriffe? Mit einer entsprechenden Datenbank könnte man diese
Fragestellungen alle sehr leicht abfackeln.

Nicht dass es sich lohnt, für so ein kleines Problem eine MOLAP-Datenbank
aufzusetzen, aber MDX z.B. könnte hier enorm helfen. Gibt es irgend welche
Projekte, die MDX oder etwas äquivalentes für normale relationale
Datenbanken umsetzt?

Ciao
Timo

Bernd Nawothnig

2003-12-22 10:48:53 UTC

Hi Marco,

parent_id und ein zusätzliches gender-Feld wäre hier wohl sinniger.

Ansonsten ist das Ganze ein klassischer Fall für einen Self-Join.

Und? Da kannst Du stored procedures (MS-SQL, bzw. deren Äquivalente in
anderen SQL-Dialekten) doch gut für verwenden: Person reinstecken, getestete
Verwandtschaft als Boolean zurückliefern.

Wieso das? Es gibt die 3. Normalform nicht ganz ohne Grund ...

Post by Marco Schmidt
Ich habe darüber nachgedacht, wie Nested-Set wiederverwendet werden
kann, aber da eine Person nun mal zwei statt nur einem direkten
"Vorgänger" im Baum hat fiel mir bislang nichts ein.

Ach was. Normales SQL reicht da vollkommen aus.

Bernd

--
Those who desire to give up freedom in order to gain security,
will not have, nor do they deserve, either one. [T. Jefferson]

Marco Schmidt

2003-12-22 14:11:47 UTC

parent_id und ein zusätzliches gender-Feld wäre hier wohl sinniger.

Wieso? Es sollen doch innerhalb eines Personenverbunds beide
Elternteile referenziert werden können.

[...]

Ach was. Normales SQL reicht da vollkommen aus.

Klar reicht es grundsätzlich. Ich war nur auf der Suche nach einer
eleganten Lösung bzw. Abkürzungen für die zig einzelnen Anfragen.

Gruß,
Marco

Bernd Nawothnig

2003-12-22 17:15:56 UTC

Hi Marco,

parent_id und ein zusätzliches gender-Feld wäre hier wohl sinniger.

Wieso? Es sollen doch innerhalb eines Personenverbunds beide
Elternteile referenziert werden können.

Können sie doch so auch. Was Vater und was Mutter ist, sagt Dir dann das
gender-Feld. Erscheint mir irgendwie naheliegender, Eigenschaften, die zu
den Personen direkt gehören (wie hier das Geschlecht), auch dort abzulegen
und nicht indirekt zu verklausulieren. Die ältesten Einträge hätten sonst
nämlich auch keine Geschlechtsinformation.

Post by Bernd Nawothnig
Ach was. Normales SQL reicht da vollkommen aus.

Klar reicht es grundsätzlich. Ich war nur auf der Suche nach einer
eleganten Lösung bzw. Abkürzungen für die zig einzelnen Anfragen.

Die Eleganz von SQL liegt für mich im einfachen Grundaufbau. Und den sollte
man nicht ohne Not verlassen.

Bernd

--
Those who desire to give up freedom in order to gain security,
will not have, nor do they deserve, either one. [T. Jefferson]

Bernd Nawothnig

2003-12-22 17:21:29 UTC

parent_id und ein zusätzliches gender-Feld wäre hier wohl sinniger.

Wieso? Es sollen doch innerhalb eines Personenverbunds beide
Elternteile referenziert werden können.

Können sie doch so auch. Was Vater und was Mutter ist, sagt Dir dann das
gender-Feld. [...]

Sorry, beide gleichzeitig geht natürlich nicht. Müssen doch zwei Felder
rein.

Bernd

--
Those who desire to give up freedom in order to gain security,
will not have, nor do they deserve, either one. [T. Jefferson]

Bernd Eckenfels

2003-12-23 01:45:35 UTC

Post by Bernd Nawothnig
Sorry, beide gleichzeitig geht natürlich nicht. Müssen doch zwei Felder
rein.

Und fuer die TS Geschlechtsumwandlungen noch ne Tabelle mit Zeitpunkt/Geschlecht
paaren? Wir wollen ja niemanden benachteiligen. Warum ist eigentlich die
Welt immer so komplex im Vergleich zu DB Schemas?

Gruss
Bernd

--
eckes privat - http://www.eckes.org/
Project Freefire - http://www.freefire.org/

Bernd Nawothnig

2003-12-23 12:11:26 UTC