Puutteellisen vieraan avaimen käytön haitat leikkaustaulukon luomisen sijaan

Nämä kaksi mallia edustavat erilaisia suhteita.

Käyttämällä liittotaulukkoa , mallinnat monta moneen -suhteen.

Käyttämällä yksinkertaista ulkomaista avainta mallinnat yksi moneen -suhteen.

Nollattavan ulkomaisen haitta avain ei pysty mallintamaan suhdetta niin moneksi moneksi, jos yrität saavuttaa tämän.

Kysymykseen tekemäsi muokkauksen perusteella jaat opiskelijataulukon tehokkaasti. kahteen taulukkoon samalla avaimella. Näen tämän yleensä pöydissä, joissa on aivan liian monta kenttää, joten joku jakaa ne kahteen osaan hallittavuuden lisäämiseksi (kutsun sitä huulipunan asettamiseksi sialle).

Jakamalla opiskelijapöydän teet toinen taulukko on valinnainen, koska tietueen ei tarvitse olla toisessa taulukossa. Mikä on hyvin samanlainen kuin kenttä, jota ei tarvitse asettaa, koska se voi olla tyhjä.

Jos haluat ”suhde moniin” -suhteen, sinun on paljon parempi käyttää yhtä taulukkoa ja sallia koulun tunnus olla tyhjä opiskelijataulukossa. Ei ole mitään syytä välttää tyhjentämistä kentissä, jopa ulkomaisen avaimen kohdalla. Tämä tarkoittaa, että ulkosuhde on valinnainen: kehittäjät ja DBA: t ymmärtävät sen selvästi, ja taustalla olevan tietokantamoottorin pitäisi varmasti toimia hyvin.

Jos olet huolissasi liittymisistä, älä huoli. On hyvin määritelty semantiikka, kuinka liitokset toimivat nollakenttien kanssa. Käyttämällä yhtä taulukkoa voit liittää kaksi taulukkoa kolmen sijasta.

Kommentit

• Joten jos mallin ”yksi moniin” -suhdetta (jos valinnainen osallistuminen on: 1-puolella), minun pitäisi käyttää ulkomaista avainta huolimatta siitä, että sillä voi olla NULL -arvoja?
• @Tom kyllä, että on miten se mallinnetaan. Vaikka liittymispöydän käyttö on teknisesti mahdollista, tietomalli sallii monet monille, joten tarvitset laukaisimia ja tietokantalogiikkaa sen estämiseksi. Sinun on parempi rajoittaa suhdetta siten, että virheellisten tietojen lisääminen on mahdotonta.
• Muokkasin kysymykseeni.Tein vain student_id ensisijaisen avaimen School_has_Student -taulukkoon, joka säilytti suhde moniin. Mitä haittoja tällä menetelmällä on ulkomaisen avaimen käytöstä?
• @Tom muokatin vastaustani.

Kirjoitit yllä olevaan kommenttiin:

kirjassa ”Tietokantajärjestelmien perusteet” […] sanotaan [.. .] että on suositeltavaa käyttää leikkaustaulukkoa, jos vieraan avaimen sarakkeessa on paljon NULL-arvoja (esimerkiksi: jos 98% työntekijöistä ei hallinnoi osastoa)

Kun vieraan avaimen sarakkeessa on paljon NULL-arvoja, ohjelmiesi on käsiteltävä tämä enimmäkseen tyhjä sarake jokaiselle käsittelemälleen tietueelle. Sarake vie todennäköisesti levytilaa vaikka 98% kaikista tapauksista sen tyhjä, kysely suhde tarkoittaa kyselemistä sarakkeesta, joka antaa sinulle enemmän verkkoliikennettä, ja jos käytät ORM: ää, joka tuottaa sinulle luokkia taulukoistasi, ohjelmat tarvitsevat myös enemmän tilaa asiakkaalla Käytä välilyöntiä ection-taulukko välttää tämän, linkkitietueita tarvitaan vain silloin, kun vastaava vieras avain ei muuten olisi NULL.

Jos sitä ei ole vain muutama NULL-arvo, sanotaan 50% tai enemmän relaatiot eivät ole NULL, leikkaustaulukon käyttö antaa sinulle päinvastaisen vaikutuksen – enemmän levytilaa, suurempi monimutkaisuus, mikä lisää verkkoliikennettä jne.

Joten leikkauspöydän käyttö on vain eräänlainen optimointityökalu, järkevää vain erityistapaus ja varsinkin nykyään, jolloin levytila ja muisti halpenivat, paljon harvemmin. Huomaa, että ”Tietokantajärjestelmien perusteet” kirjoitettiin alun perin yli 20 vuotta sitten (löysin viittauksen toiseen painokseen vuodelta 1994), ja luulen, että suositus oli siellä jo tuolloin. Ennen vuotta 1994 avaruuden optimointi oli todennäköisesti paljon tärkeämpää kuin nykyään, koska massamuisti oli edelleen kalliimpaa ja tietokoneet ja verkot olivat paljon hitaampia kuin tänään.

Lisähuomautuksena valinnaiselle kommentille: Yllä oleva lausunto yrittää vain ennakoida, mitä ”Tietokantajärjestelmien perusteet” -kirjoittajalla oli mielessään suosituksensa, luulen, että hän antoi karkean, yleisen lausunnon, joka pätee useimpiin järjestelmiin. Joissakin tietokannoissa on muita mahdollisia optimointeja, kuten ”harvat sarakkeet”, jotka tekevät leikkaustaulukon käytöstä entistä vanhentuneempaa.

Joten älä saa tätä suositusta vääräksi. Kirja ei kerro haluat mieluummin leikkaustaulukot {0,1}:n -suhteille yleensä tai – kuten kirjoitit – että tämä on ”oikea tapa”. Käytä tällaisia optimointeja, jotka tekevät ohjelmistasi monimutkaisemman vasta, kun tarvitset niitä todella.

Kommentit

• Olet ’ oletamassa paljon järjestelmän käyttöönotosta tietokanta, varsinkin kun otetaan huomioon, että OP ei ’ t maininnut tiettyä. Se ’ on enemmän kuin todennäköistä, että tietokanta on tarpeeksi älykäs vain pieni määrä tilaa harvoille sarakkeille.
• @gardenhead: mikä saa sinut uskomaan, että tämä on ” enemmän kuin todennäköistä ”?
• Se, että tietokannoissa on ovat olleet olemassa vuosikymmenien ajan ja ovat erittäin optimoituja, koska ne ovat kriittinen osa suurinta osaa infrastruktuureista.
• @gardenhead: kuulostaa siltä, että olet tekemässä paljon pahoja perusteettomia oletuksia kuin minä. Katso kuitenkin muokkaukseni.

Käsitteellinen malli näyttää tältä, mikä on hyvin epätavallinen sanoen vähemmän:

Fyysinen malli näyttää tältä, mikä on hämmentävä sanoa vähemmän (ihmiset ajattelevat sen M: M, elleivät näe läheisesti):

Ehdotukseni:

Jos haluat, monet sarakkeet (FK tai muut), jotka eivät koske useimpia opiskelijoita, erota taulukot roolitaulukoiksi 1: 1-releillä. Mutta se ei johdu siitä, että ne ovat FK, se koska sarakkeet eivät koske useimpia rivejä.

Muuten , mitätöitävät FK-tiedostot ovat tietokannan normaali osa ja liitetaulukot ovat yleensä M: M-releille.

1: 1-relien yleiset käyttötavat ovat roolitaulukoille, joissa on sarakkeita, jotka ovat käytettävissä vain, jos entiteetti on tietyntyyppinen, ja puretaan BLOB-sarakkeita suorituskyvyn tai tallennuksen kannalta. Nolla-arvojen etsiminen FK: ssa ei ole yksi yleinen käyttö siihen.

Muiden vastausten lisäksi haluaisin huomauttaa, että vieraan avaimen nolla-arvo on epäselvä. Tarkoittaako se:

1) Opiskelijan koulu (jos sellainen on) on tuntematon (tämä on ”null” -standardin merkitys – arvo on tuntematon)

2) Se on tiedetään onko opiskelijalla koulu vai ei.

Jos käytät nollan vakiomerkintää, miten edustaisit ”opiskelijalla ei ole koulua” ulkomaisessa avainmallissasi. Tällöin luultavasti sinun on luotava ”ei koulua” -merkintä, jonka oma tunnus on koulupöydässä. (Ei ihanteellinen)

Kommentit

• Kirjassa ” Tietokantajärjestelmien perusteet ” mainitaan, että NULL, se voi tarkoittaa: 1) Tuntematon arvo. 2) Ei käytettävissä tai pidätetty arvo. 3) Ei määritettä (mielestäni tämä tulkinta tarkoittaa, että voit määrittää NULL vieraalle avaimelle).
• Tämä ’ on hyödyllinen luettelo, mutta nullan (tai minkä tahansa arvon todella) semantiikka on käyttäjän määriteltävissä.voi tarkoittaa mitä suunnittelija sanoo sen tarkoittavan, ei rajoitu kyseiseen luetteloon. Kysymys on siitä, miten erottaa eri merkitykset, kun useampi kuin yksi saatetaan tarvita (tai jopa tallentaa tahattomasti)
• Eli ehdotatko, että minun pitäisi luoda risteystaulukko sen sijaan, että käytettäisiin tyhjennettävää ulkomaista avainta?
• @Tom Kyllä, uskon, että tämä on parempi tässä tapauksessa
• @BradThomas – jotta vältetään sama epäselvyys käytettäessä leikkauspöytää, edustaisitko tapausta 2 (tiedetään, että opiskelijalla on ei koulua) leikkauspöydän tietueella NULL School_ID?

Tietokantataulukoissa on tämä mukava asia, jota kutsutaan rajoituksiksi. Joten se on erittäin helppo tehdä leikkauspöydässä, joka sallii vain yhden jokaisesta oppilaasta esiintyä taulukossa, mutta monet kouluista siinä taulukossa. Tehokkaasti antamalla sinulle

Teoria on mukavaa, mutta lopulta mallinnetaan tietokanta kysymyksiisi, joita kysyt.

Jos haluat kysyä usein kysymyksellä: ”mitkä oppilaat ovat koulussa”, haluatko todella kysyä koko opiskelijataulukkoa tai onko sinulla helppo leikkauspöytä.

Tietokannoissa: optimoi kysymyksiisi.

Siellä on käyttötapaus, jossa kolmannen taulukon käyttäminen voi olla järkevää. Esimerkki saattaa tuntua puhtaasti hypoteettiselta, mutta toivon, että se kuvaa hyvin asiani. Oletetaan, että olet lisännyt lisää sarakkeita students -taulukkoon ja jossain vaiheessa päätät pakottaa tietueiden ainutlaatuisuuden yhdistettyyn hakemistoon useissa sarakkeissa. On hyvin todennäköistä, että ”Minun on sisällytettävä myös school_id -sarakkeeseen, ja täällä asiat alkavat sotkeutua. SQL: n suunnittelutavan vuoksi lisätään useita identtisiä tietueita, joissa school_id on NULL on mahdollista. Se on teknisestä näkökulmasta täysin järkevää, mutta se on järkevää ja voi johtaa odottamattomiin tuloksiin. Toisaalta yksilöllisyyden toteuttaminen leikkaustaulukko on helppoa.

Minun piti mallintaa tällainen ”valinnainen” suhde äskettäin, jossa vaatimus ainutlaatuisuusrajoituksesta johtui aikaleimasarakkeesta. Nollattavan vieraan avaimen jättäminen taulukkoon johtaa yhtäkkiä mahdollisuus lisätä tietueita samalla aikaleimalla (oletetaan, että se on oletusarvo, asetettu tietueille, joita ei ole tarkastettu / hyväksytty oved yet) – ja ainoa tie ulos oli poistaa mitätöitävä sarake.

Joten kuten näette, se on melko spesifinen tapaus, ja kuten muutkin huomauttivat, useimmiten olit täysin ok kaikkien kanssa NULL -arvot. Se riippuu todella mallisi erityisvaatimuksista.

Jo lähetettyjen monien hyvien ehdotusten lisäksi henkilökohtaisesti minä ”En ole ulkomaisten avainten fani, elleivät ne ole todella välttämättömiä. Ensin on viittaamasi M: M-suhde. Lisäksi ulkomaisen avaimen soittaminen ja siten taulukon tietojen vetäminen kyselyihisi tuo monimutkaisuuden ja riippuu taulukon koko, hitaampi suorituskyky. Kuten muut ovat sanoneet, mitätöitäviä FK-kenttiä ei voida tukea ja ne voivat aiheuttaa tietojen eheysongelmia.

Jos määrität tilaa, jossa opiskelijakoulu on tuntematon tai tyhjä, NULL ei eroa näitä ehtoja. (jälleen palataan tietojen eheyteen.) Tulainsin roolitaulukkoehdotus on tyylikäs ja sallii tyhjien arvojen puhtaan.