[SQL] Gelijkwaardige kruisrelatie tussen personen

[CurlyMo]

Neem de volgende situatieschets. Ik heb een entiteit persoon. Daarvan is bekend dat hij of zij relatie heeft inclusief een aantal gegevens over de partner en de specifieke type relatie (getrouwd of geregistreerd partnerschap). De partner is ofwel (nog) niet bekend, ofwel al wel bekend.

Dus stel we hebben de volgende situatie. Persoon A is getrouwd met persoon B. Ik sla persoon A op én persoon B. Van persoon B is nog goed als niks bekend. Op een gegevens moment is persoon B wel bekend met daarbij de gegevens dat persoon B getrouwd is met persoon A (zoals we al vanuit persoon A wisten). Persoon B was al ingevoerd dus we hoeven dan alleen zijn gegevens verder aan te vullen.

Ik loop nu te dubben over hoe ik dit goed moet opslaan. Dat kan naar mijn idee op twee manieren waarbij elke manier zijn voors en tegens heeft.

Optie 1

code:

persoon            relatie
| id | naam |      | id | persoon_id | soort    | partner_id | 
| 1  | A    |      | 1  | 1          | Getrouwd | 2          |
| 2  | B    |      | 2  | 2          | Getrouwd | 1          |

De FK zit vanzelfsprekend op relatie.partner_id > persoon.id en op relatie.persoon_id > persoon.id. Het voordeel van deze constructie is dat ik geen complexe koppeltabel nodig heb en dat bij het verwijderen van een van de twee personen de relatie ook automatisch verdwijnt. Het nadeel is echter dat ik de relatie tussen personen twee keer opsla. Zou ik de relatie één keer opslaan, dan heb ik aanvullende logica in mijn select nodig om te zien of een persoon opgeslagen staat als partner of persoon.Ook heb ik aanvullende logica nodig bij het invoeren om te zien of een persoon misschien al opgeslagen is als partner.

Optie 2

code:

persoon            relatie            persoon_relatie
| id | naam |      | id | soort    |  | relatie_id | persoon_id | partner_id |
| 1  | A    |      | 1  | Getrouwd |  | 1          | 1          | 2          |
| 2  | B    |                         | 1          | 2          | 1          |

De FK zit nu op persoon_relatie.relatie_id > relatie.id, persoon_relatie.persoon_id > persoon.id en op persoon_relatie.partner_id > persoon.id. Het voordeel van deze structuur is dat ik maar een record heb in de relatie tabel voor deze specifieke relatie*. Het ophalen van beide personen inclusief de relatie kan met iets simpelere select. Het enige probleem is dat het consistent houden van deze relatie aanvullende logica vergt. Zo zou er een trigger moeten draaien om bij het invoeren van de waardes (1, 1, 2) ook de invoer doet (1, 2, 1) zodat beide personen ook een keer partner zijn. Daarnaast heb ik triggers nodig om bij het verwijderen van een persoon tevens het relatie record te verwijderen.

Wat is in dit geval nou het meest ideale model voor het opslaan van zo'n gelijkwaardige kruisrelatie?

*In dit voorbeeld zouden sowieso meerdere records vrij nutteloos zijn echter sla ik meer zaken op de relatie zoals de datum waarop iemand getrouwd is

[Paul]

Ik weet niet wat voor soort systeem het is en of er meer relaties gelegd gaan worden maar het voordeel van nummer 2 is (buiten de normalisatie van het soort relatie ) dat je erg simpel ook de relatie ouder <> kind, of "broer", of desnoods "klasgenoot basisschool groep 3" tot en met "klasgenoot basisschool groep 7" (al kun je die laatste misschien beter op een andere manier opslaan ).

Wel zou ik in persoon_relatie het partner_id weg te laten en twee regels te maken met persoon_id, want ook de partner is een regel in "persoon".

[CurlyMo]

Paul schreef op maandag 30 november 2015 @ 10:37:
Wel zou ik in persoon_relatie het partner_id weg te laten en twee regels te maken met persoon_id, want ook de partner is een regel in "persoon".

Dus:

code:

persoon_relatie
| relatie_id | persoon_id |
| 1          | 1          |
| 1          | 2          |

Nadeel van die variant is dat het moeilijker wordt om de partner van een persoon te selecteren. Vandaar de tussenweg door toch de partner erbij op te slaan.

[ValHallASW]

Leuk leesvoer hierbij: Gay marriage: the database engineering perspective

[Paul]

Oh ja, die had ik niet opgeschreven, je moet dus nog ergens een ID opslaan voor de relatie zelf

code:

persoon_relatie
persoon_relatie_id | relatie_id | persoon_id |
1                  | 1          | 1          |
1                  | 1          | 2          |
2                  | 1          | 125        |
2                  | 1          | 7239       |

SQL:

SELECT
    relatie_id, persoon_id
FROM
    persoon_relatie
WHERE
    persoon_relatie_id IN
    (
    SELECT
        persoon_relatie_id
    FROM
        persoon_relatie
    WHERE
        relatie_id = ?
    )
    AND NOT relatie_id = ?

Uit de losse pols + mijn SQL is wat roestig dus ik weet niet eens of bovenstaande überhaupt klopt maar zoiets?

[CurlyMo]

ValHallASW schreef op maandag 30 november 2015 @ 10:54:
Leuk leesvoer hierbij: Gay marriage: the database engineering perspective

Daar zijn we hier in het team al helemaal doorheen gelopen. Het systeem ondersteund al sekse neutrale relaties en polygamie. Ook weten we in theorie van de polygame relatie welke relatie is verbroken en welke nog bestaan (om weer vast te stellen of iemand alleenstaand is). We gaan hier dus nog een stapje verder en hebben model 15. Dat is dus allemaal al afgedekt. Enige puntje blijft nog de manier waarop je een relatie opslaat. De schrijver van het blog komt uit op mijn eerste optie. Ik betwijfel of dat de juiste is.

@Paul, sla je nu niet alsnog de partner op in hetzelfde record als de persoon alleen noem je hem anders. Zo ja, dan wordt het een definitiekwestie

[ Voor 12% gewijzigd door CurlyMo op 30-11-2015 11:07 ]

[Paul]

CurlyMo schreef op maandag 30 november 2015 @ 11:03:
@Paul, sla je nu niet alsnog de partner op in hetzelfde record als de persoon alleen noem je hem anders. Zo ja, dan wordt het een definitiekwestie

Nee Het is zeg maar nr. 12 zonder de extra info in de "marriages", persoon_relatie_id is dan de FK naar de (in mijn voorbeeld niet bestaande) "marriages"-tabel.

code:

persoon_relatie
persoon_relatie_id | relatie_id | persoon_id |
1                  | 1          | 1          |
1                  | 1          | 2          |
2                  | 1          | 125        |
2                  | 1          | 7239       |

De relatie met ID 1 is van het type 1 en bevat personen 1 en 2. De relatie met ID 2 is van type 1 en bevat personen 125 en 7239.

[CurlyMo]

Schiet me maar lek, maar het komt niet binnen

Kan je de hele situatie even kort schetsen?

---

Daarbij, klopt mijn aanname dat ik niet anders kan dan triggers gebruiken om het goed te implementeren?

[ Voor 36% gewijzigd door CurlyMo op 30-11-2015 13:01 ]