Nevýhody použití cizího klíče s povolenou hodnotou Null místo vytváření průnikové tabulky

Tyto dva modely představují různé vztahy.

Pomocí tabulky spojení , modelujete vztah mnoho ku mnoha.

Použitím jednoduchého cizího klíče modelujete vztah jeden ku mnoha.

Nevýhoda cizí hodnoty s možnou hodnotou null klíč je neschopný modelovat vztah jako mnoho na mnoho, pokud se toho snažíte dosáhnout.

Na základě vaší úpravy otázky efektivně rozdělujete studentskou tabulku do dvou tabulek se stejným klíčem. Obvykle to vidím na tabulkách, které mají příliš mnoho polí, takže je někdo rozdělí na dvě, aby to bylo lépe zvládnutelné (říkám tomu dát rtěnku na prase).

Rozdělením studentského stolu vytváříte druhá tabulka volitelná, protože záznam nemusí ve druhé tabulce existovat. Což je velmi podobné poli, které není nutné nastavovat, protože může mít hodnotu null.

Pokud chcete mít vztah jedna k více, je mnohem lepší použít jednu tabulku a povolit školní ID být v tabulce studentů nulová. Není důvod vyhýbat se nulovým hodnotám v polích, a to ani pro cizí klíč. To znamená, že zahraniční vztah je volitelný: vývojáři a správci databází to jasně chápou a základní databázový stroj by určitě měl fungovat dobře.

Pokud máte obavy o připojení, nebojte se. Existuje dobře definovaná sémantika toho, jak spojení fungují s nulovými poli. Použitím jedné tabulky můžete spojit dvě tabulky místo tří.

Komentáře

• Takže pokud modeluji vztah jedna k více (s volitelnou účastí na straně: 1), měl bych použít cizí klíč navzdory skutečnosti, že může mít NULL hodnoty?
• @Tom ano, to je přesně to, jak to modelovat. I když je technicky možné použít tabulku spojení, datový model umožňuje mnoho až mnoho, takže k tomu budete potřebovat aktivační události a logiku databáze. Lepší je, když vztah omezíte tak, že nebude možné přidat nesprávná data.
• Upravil jsem svoji otázku.student_id jsem vytvořil pouze primární klíč v tabulce School_has_Student, který udržoval vztah jako jedna k mnoha. Jaké nevýhody má tato metoda při používání cizího klíče?
• @Tom Upravil jsem svou odpověď.

Napsali jste výše v komentáři:

v knize „Základy databázových systémů“ […] se píše [.. .] že je doporučeno použít křižovatkovou tabulku, pokud je ve sloupci cizího klíče mnoho hodnot NULL (například: pokud 98% zaměstnanců nezvládá správu)

Pokud je ve sloupci cizího klíče mnoho hodnot NULL, budou se vaše programy muset vypořádat s tímto většinou prázdným sloupcem pro každý zpracovaný záznam. Sloupec pravděpodobně zabírá nějaký prostor na disku i když v 98% všech případů je prázdný, dotazování na vztah znamená dotazování na tento sloupec, který vám dává větší síťový provoz, a pokud používáte ORM, který vám generuje třídy z vašich tabulek, vaše programy budou také potřebovat více místa u klienta straně, než je nutné. Pomocí inters Tabulka efektů se tomu vyhne, budou existovat pouze záznamy odkazů, kde ekvivalentní cizí klíč by jinak nebyl NULL.

Na rozdíl od toho, pokud nemáte jen několik NULL hodnot, řekněme 50% nebo více relace nejsou NULL, použití křižovatkové tabulky vám poskytne opačný efekt – více místa na disku, vyšší složitost vedoucí k většímu síťovému provozu atd.

Takže použití křižovatkové tabulky je jen formou optimalizace, která je vhodná pouze pro specifický případ, zejména v dnešní době, kdy místo na disku a paměť zlevnily a byly mnohem méně často potřebné. Všimněte si, že „Základy databázových systémů“ bylo původně napsáno před více než 20 lety (našel jsem odkaz na druhé vydání z roku 1994) a myslím, že toto doporučení tam už tehdy bylo. Před rokem 1994 byla optimalizace prostoru pravděpodobně mnohem důležitější než dnes, protože velkokapacitní úložiště bylo stále dražší a počítače a sítě byly mnohem pomalejší než dnes.

Jako vedlejší poznámku k vybíravému komentáři: výše uvedené prohlášení se jen snaží předvídat, co měl autor „Základy databázových systémů“ na mysli se svým doporučením, myslím, že učinil hrubé, obecné prohlášení platné pro většinu systémů. V některých databázích existují další možné optimalizace, jako jsou „řídké sloupce“, díky nimž je použití křižovatkové tabulky ještě více zastaralé.

Nenechte se tedy tímto doporučením mýlit. Kniha neříká upřednostňujete křižovatkové tabulky pro {0,1}:n vztahy obecně, nebo – jak jste napsali – že je to „správná cesta“. Použijte takové optimalizace, které vaše programy zkomplikují, pouze když opravdu je potřebujete.

Komentáře

• Vy ‚ hodně předpokládáte implementaci databáze, zejména s ohledem na OP, nezmiňoval ‚ konkrétní. Je ‚ více než pravděpodobné, že databáze je dostatečně chytrá jen málo místa pro řídké sloupce.
• @gardenhead: díky čemu věříte, že je to “ „?
• Skutečnost, že databáze mají jsou tu po celá desetiletí a jsou vysoce optimalizované, protože jsou kritickou součástí většiny infrastruktur.
• @gardenhead: zvuky pro mě vytváříte mnohem těžší neoprávněné předpoklady než já. Nicméně viz moje úprava.

Konceptuální model bude vypadat takto, což je velmi neortodoxní a tím méně:

Fyzický model bude vypadat takto, což je matoucí říci méně (lidé si budou myslet, že je to M: M, pokud nebudou vidět zblízka):

Můj návrh:

Pokud máte mnoho sloupců (FK nebo jiných), které se na většinu studentů nevztahují, oddělte tabulky do tabulek rolí s verzemi 1: 1. Ale není to tak, protože jsou FK, je to proto, že sloupce se na většinu řádků nevztahují.

Jinak , Nullable FK jsou běžnou součástí databáze a tabulky spojení jsou obvykle pro M: M rels.

Běžné použití verzí 1: 1 je pro tabulky rolí se sloupci, které se používají pouze v případě, že je entita určitého typu, a extrakce sloupců BLOB z hlediska výkonu nebo úložiště. Avoding hodnot null v FK není pro toto jedno běžné použití.

Kromě dalších odpovědí bych chtěl poukázat na to, že nulová hodnota cizího klíče je nejednoznačná. Znamená to:

1) Studentova škola (pokud existuje) není známa (jedná se o standardní význam „null“ – hodnota není známa)

2) Je je známo, zda student má či nemá školu, a oni žádnou nemají.

Pokud použijete standardní význam null, jak byste ve svém modelu cizího klíče představovali „student nemá školu“. V takovém případě pravděpodobně budete muset vytvořit záznam „žádná škola“ s vlastním ID ve školní tabulce. (Není ideální)

Komentáře

• Kniha “ Základy databázových systémů “ uvádí, že pro NULL, může to znamenat: 1) neznámá hodnota. 2) nedostupná nebo zadržená hodnota. 3) nepoužitelný atribut (myslím, že tento výklad znamená, že můžete zadat NULL pro cizí klíč).
• Tento ‚ je užitečný seznam, ale sémantika null (nebo jakékoli hodnoty ve skutečnosti) je definovatelná uživatelem.může znamenat cokoli, co návrhář říká, znamená to, bez omezení na tento seznam. Jde o to, jak rozlišit různé významy, když může být požadováno více než jeden (nebo dokonce neúmyslně uloženo)
• Navrhujete tedy, že bych měl místo použití cizího klíče s možnou hodnotou Null vytvořit průnikovou tabulku?
• @Tom Ano, domnívám se, že je to v tomto případě lepší
• @BradThomas – reprezentovali byste případ 2 (abyste se vyhnuli stejné nejednoznačnosti při použití křižovatkové tabulky) žádná škola) o záznam v křižovatce s NULL School_ID?

Databázové tabulky mají toto pěkná věc zvaná omezení. Je tedy velmi snadné vytvořit v průnikové tabulce, která umožňuje, aby se v tabulce objevil pouze 1 z každého studenta, ale v této tabulce je mnoho škol. Účinně vám

Teorie je hezká, ale nakonec jste Chystáte se modelovat svou databázi po otázkách, na které se ptáte.

Pokud se chcete často ptát na otázku: „kteří studenti jsou v mé škole“, opravdu chcete dotazovat celou tabulku studentů nebo mít snadná křižovatková tabulka.

V databázích: optimalizujte podle otázek, které kladete.

Tam je případ použití, kdy může mít použití třetí tabulky skutečně smysl. Příklad se může zdát čistě hypotetický, ale doufám, že dobře ilustruje můj názor. Předpokládejme, že do tabulky students přidáte více sloupců, a v určitém okamžiku se rozhodnete prosadit jedinečnost záznamů prostřednictvím složeného indexu v několika sloupcích. Je velmi pravděpodobné, že „Budu muset zahrnout také sloupec school_id a tady to začne být nepořádek. Kvůli způsobu, jakým byl SQL navržen, vkládá několik identických záznamů, kde school_id is NULL bude možné. Z technického hlediska to dává dokonalý smysl, ale je to neintuitivní a může to vést k neočekávaným výsledkům. Na druhé straně prosazování jedinečnosti na průniková tabulka je snadná.

Nedávno jsem musel modelovat takový „volitelný“ vztah, kde požadavek na omezení jedinečnosti byl způsoben sloupcem časového razítka. Ponechání cizího klíče s možnou hodnotou Null v tabulce najednou vedlo k možnost vkládání záznamů se stejným časovým razítkem (předpokládejme, že je to výchozí, nastaveno na záznamy, které nebyly auditovány / schváleny dosud) – a jediným východiskem bylo odstranit sloupec s možnou hodnotou Null.

Takže jak vidíte, je to „docela specifický případ a jak poznamenali jiní, většinou budete naprosto v pořádku se všemi hodnoty NULL. Opravdu to záleží na konkrétních požadavcích vašeho modelu.

Kromě mnoha již předložených dobrých návrhů osobně „Nejsem fanouškem cizích klíčů, pokud to nejsou skutečně nezbytné. Nejprve existuje vztah M: M, na který odkazujete. Navíc volání cizího klíče a tím stažení dat z tabulky do vašich dotazů přináší složitost a v závislosti na velikost tabulky, pomalejší výkon. Jak již řekli ostatní, nulová pole FK mohou být nepodporovaná a mohou vytvářet problémy s integritou dat.

Pokud definujete stav, kdy je studentská škola neznámá nebo prázdná, NULL nebude tyto podmínky rozlišovat. (opět se vracíme k integritě dat.) Návrh tabulky rolí od Tulains je elegantní a umožňuje čisté hodnoty null.