Si at jeg har følgende ER-diagram:
Nå hvis jeg representerte forholdet ved hjelp av en fremmed nøkkel på School i Student, kunne jeg ha NULL verdier (fordi en Student er ikke nødvendig for å tilhøre en School), for eksempel:
Så den riktige måten (basert på det jeg har lest) er å lage en skjæringstabell for å representere forholdet, for eksempel:
Denne måten, ingen NULL verdier kan være tilstede i tabellen School_has_Student.
Men hva er ulemper ved å bruke en nullbar fremmed nøkkel i stedet for å lage en krysningstabell?
Rediger:
Jeg valgte feilaktig (school_id, student_id) å være den primære nøkkelen til School_has_Student tabell, som gjorde forholdet mange-til-mange. Riktig primærnøkkel burde vært student_id:
Kommentarer
- Der ‘ s er ingen » riktig » måte. Der ‘ er akkurat den måten som er best for dine behov.
- Jeg er enig med Doc om den falske forutsetningen, men kanskje det ‘ er fortsatt klare nok til å svare?
- Det er en falsk forutsetning, men det er lett nok å rette opp og forklare forskjellen.
- Jeg trakk min nære stemme tilbake , men setningen » Så den riktige måten (basert på det jeg har lest) er å lage en skjæringstabell for å representere forholdet » gir meg inntrykk av at du burde fortelle oss hvilken strekkilde som har fortalt deg at dette er » riktig » måte. I hver lærebok jeg har lest før, er den kanoniske måten for 1: n-forhold en enkelt fremmed nøkkel. Eller misforsto du noe?
- @Doc Brown Jeg ‘ husker ikke hvor jeg har lest det, men jeg er sikker på at det står at det var et skjæringsbord riktig måte. Uansett, kan du gi meg navnet på en bok som sier at et 1: n-forhold (med valgfri deltakelse på: 1-siden) skal representeres ved hjelp av en enkelt fremmed nøkkel, jeg er interessert i å lese hva de sier om dette emnet.
Svar
De to modellene representerer forskjellige forhold.
Ved å bruke en sammenføyningstabell , modellerer du et mange-til-mange-forhold.
Ved å bruke en enkel utenlandsk nøkkel, modellerer du et en-til-mange-forhold.
Ulempen med en ugyldig fremmed nøkkelen er å være i stand til å modellere forholdet så mange-til-mange, hvis det er det du prøver å oppnå.
Basert på redigeringen av spørsmålet, deler du effektivt elevtabellen i to tabeller med samme nøkkel. Jeg ser dette generelt på bord som har altfor mange felt, så noen deler dem i to for å være mer håndterbare (jeg kaller det å sette leppestift på en gris).
Ved å dele studentbordet lager du den andre tabellen valgfri fordi en post ikke trenger å eksistere i den andre tabellen. Som ligner veldig på et felt som ikke trenger å bli satt fordi det kan være null.
Hvis du vil ha et forhold mellom mange, har du det bedre å bruke en enkelt tabell og tillate skole-ID å være null i studenttabellen. Det er ingen grunn til å unngå null i felt, selv for en utenlandsk nøkkel. Det betyr at det utenlandske forholdet er valgfritt: utviklere og DBA-er forstår det tydelig, og den underliggende databasemotoren burde absolutt fungere bra.
Hvis du er bekymret for å bli med, ikke bekymre deg. Det er veldefinert semantikk for hvordan sammenføyninger fungerer med nullfelt. Ved å bruke en enkelt tabell kan du bli med i to tabeller i stedet for tre.
Kommentarer
- Så hvis jeg modellerer et forhold mellom mange og mange (med valgfri deltakelse på: 1-siden), bør jeg bruke en fremmed nøkkel til tross for at den kan ha
NULLverdier? - @Tom ja, det er akkurat hvordan man skal modellere det. Selv om det er teknisk mulig å bruke en sammenføyningstabell, tillater datamodellen mange for mange, så du trenger utløsere og databaselogikk for å forhindre det. Du har det bedre ved å begrense forholdet slik at det er umulig å legge til feil data.
- Jeg redigerte på spørsmålet mitt.Jeg gjorde bare
student_idtil en primærnøkkel iSchool_has_Student-tabellen, som holdt forholdet som en-til-mange. Hvilke ulemper har denne metoden med å bruke en fremmed nøkkel? - @Tom Jeg redigerte svaret mitt.
Svar
Du skrev i en kommentar ovenfor:
boken «Fundamentals of Database Systems» […] sier [.. .] at det anbefales å bruke en krysningstabell hvis det er mange NULL-verdier i den utenlandske nøkkelkolonnen (for eksempel: hvis 98% av de ansatte ikke leder en avdeling)
Når det er mange NULL-verdier i kolonnen for utenlandsk nøkkel, må programmene dine håndtere denne for det meste tomme kolonnen for hver eneste post de behandler. Kolonnen vil sannsynligvis oppta noe diskplass selv om det i 98% av alle tilfeller er tomt, spørre forholdet om å spørre den kolonnen som gir deg mer nettverkstrafikk, og hvis du bruker en ORM som genererer deg klasser fra tabellene dine, vil programmene dine også trenge mer plass hos klienten side enn nødvendig. Bruke en inters ection tabell unngår dette, det vil bare være koblingsoppføringer som er nødvendige der den tilsvarende utenlandske nøkkelen ikke ville være NULL ellers.
Motsatt det, hvis du ikke bare har noen få NULL-verdier, kan vi si 50% eller mer forhold er ikke NULL, ved å bruke en kryssingstabell får du motsatt effekt – mer diskplass, høyere kompleksitet som resulterer i mer nettverkstrafikk osv.
Så å bruke en krysningstabell er bare en form for optimalisering, bare fornuftig for et bestemt tilfelle, og spesielt i dag, hvor diskplass og minne ble billigere, mye sjeldnere nødvendig. Merk at «Fundamentals of Database Systems» ble opprinnelig skrevet for mer enn 20 år siden (jeg fant en referanse til den andre utgaven fra 1994), og jeg antar at den anbefalingen allerede var der inne på den tiden. Før 1994 var trolig plassoptimalisering mye viktigere enn i dag, siden masselagring fortsatt var dyrere og datamaskiner og nettverk var mye tregere enn i dag.
Som en sidemerknad til en kresen kommentar: ovennevnte uttalelse prøver bare å forutse hva forfatteren av «Fundamentals of Database Systems» hadde i tankene med sin anbefaling, antar han at han kom med en grov, generell uttalelse, gyldig for de fleste systemer. I noen databaser er det andre mulige optimaliseringer som «sparsomme kolonner» som gjør bruken av en skjæringstabell enda mer foreldet.
Så ikke gi den anbefalingen feil. Boken forteller ikke du foretrekker kryssingstabeller for {0,1}:n -forhold generelt, eller – som du skrev – at dette er «riktig måte». Bruk optimaliseringer som dette som vil gjøre programmene dine mer kompliserte bare når du trenger dem virkelig.
Kommentarer
- Du ‘ antar mye om implementeringen av database, spesielt med tanke på at OP ikke ‘ ikke nevnte en spesifikk. Det ‘ er mer enn sannsynlig at databasen er smart nok til å bruke bare en liten mengde plass til sparsomme kolonner.
- @ gardenhead: hva får deg til å tro at dette er » mer enn sannsynlig «?
- Det faktum at databaser har har eksistert i flere tiår og er svært optimalisert, siden de er en kritisk komponent i de fleste infrastrukturer.
- @ gardenhead: lyder for meg at du gjør mye uberettigede antakelser enn meg. Likevel, se min redigering.
Svar
Konseptuell modell vil se slik ut, som er veldig uortodoks for å si det mindre:
Fysisk modell vil se slik ut, som er forvirrende for å si det mindre (folk vil tro det er M: M med mindre de ser nøye):
Mitt forslag:
Hvis du har like mange kolonner (FK eller annet) som ikke gjelder for de fleste studenter, skiller du tabellene inn i rolletabeller med 1: 1-ruller. Men det er ikke fordi de er FK, det er fordi kolonnene ikke gjelder for de fleste rader.
Ellers , nullbare FK er en normal del av en database, og sammenføyningstabeller er vanligvis for M: M rels.
Vanlige bruksområder for 1: 1-reller er for rolletabeller som har kolonner som bare gjelder hvis enheten er av en bestemt type, og trekker ut BLOB-kolonner av hensyn til ytelse eller lagring. Avoding nullverdier i FK er ikke en vanlig bruk for det.
Svar
I tillegg til andre svar vil jeg påpeke at en nullverdi for den utenlandske nøkkelen er tvetydig. Betyr det:
1) Studentens skole (hvis noen) er ukjent (dette er standardbetydningen av «null» – verdien er ukjent)
2) Det er kjent om eleven har en skole eller ikke, og de har ingen
Hvis du bruker standardbetydningen null, hvordan vil du representere «student har ingen skole» i din utenlandske nøkkelmodell? du må sannsynligvis opprette en «ingen skole» -oppføring, med sin egen id i skoletabellen. (Ikke ideell)
Kommentarer
- Boken » Fundamentals of Database Systems » nevner at det er 3 tolkninger for
NULL, det kan bety: 1) Ukjent verdi. 2) Ikke tilgjengelig eller tilbakeholdt verdi. 3) Ikke aktuelt attributt (jeg tror denne tolkningen betyr at du kan spesifisere enNULLfor en fremmed nøkkel). - At ‘ er en nyttig liste, men semantikken til null (eller en hvilken som helst verdi virkelig) kan defineres av brukeren. Det vil sikan bety hva designeren sier det betyr, ikke begrenset til den listen. Problemet er hvordan man skiller mellom forskjellige betydninger når mer enn en kan være nødvendig (eller til og med lagret utilsiktet)
- Så foreslår du at jeg skal lage en skjæringstabell i stedet for å bruke en null fremmed nøkkel?
- @Tom Ja, jeg tror det er bedre i dette tilfellet
- @BradThomas – for å unngå den samme tvetydigheten når du bruker en skjæringstabell, vil du representere sak 2 (det er kjent at studenten har ingen skole) av en post i krysningstabellen med en NULL School_ID?
Svar
Databasetabeller har dette fin ting som kalles begrensninger. Så det er veldig enkelt å lage i kryssetabellen som bare tillater 1 av hver elev å vises i tabellen, men mange skoler i den tabellen. Effektivt å gi deg en
Teori er fin, men til slutt er du skal modellere databasen din etter spørsmålene du stiller.
Hvis du ofte vil stille spørsmål med spørsmålet: «hvilke elever som er på skolen min», vil du virkelig spørre hele studenttabellen eller ha en enkel krysningstabell.
I databaser: optimaliser for spørsmålene du stiller.
Svar
Der er et brukstilfelle der bruk av en tredje tabell faktisk kan være fornuftig. Eksemplet kan virke rent hypotetisk, men jeg håper det illustrerer poenget mitt godt. La oss anta at du legger til flere kolonner i students -tabellen, og på et eller annet tidspunkt bestemmer du deg for å håndheve unikhet i postene via sammensatt indeks i flere kolonner. «Jeg må også inkludere school_id -kolonnen, og her begynner ting å bli rotete. På grunn av måten SQL ble designet på, satte du inn flere identiske poster der school_id er NULL vil være mulig. Det gir full mening fra et teknisk perspektiv, men er kontraintuitivt og kan føre til uventede resultater. På den annen side håndhever det unike på krysningstabellen er enkel.
Jeg måtte modellere et slikt «valgfritt» forhold nylig, hvor kravet til en unik begrensning skyldtes en tidsstempelkolonne. Å la den nullaktive fremmednøkkelen i tabellen plutselig føre til mulighet for å sette inn poster med samme tidsstempel (la oss anta at det er en standard, satt på poster som ikke har blitt revidert / ca. ennå) – og den eneste utveien var å fjerne ugyldig kolonne.
Så som du kan se, er det et ganske spesifikt tilfelle, og som andre bemerket, ville du ofte være helt ok med alle NULL verdiene. Det avhenger virkelig av de spesifikke kravene til modellen din.
Svar
I tillegg til de mange gode forslagene som allerede er sendt inn, personlig har jeg «Jeg er ikke fan av utenlandske nøkler med mindre de virkelig er nødvendige. Først er det M: M-forholdet du refererer til. Pluss å ringe til en utenlandsk nøkkel og derved trekke tabelldataene inn i spørsmålene dine, introduserer mer kompleksitet og avhengig tabellstørrelse, langsommere ytelse. Som andre har sagt, kan nullbare FK-felt ikke støttes og kan skape problemer med dataintegritet.
Hvis du definerer en tilstand der studentskolen er ukjent eller tom, er NULL vil ikke skille disse forholdene. (igjen er vi tilbake til dataintegritet.) Rolletabelforslaget fra Tulains er elegant og tillater nullverdier rent.