Dezavantaje ale utilizării unei chei străine nulabile în loc de a crea o tabelă de intersecție

Cele două modele reprezintă relații diferite.

Prin utilizarea unui tabel de unire , modelați o relație de la mulți la mulți.

Utilizând o cheie străină simplă, modelați o relație de la mai mulți.

Dezavantajul unei străini nulabile cheia este imposibilitatea de a modela relația la fel de mulți-la-mulți, dacă asta este ceea ce încercați să realizați.

Pe baza modificării la întrebare, împărțiți în mod eficient tabelul elevului în două tabele cu aceeași cheie. În general, văd acest lucru pe tabele care au prea multe câmpuri, așa că cineva le împarte în două pentru a fi mai ușor de gestionat (îi spun așezarea rujului pe un porc).

Prin împărțirea mesei studenților, faceți al doilea tabel este opțional, deoarece nu este necesar să existe o înregistrare în al doilea tabel. Ceea ce este foarte asemănător cu un câmp care nu trebuie setat deoarece poate fi nul.

Dacă doriți o relație de la mai mulți, este mult mai bine să folosiți un singur tabel și să permiteți ID-ul școlii. a fi nul în tabelul elevului. Nu există niciun motiv pentru a evita valorile nule în câmpuri, chiar și pentru o cheie străină. Asta înseamnă că relația externă este opțională: dezvoltatorii și DBA-urile înțeleg clar acest lucru, iar motorul de bază de bază ar trebui să funcționeze bine.

Dacă sunteți îngrijorat de înscrieri, nu vă faceți griji. Există semantică bine definită pentru modul în care îmbinările funcționează cu câmpuri nule. Utilizând un singur tabel, puteți alătura două tabele în loc de trei.

Comentarii

• Deci, dacă modelez o relație unu-la-mulți (cu participare opțională pe partea: 1), ar trebui să folosesc o cheie străină în ciuda faptului că poate avea valori NULL?
• @Tom da, că este exact cum să-l modelezi. Deși este posibil din punct de vedere tehnic să se utilizeze o tabelă de asociere, modelul de date permite multor la mulți, astfel încât veți avea nevoie de declanșatoare și logică a bazei de date pentru a preveni acest lucru. Vă simțiți mai bine restricționând relația într-un mod în care este imposibil să adăugați date incorecte.
• Am editat la întrebarea mea.Am făcut din student_id o cheie principală în tabelul School_has_Student, care a menținut relația ca unul-la-mulți. Ce dezavantaje are această metodă în ceea ce privește utilizarea unei chei străine?
• @Tom Am editat răspunsul meu.

Ați scris într-un comentariu de mai sus:

cartea „Fundamentals of Database Systems” […] spune [.. .] că se recomandă utilizarea unui tabel de intersecție dacă există o mulțime de valori NULL în coloana cu cheie străină (de exemplu: dacă 98% dintre angajați nu gestionează un departament)

Când există o mulțime de valori NULL în coloana cu cheie străină, programele dvs. vor trebui să se ocupe de această coloană în cea mai mare parte goală pentru fiecare înregistrare pe care o procesează. Coloana va ocupa probabil un spațiu pe disc chiar dacă în 98% din toate cazurile este gol, interogarea relației înseamnă interogarea acelei coloane care vă oferă mai mult trafic de rețea și dacă utilizați un ORM care vă generează clase din tabelele dvs., programele dvs. vor avea nevoie și de mai mult spațiu la client lateral decât este necesar. Folosind un inters tabelul de ectiune evită acest lucru, vor exista numai înregistrări de legături necesare acolo unde cheia externă echivalentă nu ar fi NULL altfel.

Opus, dacă nu aveți doar câteva valori NULL, să spunem 50% sau mai mult relațiile nu sunt NULL, utilizarea unui tabel de intersecție vă oferă efectul opus – mai mult spațiu pe disc, complexitate mai mare, rezultând mai mult trafic de rețea etc.

Deci, utilizarea unui tabel de intersecție este doar o formă de optimizare, numai sensibilă pentru un caz specific, și mai ales în zilele noastre, în care spațiul pe disc și memoria au devenit mai ieftine, mult mai puțin frecvent necesare. Rețineți că „Fundamentele sistemelor de baze de date” a fost scris inițial acum mai bine de 20 de ani (am găsit o referință la a doua ediție din 1994) și cred că recomandarea respectivă era deja acolo în acel moment. Înainte de 1994, optimizarea spațiului era probabil mult mai importantă decât astăzi, deoarece stocarea în masă era încă mai scumpă, iar computerele și rețelele erau mult mai lente decât astăzi.

Ca o notă laterală la un comentariu pretențios: afirmația de mai sus încearcă doar să anticipeze ceea ce autorul „Fundamentele sistemelor de baze de date” a avut în vedere cu recomandarea sa, cred că făcea o afirmație generală dură, valabilă pentru majoritatea sistemelor. În unele baze de date există alte posibile optimizări, cum ar fi „coloane rare”, care fac utilizarea unei tabele de intersecție și mai învechită.

Deci, nu greșiți această recomandare. Cartea nu spune să preferați tabelele de intersecție pentru {0,1}:n relații în general sau – așa cum ați scris – că acesta este „modul corect”. Utilizați optimizări de genul acesta care vă vor face programele mai complicate doar atunci când chiar ai nevoie de ele.

Comentarii

• ‘ îți asumi multe despre implementarea programului baza de date, mai ales având în vedere că OP nu ‘ nu menționează una specifică. Este ‘ mai probabil că baza de date este suficient de inteligentă pentru a o utiliza doar o cantitate mică de spațiu pentru coloanele rare.
• @gardenhead: ce vă face să credeți că acest lucru este ” mai mult decât probabil „?
• Faptul că bazele de date au există de zeci de ani și sunt extrem de optimizate, deoarece sunt o componentă critică a majorității infrastructurilor.
• @gardenhead: mi se pare că faci presupuneri nejustificate mult mai grele decât mine. Cu toate acestea, vedeți editarea mea.

Modelul conceptual va arăta astfel, care este foarte neortodox pentru a spune cu atât mai puțin:

Modelul fizic va arăta astfel, ceea ce este confuz pentru a spune cu atât mai puțin (oamenii vor crede că este” M: M dacă nu văd cu atenție):

Sugestia mea:

Dacă aveți multe coloane similare (FK sau altele), care nu se aplică majorității studenților, separați tabelele în tabele de roluri cu relee 1: 1. Dar asta nu este pentru că sunt FK, este deoarece coloanele nu se aplică majorității rândurilor.

În caz contrar , FK nulabile sunt o parte normală a unei baze de date, iar tabelele de asociere sunt de obicei pentru relațiile M: M.

Utilizările obișnuite ale relațiilor 1: 1 sunt pentru tabelele de roluri care au coloane care se aplică numai dacă entitatea este de un anumit tip și extragerea coloanelor BLOB pentru considerente de performanță sau stocare. Evitarea valorilor nule în FK-uri nu este o utilizare obișnuită pentru asta.

Pe lângă alte răspunsuri, aș dori să subliniez că o valoare nulă pentru cheia externă este ambiguă. Înseamnă:

1) Școala elevului (dacă există) este necunoscută (acesta este sensul standard al „nul” – valoarea este necunoscută)

2) Este știu dacă elevul are sau nu școală și nu au niciunul

Dacă utilizați sensul standard de nul, cum ați reprezenta „elevul nu are școală” în modelul dvs. de cheie străină. În acest caz, probabil că ar trebui să creați o intrare „fără școală”, cu propriul id în tabelul școlii. (Nu este ideal)

Comentarii

• Cartea ” Fundamentals of Database Systems ” menționează că există 3 interpretări pentru NULL, poate însemna: 1) valoare necunoscută. 2) valoare indisponibilă sau reținută. 3) atribut neaplicabil (cred că această interpretare înseamnă că puteți specifica un NULL pentru o cheie străină).
• Această ‘ este o listă utilă, dar semantica nulă (sau orice valoare într-adevăr) poate fi definită de utilizator.poate însemna orice înseamnă proiectantul, nu se limitează la lista respectivă. Problema este cum să distingem semnificații diferite atunci când ar putea fi necesare mai multe (sau chiar salvate neintenționat)
• Deci, sugerați că ar trebui să creez o tabelă de intersecție în loc să folosesc o cheie externă nulă?
• @Tom Da, cred că este mai bine în acest caz
• @BradThomas – pentru a evita aceeași ambiguitate atunci când utilizați un tabel de intersecție, ați reprezenta cazul 2 (se știe că elevul are fără școală) printr-o înregistrare în tabelul de intersecție cu un NULL School_ID?

Tabelele bazei de date au acest lucru un lucru frumos numit constrângeri. Deci, este foarte ușor de realizat în tabelul de intersecție, care permite doar 1 din fiecare elev să apară în tabel, dar multe școli din tabelul respectiv. Efectiv, oferindu-ți o

Teorie este plăcută, dar la final ești o să vă modelez baza de date după întrebările pe care le puneți.

Dacă doriți să întrebați adesea cu întrebarea: „ce elevi sunt în școala mea”, doriți cu adevărat să interogați întregul tabel al elevilor sau să aveți un tabel de intersecție ușor.

În baze de date: optimizați pentru întrebările pe care le puneți.

Acolo este un caz de utilizare în care utilizarea unui al treilea tabel poate avea sens. Exemplul poate părea pur ipotetic, dar sper că ilustrează bine punctul meu. Să presupunem că adăugați mai multe coloane la tabelul students și, la un moment dat, decideți să impuneți unicitatea înregistrărilor prin intermediul indexului compozit pe mai multe coloane. Este foarte probabil ca dvs. „Va trebui să includă și coloana school_id și aici lucrurile încep să se încurce. Datorită modului în care a fost conceput SQL, inserând mai multe înregistrări identice în care school_id este NULL va fi posibil. Are un sens perfect dintr-o perspectivă tehnică, dar este contraintuitiv și poate duce la rezultate neașteptate. Pe de altă parte, aplicarea unicității pe tabelul de intersecție este ușor.

A trebuit să modelez recent o astfel de relație „opțională”, în care cerința pentru o constrângere de unicitate se datora unei coloane de marcare temporală. Lăsarea cheii externe nulabile în tabel duce brusc la posibilitatea de a insera înregistrări cu aceeași marcă de timp (să presupunem că este una implicită, setată pe înregistrări care nu au fost auditate / aprobate iubit încă) – și singura cale de ieșire a fost eliminarea coloanei care poate fi anulată.

Deci, după cum puteți vedea, „este un caz destul de specific și, așa cum au remarcat alții, de cele mai multe ori ați fi perfect ok cu toate valorile NULL. Depinde într-adevăr de cerințele specifice ale modelului dvs.

Pe lângă numeroasele sugestii bune deja trimise, personal am „Nu sunt un fan al cheilor străine decât dacă sunt cu adevărat necesare. Mai întâi există relația M: M la care faceți referință. În plus, apelarea unei chei străine și, prin urmare, extragerea datelor din tabel în interogările dvs., introduce mai multă complexitate și în funcție de dimensiunea mesei, performanță mai lentă. După cum au spus alții, câmpurile FK nulabile pot fi neacceptate și pot crea probleme de integritate a datelor.

Dacă definiți o stare în care școala elevilor este necunoscută sau goală, NULL nu va diferenția aceste condiții. (din nou „revenim la integritatea datelor.) Sugestia de tabel de roluri de către Tulains este elegantă și permite valorile nule în mod curat.