Nadelen van het gebruik van een nullable externe sleutel in plaats van het maken van een intersectietabel

De twee modellen vertegenwoordigen verschillende relaties.

Door een samenvoegtabel te gebruiken , je modelleert een veel-op-veel-relatie.

Door een eenvoudige externe sleutel te gebruiken, modelleer je een een-op-veel-relatie.

Het nadeel van een nullabel vreemde sleutel is dat je de relatie niet kunt modelleren als veel-op-veel, als dat is wat je probeert te bereiken.

Op basis van je wijziging van de vraag, splits je in feite de leerlingentafel in twee tafels met dezelfde sleutel. Ik zie dit over het algemeen op tafels met veel te veel velden, dus iemand splitst ze in twee om beter beheersbaar te zijn (ik noem het lippenstift op een varken zetten).

Door de studententafel te splitsen, maak je de tweede tabel optioneel omdat er geen record in de tweede tabel hoeft te bestaan. Dat lijkt erg op een veld dat niet hoeft te worden ingesteld omdat het nul kan zijn.

Als je een een-op-veel-relatie wilt, kun je veel beter een enkele tabel gebruiken en de school-ID toestaan nul zijn in de studententabel. Er is geen reden om null-waarden in velden te vermijden, zelfs niet voor een externe sleutel. Dat betekent dat de buitenlandse relatie optioneel is: ontwikkelaars en DBAs begrijpen dat duidelijk, en de onderliggende database-engine zou zeker goed moeten werken.

Maak je geen zorgen als je je zorgen maakt over joins. Er is een goed gedefinieerde semantiek voor hoe joins werken met null-velden. Door een enkele tabel te gebruiken, kun je twee tafels samenvoegen in plaats van drie.

Opmerkingen

• Dus als ik een een-op-veel-relatie aan het modelleren ben (met optionele deelname aan de: 1-zijde), moet ik een externe sleutel gebruiken ondanks het feit dat deze NULL waarden kan hebben?
• @Tom ja, dat is precies hoe je het moet modelleren. Hoewel het technisch mogelijk is om een join-tabel te gebruiken, staat het datamodel veel tot veel toe, dus je hebt triggers en databaselogica nodig om dat te voorkomen. U bent beter af door de relatie zo te beperken dat het onmogelijk is om onjuiste gegevens toe te voegen.
• Ik heb mijn vraag aangepast.Ik heb alleen student_id een primaire sleutel gemaakt in de tabel School_has_Student, waardoor de relatie één-op-veel bleef. Welke nadelen heeft deze methode ten opzichte van het gebruik van een externe sleutel?
• @Tom Ik heb mijn antwoord bewerkt.

Je schreef hierboven in een opmerking:

het boek “Fundamentals of Database Systems” […] zegt [.. .] dat het wordt aanbevolen om een kruisingstabel te gebruiken als er veel NULL-waarden in de kolom met de externe sleutel staan (bijvoorbeeld: als 98% van de werknemers geen afdeling beheert)

Als er veel NULL-waarden in de kolom met de externe sleutel staan, zullen uw programmas met deze grotendeels lege kolom te maken krijgen voor elke record die ze verwerken. De kolom neemt waarschijnlijk wat schijfruimte in beslag hoewel het in 98% van alle gevallen leeg is, betekent het opvragen van de relatie dat je die kolom doorzoekt die je meer netwerkverkeer oplevert, en als je een ORM gebruikt die je klassen uit je tabellen genereert, hebben je programmas ook meer ruimte nodig bij de client kant dan nodig is. Met behulp van een inters ection-tabel vermijdt dit, er zijn alleen linkrecords nodig waar de equivalente externe sleutel anders niet NULL zou zijn.

Daar staat tegenover, als je niet slechts een paar NULL-waarden hebt, laten we zeggen 50% of meer relaties zijn niet NULL, het gebruik van een intersectietabel geeft het tegenovergestelde effect – meer schijfruimte, hogere complexiteit resulterend in meer netwerkverkeer enz.

Dus het gebruik van een intersectietabel is slechts een vorm van optimalisatie, alleen zinvol voor een specifiek geval, en vooral tegenwoordig, waar schijfruimte en geheugen goedkoper werden, veel minder vaak nodig. Merk op dat “Fundamentals of Database Systems” oorspronkelijk meer dan 20 jaar geleden werd geschreven (ik vond een verwijzing naar de tweede editie uit 1994), en ik denk dat die aanbeveling er toen al in zat. Vóór 1994 was optimalisatie van de ruimte waarschijnlijk veel belangrijker dan nu, aangezien massaopslag nog steeds duurder was en computers en netwerken veel langzamer waren dan vandaag.

Als kanttekening bij een kieskeurige opmerking: de bovenstaande verklaring probeert alleen maar te anticiperen op wat de auteur van “Fundamentals of Database Systems” in gedachten had met zijn aanbeveling. Ik denk dat hij een ruwe, algemene verklaring aflegde, geldig voor de meeste systemen. In sommige databases zijn er andere mogelijke optimalisaties zoals “sparse columns” die het gebruik van een intersectietabel nog meer achterhaald maken.

Begrijp die aanbeveling dus niet verkeerd. Het boek vertelt het niet. u de voorkeur geeft aan kruisingstabellen voor {0,1}:n relaties in het algemeen, of – zoals u schreef – dat dit de “juiste manier” is. Gebruik dergelijke optimalisaties die uw programmas alleen gecompliceerder zullen maken wanneer je hebt ze echt nodig.

Opmerkingen

• Je ‘ gaat veel uit van de implementatie van de database, vooral gezien het feit dat het OP geen ‘ een specifieke noemt. Het ‘ is meer dan waarschijnlijk dat de database slim genoeg is om te gebruiken slechts een kleine hoeveelheid ruimte voor schaarse kolommen.
• @gardenhead: waarom denk je dat dit ” meer dan waarschijnlijk is “?
• Het feit dat databases bestaan al tientallen jaren en zijn sterk geoptimaliseerd omdat ze een cruciaal onderdeel zijn van de meeste infrastructuren.
• @gardenhead: het klinkt voor mij dat je veel zwaardere onterechte aannames doet dan ik. Zie desalniettemin mijn bewerking.

Conceptueel model zal er als volgt uitzien, namelijk erg onorthodox om minder te zeggen:

Fysiek model zal er als volgt uitzien, wat verwarrend is om het minder te zeggen (mensen zullen denken dat het” M: M is, tenzij ze goed kijken):

Mijn suggestie:

Als je wilt, veel kolommen (FK of anderszins), die niet van toepassing zijn op de meeste studenten, scheiden de tabellen in roltabellen met 1: 1 rels. Maar dat is niet omdat ze FK zijn, dit is omdat de kolommen niet op de meeste rijen van toepassing zijn.

Anders , nullable FK zijn een normaal onderdeel van een database en join-tabellen zijn meestal voor M: M rels.

Algemeen gebruik van 1: 1-rels is voor roltabellen met kolommen die alleen van toepassing zijn als de entiteit van een bepaald type is, en het extraheren van BLOB-kolommen voor prestatie- of opslagoverwegingen. Het coderen van null-waarden in FKs is daarvoor niet een algemeen gebruik.

Naast andere antwoorden zou ik erop willen wijzen dat een null-waarde voor de externe sleutel dubbelzinnig is. Betekent dit:

1) De school van de leerling (indien aanwezig) is onbekend (dit is de standaardbetekenis van “nul” – waarde is onbekend)

2) Het is weten of de leerling wel of niet een school heeft, en zij hebben er geen

Als u de standaardbetekenis van null gebruikt, hoe zou u dan leerling heeft geen school voorstellen in uw model voor buitenlandse sleutels. In dat geval je “zou waarschijnlijk een” geen school “-vermelding moeten maken, met zijn eigen ID in de schooltafel. (niet ideaal)

Reacties

• Het boek ” Fundamentals of Database Systems ” vermeldt dat er 3 interpretaties zijn voor NULL, dit kan betekenen: 1) Onbekende waarde. 2) Niet beschikbare of ingehouden waarde. 3) Niet van toepassing zijnde attribuut (ik denk dat deze interpretatie betekent dat je een NULL voor een externe sleutel).
• Dat ‘ is een nuttige lijst, maar de semantiek van null (of welke waarde dan ook) kan door de gebruiker worden gedefinieerd.kan betekenen wat de ontwerper zegt dat het betekent, niet beperkt tot die lijst. Het probleem is hoe je verschillende betekenissen kunt onderscheiden als er meer dan één vereist is (of zelfs onbedoeld wordt opgeslagen).
• Dus stel je voor dat ik een intersectietabel moet maken in plaats van een nullable externe sleutel?
• @Tom Ja, ik denk dat dat in dit geval beter is
• @BradThomas – om dezelfde dubbelzinnigheid te vermijden bij het gebruik van een kruisingstabel, zou je geval 2 voorstellen (het is bekend dat de student geen school) door een record in de intersectietabel met een NULL School_ID?

Database-tabellen hebben dit leuk ding genaamd beperkingen. Het is dus heel gemakkelijk om een intersectietabel te maken die slechts 1 van elke leerling in de tabel laat verschijnen, maar veel scholen in die tabel. Je effectief een

Theorie geven is leuk, maar uiteindelijk ben je dat ook ga je database modelleren naar de vragen die je stelt.

Als je vaak een vraag wilt stellen met de vraag: “welke leerlingen zitten er op mijn school”, wil je dan echt de hele leerlingentafel doorzoeken of een gemakkelijke intersectietabel.

In databases: optimaliseer voor de vragen die u stelt.

Daar is een use-case waarbij het gebruik van een derde tabel echt zinvol kan zijn. Het voorbeeld lijkt misschien puur hypothetisch, maar ik hoop dat het mijn punt goed illustreert. Laten we aannemen dat u meer kolommen toevoegt aan de students -tabel en op een gegeven moment besluit u uniciteit op de records af te dwingen via een samengestelde index voor verschillende kolommen. Het is zeer waarschijnlijk dat u “Ik moet ook de school_id -kolom opnemen, en hier begint het rommelig te worden. Door de manier waarop SQL is ontworpen, voegt u verschillende identieke records in waarbij school_id is NULL zal mogelijk zijn. Het is volkomen logisch vanuit technisch perspectief, maar is contra-intuïtief en kan leiden tot onverwachte resultaten. Aan de andere kant is het afdwingen van uniciteit aan de intersectietabel is eenvoudig.

Ik moest onlangs een dergelijke “optionele” relatie modelleren, waarbij de vereiste voor een uniciteitsbeperking te wijten was aan een tijdstempelkolom. Het laten staan van de nullable externe sleutel in de tabel leidde plotseling tot de mogelijkheid om records in te voegen met hetzelfde tijdstempel (laten we aannemen dat het een standaard tijdstempel is, ingesteld op records die niet zijn gecontroleerd / appr oved) – en de enige uitweg was om de nullable-kolom te verwijderen.

Zoals je kunt zien, is het dus een vrij specifiek geval, en zoals anderen al opmerkten, zou je meestal prima akkoord gaan met alle de NULL waarden. Het hangt echt af van de specifieke vereisten van uw model.

Naast de vele goede suggesties die al zijn ingediend, persoonlijk “Ik ben geen fan van externe sleutels, tenzij ze echt nodig zijn. Ten eerste is er de M: M-relatie waarnaar u verwijst. Plus, het aanroepen van een externe sleutel, en daardoor die tabelgegevens in uw zoekopdrachten opnemen, zorgt voor meer complexiteit en afhankelijk van tabelgrootte, tragere prestaties. Zoals anderen al hebben gezegd, kunnen nullable FK-velden niet worden ondersteund en kunnen ze problemen met de gegevensintegriteit veroorzaken.

Als u een staat definieert waarin de leerlingschool onbekend of leeg is, wordt de NULL zal deze voorwaarden niet differentiëren. (we zijn weer terug bij de gegevensintegriteit.) De suggestie van de roltabel van Tulains is elegant en staat nulwaarden netjes toe.