[SQL] Database design

[.Vii]

Hi all,

Al een vrij lange tijd werk ik met SQL. Echter was dat voornamelijk aan de data ophaal kan (SELECT) en niet het design gedeelte van SQL. Echter met de komst van maatwerk applicatie is daar verandering in gekomen en heb ik destijds wat in "elkaar geknutselt" om alles werkend te krijgen.

Hierbij heb ik een database opgezet naar des tijds het beste van mijn kunnen en de kennis die ik had. Nu de maatwerk applicatie een tijdje draait merk je toch dat je tegen wat onhandigheden in de de database aanloopt en dat het uitbreiden met nieuwe functionaliteit vooral op database niveau af en toe een geklooi is waar ik van af wil.

Nu heb ik het volgende gemaakt (Alleen design):
https://sqldbm.com/Projec...JhOkGFrngIE8md_DYjF4jNYw0

In kort samen gevat wat de database momenteel doet en de functionaliteit qua data welke het ondersteund.
Er komt een document (PDF) binnen welke in een filestore terecht komt en wordt opgeslagen met een unique ID (tabel "summon").

Dat is de hoofd tabel van de database en vrijwel elke tabel is een afgeleide van die tabel of weer daarvan een afgeleiden. Er wordt data uitgewisseld tussen drie applicaties waar ik graag wil hebben dat de benodigde data uit eindelijk in deze database terecht komt waar de "waarheid" van dat document dus in staat en wat leidend is om informatie over dat document op te vragen.

Mijn vraag
Is de opbouwe van deze database logisch, tabel namen gebruik, kolom naam gebruik. De onderlingen verbindingen met foreign keys. Zien jullie hier nog rariteiten in of zaken waarvan jullie denken als je dit nu net iets anders doet dan wordt X makkelijker later.

Daarnaast, het concept "CLUSTERD INDEX" en "UNCLUSTERD INDEX" is me ook niet helemaal duidelijk, wat is het verschil tussen de twee en in welke situatie is het handig om de 1 of de ander te gebruiken.

En als laatste veen vraag over indexxen. Vrijwel alles zal op basis van het "ID" gebeuren welke in de "summon" tabel zit, echter kan het af en toe zijn dat ik ook op basis van andere kolommen een tabel moet uitlezen, wanneer is het handig om een tweede index op een tabel te zetten?

Relevante software en hardware die ik gebruik
SQL Server 2008r2 (Ja ja ik weet het, we moeten naar een nieuwe versie ;-))

Voor iedereen die ook maar even de tijd neemt om er naar te kijken, mijn dank is groot :-)!

De pure SQL voor de gene die dat makkelijker vinden lezen:
https://pastebin.com/NuxM28PN

[Radiant]

Aangezien je toch al wil gaan upgraden...
https://docs.microsoft.co...rver?view=sql-server-2017

[Adimeuz]

Een index clustered maken betekend dat je de database zegt dat de fysieke data dicht bij elkaar opgeslagen moet worden (noem het geclustered, of gedefragmenteerd). Je kan dan ook maar één clustered index per tabel hebben. Nonclustered houd dus in dat het gefragmenteerde data (kan) zijn, er wordt dan een aparte lijst bij gehouden met de locatiegegevens van de data.

Normaliter is het dus sneller om van een tabel te lezen op basis van een clustered index, omdat je niet hoeft te zoeken op een hardeschijf (SSD's zullen dit effect wat minder of niet voelen). Schrijven kan langzamer zijn als er plaats gemaakt worden worden. Waar ga je een bottleneck verwachten, misschien kun je je keuze daarop baseren. Of gaat er helemaal geen bottleneck zijn?

Het toevoegen van een extra index heeft als voordeel dat je later sneller je data kan opvragen. Dit ten koste van een langzamere schrijf-actie en wat meer geheugen. Dit is dus een tradeoff waarin je zelf zult moeten bepalen wat handig is en wat niet. In veel situaties zal het echter geen drol uitmaken (zoek je 99% van de gevallen op de primary key en eens per maand op een ander veld? lekker boeiend..).

ps. Voor een wat specifiekere uitleg over clustered indexes, zie Martin Smith's antwoord alhier

[ Voor 8% gewijzigd door Adimeuz op 02-05-2018 12:42 ]

[Sandor_Clegane]

Wat voor problemen heb je? Laten we daar eens beginnen. Is rapporteren een draak? Is het traag?

Qua theorie kan de DB prima in elkaar zitten, derde normaalvorm e.d., maar de praktijk blijkt vaak wat weerbarstiger.

[whoami]

Adimeuz schreef op woensdag 2 mei 2018 @ 12:35:
Een index clustered maken betekend dat je de database zegt dat de fysieke data dicht bij elkaar opgeslagen moet worden (noem het geclustered, of gedefragmenteerd).

Sorry, maar dit is bull-shit.
Het is idd waar dat je slechts één clustered index per tabel kunt hebben, maar dit komt doordat de clustered-index op de 'leaf node' is. De clustered index bepaalt dus eigenlijk de opslagvolgorde van je records op fysiek niveau.
Als je de clustered index op de column 'Id' ligt bv, dan zullen de records dus daadwerkelijk in die volgorde opgeslagen worden; vandaar kan je dus slechts één clustered index hebben.

Om page-splits en re-ordering van data te vermijden, leg je dus beter een clustered index op een veld dat nooit meer wijzigt, en incrementeel oploopt (waarde van dat veld in het volgende record dus hoger dan de waarde van het vorige record).

.Vii schreef op dinsdag 1 mei 2018 @ 19:32:
Hi all,


En als laatste veen vraag over indexxen. Vrijwel alles zal op basis van het "ID" gebeuren welke in de "summon" tabel zit, echter kan het af en toe zijn dat ik ook op basis van andere kolommen een tabel moet uitlezen, wanneer is het handig om een tweede index op een tabel te zetten?

Leg een index op de kolommen waar er op gezocht of gesorteerd wordt.
Hou ook rekening met de volgorde van de kolommen binnen je index.
Bij non-clustered indexen kan je ook gebruik maken van included-columns in bepaalde gevallen. Stel dat je bv vaak zoekt op Id om de naam van iets op te halen, dan kan je een index maken op de Id column & de Name column als included column specifieren.
Bij het zoeken moet SQL Server dan enkel de index raadplegen; alle data die nodig is heeft hij dan en er moet niet terug gegaan worden naar de tabel om de bijhorende Name op te halen.

Zonder context is het trouwens moeilijk om iets zinnigs over je DB design te zeggen. Wat me wel opvalt , is dat je heel veel nullable columns hebt.

[ Voor 36% gewijzigd door whoami op 03-05-2018 12:10 ]