referenz auf sich selbst

Hallo,

Ich habe mal eine Frage zum Datenbank desgin. Ich hoffe jemand kann mir helfen.
Ich habe eine Personendatenbank mit drei “Beziehungen”.
Es gibt eine Tabelle mit Personen. Jede dieser Personen hat in derselben Tabelle einen Vater, eine Mutter und/oder einen Lebenspartner (Immer jeweils nur 1). Es kann aber auch sein, dass es keinen der drei gibt, also eine Person keinen Vater und keine Mutter hat und kein Lebenspartner von irgendwem ist. Wie würde man dies denn am sinnvollsten modellieren? Ich habe jetzt mal eine Tabelle Personen gemacht.

Tabelle Personen:
id person
name
vorname
lebenspartner (verweist auf id person)
vater (verweist auf id person)
mutter (verweist auf id person)

Innerhalb dieser Tabelle müsste dann in den Spalten Vater, Mutter und Lebenspartner auf eine andere Person derselben Tabelle verwiesen werden. Geht das überhaupt, oder bekomme ich dann Probleme beim Einfügen, Ändern oder Löschen. Die Personen sollen nicht gelöscht werden, wenn man z.B. das Kind von einer Person löscht, sollen die Mutter und der Vater noch erhalten bleiben und nur die Referenz verschwinden usw.
Modelliert man das so? Kann man in Postgres überhaupt einen Fremdschlüssel auf sich selbst setzen? Oder gibt es da eine bessere, elegantere Möglichkeit?
Für Hilfe und Tipps bin ich wie immer sehr dankbar und bedanke mich schon einmal im Voraus für die guten Tipps.

Vielen Dank
Hanno

Warum soll das nicht gehen? Ist ja PG.

test=# create table personen (id int primary key, partner int references personen, vater int references personen, mutter int references personen, check ((case when partner is not null then 1 else 0 end + case when mutter is not null then 1 else 0 end + case when vater is not null then 1else 0 end) <= 1));
CREATE TABLE
Time: 10,196 ms
test=*# insert into personen values (1, null, null, null);
INSERT 0 1
Time: 0,552 ms
test=*# insert into personen values (2, null, null, null);
INSERT 0 1
Time: 0,251 ms
test=*# insert into personen values (3, 1, null, null);
INSERT 0 1
Time: 0,541 ms
test=*# insert into personen values (4, 1, 2, null);
ERROR:  new row for relation "personen" violates check constraint "personen_check"
DETAIL:  Failing row contains (4, 1, 2, null).
Time: 0,309 ms

Was ist aber, wenn Du auch Geschwister, Onkel, Tanten, Großcousine und Schwippschwager unterbringen willst?

Ich könnte mir eine extra Tabelle vorstellen, die in 2 Spalten Frendschlüssel auf Deine Personen-Tabelle hat und in einer dritten Spalten den Art der Verbindung anzeigt. Dann könnte man a) leicht weitere Beziehungstypen definieren und b) es kann ja durchaus auch mehrere Verbindungen zwischen 2 Leuten geben. Aber letztendlich hängt das davon ab, was Du machen willst und wie flexibel Du bleiben willst und wie streng Deine Constraints sein sollen.

Danke für die Antwort.
Es ist ausreichend nur Vater, Mutter und Lebenspartner abzubilden. Die Geschwister würde man ja noch über die gemeinsamen Väter/Mütter bekommen, oder nicht? Onkel und Tanten wären nach demselben Prinzip mit einer zusätzlichen Spalte auch möglich. Aber das brauch ich bisher nicht.

Wenn ich eine extra Tabelle mit 2 Spalten anlege wäre das auch eine Möglichkeit. Allerdings weiss ich dann ja nicht z.B. bei einer Vater-Kind-Beziehung, wer der Vater und wer das Kind ist. Oder verstehe ich das falsch? Ich weiss dann doch nur, dass es zwischen den beiden Personen eine Vater-Kind-Beziehung gibt.

Danke
Hanno