[SQL] Circular foreign key, hoe vermijden?

[LeonM]

Ik heb een database met oa de tabellen "gerbuikers" en "contracten". In de gebruikers tabel staat een veld contractNr, welke een foreign key constraint heeft naar de contracten tabel. In de contracten tabel staat een veld "beheerder", welke een foreign key heeft in de gebruikerstabel.

Dit geeft een circular reference.

Met verwijderen van een contractrecord geeft dit weinig problemen, je geeft alle gebruikers die dat contract gebruiken eerst een ander contractNr, en dan kan het contract prima worden verwijderd. Aanmaken is een ander verhaal, als je een nieuwe user met nieuw contract aan wilt maken. Je kunt de user niet aanmaken omdat er nog geen contract voor is, en geen contract aanmaken omdat de user nog niet bestaat.

Ik wil wel de consistentie van de db bewaken, het is niet acceptabel als een gebruiker een niet bestaand contractnummer heeft, of een contract een niet bestaande beheerder heeft. Hoe los je zoiets op?

Ik vind via google genoeg documentatie over circular foreign keys, maar nergens hoe je zo'n situatie kunt voorkomen.

[Emmeau]

Door je data model nog eens kritisch onder de loep te nemen.
Gebruikers kunnen maar '1' contract hebben nu bijvoorbeeld...

en een gebruiker is geen beheerder. Doe dan iets met 'mensen' en rol-koppel-tabellen.

[Verwijderd]

Waarom moet de relatie per se twee kanten op gedefinieerd zijn? Je kunt toch gewoon contracten hebben die bij een bepaalde gebruiker horen? Het is in mijn ogen een beetje onlogisch dat een gebruiker alleen mag bestaan als hij/zij een contract heeft. Het is bovendien onlogisch dat een gebruiker slechts één contract kan hebben.

[LeonM]

Gebruikers kunnen maar 1 contract hebben, wellicht is de naam "contract" ongelukkig gekozen, het gaat hier om de abonnementdetails van de klant. Meerdere gebruikers mogen wel het zelfde contract hebben (bijvoorbeeld voor families of bedrijven), in dat geval is de beheerder de gene die gefactureerd wordt.

Het is in mijn ogen een beetje onlogisch dat een gebruiker alleen mag bestaan als hij/zij een contract heeft.

Gebruikers hebben altijd een abonnement, immers moeten de door hun afgenomen diensten altijd kunnen worden gefactureerd. De foreign key is daarom ook belangrijk, er mogen geen problemen ontstaan waarbij een user een niet (meer) bestaand contract heeft, waardoor er niet kan worden gedactureerd. Andersom dus ook, als de beheerder van een contract zijn lidmaatschap opzegt, moet zijn contract blijven bestaan (als er nog andere gebruikers gebruik van maken), zonder een geldige beheerder kan je niemand factureren.

[Onbekend]

LeonM schreef op maandag 12 juli 2010 @ 19:15:
Gebruikers hebben altijd een abonnement, immers moeten de door hun afgenomen diensten altijd kunnen worden gefactureerd.

Dus op het moment dat de klant het abonnement opzegt, verwijder je ook meteen de klant. (En waar gaat die laatste factuur heen? Die klant bestaat niet meer.)

Je kan beter een klantentabel hebben waarin je de eigenschappen van de klant opslaat,
en een abonnemententabel met de eigenschappen van elk type abonnement,
en daarnaast een klantabonnmenttabel waarin gedefinieerd staat welke klant welke abonnementen heeft.
Zodra de klant een abonnement opzegt, verwijder je later alleen maar een regel uit de klantabonnmenttabel maar de (oude) klant zelf blijft bestaan.

[ Voor 10% gewijzigd door Onbekend op 12-07-2010 19:21 ]

[djluc]

Ik zou dat niet in je databasemodel verwerken, die business logic. Ik zou je database juist wat flexibeler opzetten. Dus: contract - contact-persoon-koppeltabel(met rol) - persoon.

[Verwijderd]

LeonM schreef op maandag 12 juli 2010 @ 19:15:

Gebruikers hebben altijd een abonnement, immers moeten de door hun afgenomen diensten altijd kunnen worden gefactureerd.

Je kunt toch een gebruiker hebben zonder abonnement? Wat is het bezwaar daartegen? Als iemand geen diensten afneemt hoeft er niets te worden gefactureerd. Totdat een gebruiker diensten afneemt is hij/zij gewoon een prospect.

[Verwijderd]

Onbekend schreef op maandag 12 juli 2010 @ 19:21:

Zodra de klant een abonnement opzegt, verwijder je later alleen maar een regel uit de klantabonnmenttabel maar de (oude) klant zelf blijft bestaan.

Natuurlijk niet! Je gooit dan gegevens weg. Een opzegging is een update van bestaande gegevens, je gaat nooit zomaar informatie verwijderen om iets als een opzegging.

Een contract heeft een begindatum, opzegdatum en einddatum. Eventueel ook een gefactureerd-tot-datum.

[Freee!!]

Dit heeft helemaal niks met SQL te maken en alles met database ontwerp. En als ik zo'n ontwerp in zou leveren, zou ik op staande voet ontslagen worden.

[Gomez12]

LeonM schreef op maandag 12 juli 2010 @ 19:15:
Gebruikers kunnen maar 1 contract hebben, wellicht is de naam "contract" ongelukkig gekozen, het gaat hier om de abonnementdetails van de klant. Meerdere gebruikers mogen wel het zelfde contract hebben (bijvoorbeeld voor families of bedrijven), in dat geval is de beheerder de gene die gefactureerd wordt.

Dus eigenlijk kan je geen 2 abbonnementen aan 1 klant verkopen? En als een familie een contract heeft ( en iedereen gebruiker is ) dan kan zoon/dochter lief niet voor zichzelf een ander abbo kiezen?

[...]
Gebruikers hebben altijd een abonnement, immers moeten de door hun afgenomen diensten altijd kunnen worden gefactureerd.

Je zegt het zelf al, een afgenomen dienst moet gefactureerd kunnen worden ( zeg er even bij dat het enkel opgaat binnen de abbonementsduur en je hebt de basis voor een werkende dbase-opzet ). Gebruiker!=abbonnement ( je wilt veelal wat gebruikersgeschiedenis etc bewaren )

Normaliter heb je altijd extra gebruikers in je systeem staan ( ex-contractanten / prospects etc ) net zoals je ook altijd extra abbonnementen in je systeem hebt staan ( oude abbonnementsvormen / nog uit te komen abbonnementsvormen etc )

Simpel gezegd, je hele gedachte dat elke gebruiker/abbonnement gefactureerd moet worden is in 99,99999% van de gevallen fout ( nog even daargelaten dat dit business-logic is die je niet in je db wilt hebben )

Gewoon even een nieuwsgierige vraag, als ik vorige maand al mijn abbonnementen bij jullie heb opgezegd en deze maand wil ik een credit over het betaalde geld, hoe wil je dit momenteel doen als ik niet meer in het systeem sta?

Freee!! schreef op maandag 12 juli 2010 @ 19:46:
Dit heeft helemaal niks met SQL te maken en alles met database ontwerp. En als ik zo'n ontwerp in zou leveren, zou ik op staande voet ontslagen worden.

Ach, zelfs met dbase ontwerp heeft het nog niet eens iets te maken. De hele gedachtengang is gewoon fout ( mits er geen super-super speciale reden is )

[ Voor 16% gewijzigd door Gomez12 op 12-07-2010 20:27 ]

[Onbekend]

Verwijderd schreef op maandag 12 juli 2010 @ 19:28:
[...]

Natuurlijk niet! Je gooit dan gegevens weg. Een opzegging is een update van bestaande gegevens, je gaat nooit zomaar informatie verwijderen om iets als een opzegging.

Een contract heeft een begindatum, opzegdatum en einddatum. Eventueel ook een gefactureerd-tot-datum.

Dit was maar voor het idee. In werkelijkheid wil je het veel uitgebreider hebben met betalinggegevens, contactgegevens tussen klant en bedrijf, en andere logs over de klant zelf. En dit wil je natuurlijk een X aantal jaar bewaren, ook al heeft de klant al opgezegd.
Maar ik heb het idee dat de topicstarter hier nog niet aan toe is. Eerst moet de topicstarter maar met een completer (beter) ontwerp hier posten voordat we er verder op in gaan.

[ Voor 4% gewijzigd door Onbekend op 12-07-2010 22:43 ]

[LeonM]

Freee!! schreef op maandag 12 juli 2010 @ 19:46:
Dit heeft helemaal niks met SQL te maken en alles met database ontwerp. En als ik zo'n ontwerp in zou leveren, zou ik op staande voet ontslagen worden.

Dat is dan wel weer ironisch, mijn werkgever heeft dit ontwerp gemaakt, dus ontslaan is dan weer zo lastig

Ik ben inmiddels door jullie overtuigd dat we nog eens goed moeten kijken naar het ontwerp, een abonnement type tabel met koppeltabel lijkt mij inderdaad een beter idee.

[Freee!!]

LeonM schreef op dinsdag 13 juli 2010 @ 11:07:
[...]
Dat is dan wel weer ironisch, mijn werkgever heeft dit ontwerp gemaakt, dus ontslaan is dan weer zo lastig

Tsja, best wel lastig inderdaad

Ik ben inmiddels door jullie overtuigd dat we nog eens goed moeten kijken naar het ontwerp, een abonnement type tabel met koppeltabel lijkt mij inderdaad een beter idee.

Hopelijk kan je je werkgever ook overtuigen.