Säg att jag har följande ER-diagram:
Nu om jag representerade förhållandet med en främmande nyckel på School i Student skulle jag kunna ha NULL värden (eftersom en Student krävs inte för att tillhöra en School), till exempel:
Så det rätta sättet (baserat på vad jag har läst) är att skapa en skärningstabell för att representera förhållandet, till exempel:
Detta sätt, ingen NULL värden kan finnas i tabellen School_has_Student.
Men vad är nackdelarna med att använda en ogiltig främmande nyckel istället för att skapa en korsningstabell?
Redigera:
Jag valde felaktigt (school_id, student_id) att vara den primära nyckeln för School_has_Student tabell, vilket gjorde förhållandet många-till-många. Rätt primärnyckel borde ha varit student_id:
Kommentarer
- Det ’ s är inget ” rätt ” sätt. Det ’ är precis det sätt som är bäst för dina behov.
- Jag håller med Doc om den falska förutsättningen, men kanske är det ’ är fortfarande tillräckligt tydliga för att svara?
- Det finns en falsk förutsättning, men det är lätt nog att räta ut och förklara skillnaden.
- Jag drog tillbaka min nära röst men meningen ” Så rätt sätt (baserat på vad jag har läst) är att skapa en skärningstabell för att representera förhållandet ” ger mig intrycket att du borde berätta vilken strängkälla som sa att detta är ” korrekt ”. I varje textbok jag har läst tidigare är det kanoniska sättet för 1: n-relationer en enda främmande nyckel. Eller missförstod du något?
- @Doc Brown Jag ’ t kommer ihåg var jag har läst det, men jag är säker på att det står att en korsningstabell var på rätt sätt. Hur som helst, kan du ge mig namnet på en bok som säger att ett förhållande 1: n (med valfritt deltagande på: 1-sidan) ska representeras med en enda främmande nyckel, jag är intresserad av att läsa vad de säger om detta ämne.
Svar
De två modellerna representerar olika förhållanden.
Genom att använda en kopplingstabell , modellerar du en många-till-många-relation.
Genom att använda en enkel främmande nyckel modellerar du en en-till-många-relation.
Nackdelen med en ogiltig främmande nyckeln är att inte kunna modellera relationen så många-till-många, om det är det du försöker åstadkomma.
Baserat på din redigering av frågan delar du effektivt upp studenttabellen i två tabeller med samma nyckel. Jag ser det i allmänhet på bord som har alltför många fält, så någon delar upp dem i två för att bli mer hanterbara (jag kallar det att sätta läppstift på en gris).
Genom att dela upp studentbordet gör du den andra tabellen valfri eftersom en post inte behöver finnas i den andra tabellen. Vilket är mycket likt ett fält som inte behöver ställas in eftersom det kan vara null.
Om du vill ha en-till-många-relation, är du mycket bättre med att använda en enda tabell och tillåta skol-ID att vara noll i studenttabellen. Det finns ingen anledning att undvika nollor i fält, inte ens för en främmande nyckel. Det betyder att det utländska förhållandet är frivilligt: utvecklare och DBA: er förstår det tydligt, och den underliggande databasmotorn borde verkligen fungera bra.
Om du är orolig för att gå med, oroa dig inte. Det finns väldefinierade semantik för hur sammanfogningar fungerar med nollfält. Genom att använda en enda tabell kan du gå med i två tabeller istället för tre.
Kommentarer
- Så om jag modellerar ett förhållande mellan en och många (med valfritt deltagande på: 1-sidan), skulle jag använda en främmande nyckel trots att den kan ha
NULL-värden? - @Tom ja, det är exakt hur man modellerar det. Även om det är tekniskt möjligt att använda en kopplingstabell, tillåter datamodellen många till många så att du behöver utlösare och databaslogik för att förhindra det. Du har det bättre genom att begränsa förhållandet så att det är omöjligt att lägga till felaktiga data.
- Jag redigerade till min fråga.Jag gjorde
student_idtill en primär nyckel iSchool_has_Student-tabellen, som behöll förhållandet som en-till-många. Vilka nackdelar har den här metoden med att använda en främmande nyckel? - @Tom Jag redigerade mitt svar.
Svar
Du skrev i en kommentar ovan:
boken ”Fundamentals of Database Systems” […] säger [.. .] att det rekommenderas att använda en korsningstabell om det finns många NULL-värden i kolumnen för främmande nycklar (till exempel: om 98% av de anställda inte hanterar en avdelning)
När det finns många NULL-värden i den främmande nyckelkolumnen, kommer dina program att hantera den här mest tomma kolumnen för varje post som de bearbetar. Kolumnen kommer antagligen att ta upp lite diskutrymme även om det i 98% av alla fall är tomt, att fråga förhållandet betyder att fråga den kolumnen som ger dig mer nätverkstrafik, och om du använder en ORM som genererar dig klasser från dina tabeller behöver dina program också mer utrymme hos klienten sida än nödvändigt. Använda en korsning ection-tabellen undviker detta, det kommer bara att vara länkposter nödvändiga där motsvarande främmande nyckel annars inte skulle vara NULL.
Motsatt det, om du inte bara har några NULL-värden, kan vi säga 50% eller mer relationer är inte NULL, med en skärningstabell får du motsatt effekt – mer diskutrymme, högre komplexitet vilket resulterar i mer nätverkstrafik etc.
Att använda en korsningstabell är bara en form av optimering, bara förnuftigt för ett specifikt fall, och särskilt nuförtiden, där diskutrymme och minne blev billigare, mycket mindre ofta behövs. Observera att ”Fundamentals of Database Systems” ursprungligen skrevs för mer än 20 år sedan (jag hittade en hänvisning till den andra upplagan från 1994), och jag antar att den rekommendationen redan fanns där vid den tiden. Före 1994 var utrymmesoptimering förmodligen mycket viktigare än idag, eftersom masslagring fortfarande var dyrare och datorer och nätverk var mycket långsammare än idag.
Som en sidoanteckning till en kräsen kommentar: ovanstående uttalande försöker bara förutse vad författaren till ”Fundamentals of Database Systems” hade i åtanke med sin rekommendation, jag antar att han gjorde ett grovt, allmänt uttalande, giltigt för de flesta system. I vissa databaser finns andra möjliga optimeringar som ”glesa kolumner” som gör användningen av en skärningstabell ännu mer föråldrad.
Så får inte den rekommendationen fel. Boken berättar inte du föredrar korsningstabeller för {0,1}:n -förhållanden i allmänhet, eller – som du skrev – att detta är ”rätt sätt”. Använd optimeringar som detta som gör dina program mer komplicerade endast när du behöver dem verkligen.
Kommentarer
- Du ’ antar mycket om implementeringen av databas, särskilt med tanke på att OP inte ’ nämnde en specifik. Det ’ är mer än troligt att databasen är tillräckligt smart för att använda bara lite utrymme för glesa kolumner.
- @ gardenhead: vad får dig att tro att detta är ” mer än troligt ”?
- Det faktum att databaser har funnits i årtionden och är mycket optimerade eftersom de är en kritisk komponent i de flesta infrastrukturer.
- @ gardenhead: låter för mig att du gör mycket mer omotiverade antaganden än jag. Ändå se min redigering.
Svar
Konceptuell modell kommer att se ut så här, vilket är väldigt ovanligt för att säga mindre:
Fysisk modell kommer att se ut så här, vilket är förvirrande för att säga mindre (folk tror att det är M: M om de inte ser noga):
Mitt förslag:
Om du har liknande, många kolumner (FK eller annat), som inte gäller för de flesta elever, ska du dela upp tabellerna i rolltabeller med 1: 1 rels. Men det är inte för att de är FK, det beror på att kolumnerna inte gäller för de flesta rader.
Annars , ogiltiga FK är en normal del av en databas och anslutningstabeller är vanligtvis för M: M rels.
Vanliga användningsområden för 1: 1-rels är för rolltabeller med kolumner som endast gäller om enheten är av en viss typ, och extraherar BLOB-kolumner för prestanda- eller lagringsöverväganden. Avoding null-värden i FK: er är inte en vanlig användning för det.
Svar
Förutom andra svar vill jag påpeka att ett nullvärde för den främmande nyckeln är tvetydigt. Betyder det:
1) Studentens skola (om någon) är okänd (detta är standardbetydelsen för ”null” – värdet är okänt)
2) Det är känt om eleven har en skola eller inte, och de har ingen
Om du använder standardbetydelsen null, hur skulle du representera ”student har ingen skola” i din utländska nyckelmodell? du måste antagligen skapa en ”ingen skola” -post med sitt eget id i skoltabellen. (Inte idealiskt)
Kommentarer
- Boken ” Fundamentals of Database Systems ” nämner att det finns 3 tolkningar för
NULL, det kan betyda: 1) Okänt värde. 2) Ej tillgängligt eller undanhållet värde. 3) Ej tillämpligt attribut (jag tror att denna tolkning innebär att du kan ange ettNULLför en utländsk nyckel). - Att ’ är en användbar lista men semantiken för null (eller vilket värde som helst) är användardefinierbar.kan betyda vad designern säger det betyder, inte begränsat till den listan. Frågan är hur man skiljer olika betydelser när mer än en kan behövas (eller till och med sparas oavsiktligt)
- Så föreslår du att jag ska skapa en korsningstabell istället för att använda en ogiltig främmande nyckel?
- @Tom Ja, jag tror att det är bättre i det här fallet
- @BradThomas – för att undvika samma tvetydighet när du använder en korsningstabell, skulle du representera fall 2 (det är känt att studenten har ingen skola) av en post i korsningstabellen med ett NULL School_ID?
Svar
Databastabeller har detta trevlig sak som kallas begränsningar. Så det är väldigt enkelt att göra i korsningstabellen som gör att endast en av varje elev kan visas i tabellen men många skolor i den tabellen. Att ge dig en
Teori är bra men i slutändan är du ska modellera din databas efter de frågor du ställer.
Om du ofta vill fråga med frågan: ”vilka elever som finns i min skola” vill du verkligen fråga hela studenttabellen eller ha en enkel korsningstabell.
I databaser: optimera för de frågor du ställer.
Svar
Där är ett användningsfall där det faktiskt kan vara meningsfullt att använda en tredje tabell. Exemplet kan verka rent hypotetiskt, men jag hoppas att det illustrerar min poäng väl. Låt oss anta att du lägger till fler kolumner i students -tabellen och vid något tillfälle bestämmer du dig för att upprätthålla unikhet i posterna via sammansatt index i flera kolumner. Det är mycket troligt att du ”Jag måste inkludera kolumnen school_id, och här börjar saker och ting bli röriga. På grund av hur SQL designades, infogade du flera identiska poster där school_id är NULL kommer att vara möjligt. Det är helt vettigt ur ett tekniskt perspektiv, men är kontraintuitivt och kan leda till oväntade resultat. Å andra sidan verkställer det unika på korsningstabellen är lätt.
Jag var tvungen att modellera ett sådant ”valfritt” förhållande nyligen, där kravet på en unik begränsning berodde på en tidsstämpelkolumn. Att lämna den ogiltiga främmande nyckeln i tabellen ledde plötsligt till möjlighet att infoga poster med samma tidsstämpel (låt oss anta att det är en standard, inställt på poster som inte har granskats / appr oved ännu) – och den enda vägen ut var att ta bort ogiltig kolumn.
Så som du kan se är det ett ganska specifikt fall, och som andra noterade, skulle du oftast vara helt ok med alla NULL värdena. Det beror verkligen på de specifika kraven i din modell.
Svar
Förutom de många bra förslag som redan har lämnats in personligen ”Jag är inte ett fan av främmande nycklar såvida de inte verkligen är nödvändiga. Först finns det M: M-förhållandet som du refererar till. Dessutom ringer du en främmande nyckel och därmed drar in tabelldata i dina frågor, inför mer komplexitet och beroende på tabellstorlek, långsammare prestanda. Som andra har sagt kan ogiltiga FK-fält inte stödjas och kan skapa dataintegritetsproblem.
Om du definierar ett tillstånd där studentskolan är okänd eller tom, är NULL kommer inte att skilja på dessa villkor. (återigen återgår vi till dataintegritet.) Rolltabellförslaget från Tulains är elegant och möjliggör rena nullvärden.