Goed databaseschema maken, met afhankelijke attributen

[Jantje2000]

Als je iets wilt opslaan (in een relationele database, in dit geval SQL Server), dat afhankelijkheden kent in attributen. Hoe maak je hier een net schema van?

Hiermee wordt bijvoorbeeld bedoeld dat als er één waarde wordt aangevinkt er een waardes moeten worden ingevuld, die niet ingevuld hoeven te worden als de waarde niet aangevinkt wordt. Een voorbeeld is bijvoorbeeld een terugkeerpatroon in Outlook:



Als je als type terugkeerpatroon voor "dag" wekelijks kiest, moet je kunnen kiezen op welke dagen dit terug moet komen. Als je echter voor maandelijks kiest, moet je kunnen kiezen of de afspraak bijvoorbeeld iedere 21e moet terugkomen, of dat een afspraak iedere 3e woensdag van de maand moet terugkomen.

Nou is mijn vraag: hoe maak je een databaseschema, waar dit netjes in opgeslagen wordt. Maak je gewoon een record, waar standaard 10 velden NULL zijn, of wat is hier in een best practice?

Technieken: ik maak gebruik van EF Core, .NET Core en SQL Server

[ Voor 3% gewijzigd door Jantje2000 op 22-11-2019 12:25 ]

[0xDEADBEEF]

CHECK constraints enforce domain integrity by limiting the values that are accepted by one or more columns.

[Jantje2000]

0xDEADBEEF schreef op vrijdag 22 november 2019 @ 12:24:
CHECK constraints enforce domain integrity by limiting the values that are accepted by one or more columns.

Kun je uitleggen hoe dat een oplossing is? Want voor zover ik weet kun je daarmee inderdaad extra checks toevoegen aan een kolom, maar hoe zorg ik er dan voor dat ik geen overbodige kolommen heb?

Uiteindelijk is het steeds nét verschillende data die moet worden opgeslagen, afhankelijk van het type terugkeerpatroon

[Tazzios]

website cms`en gebruiken meestal 1 parameterveld met een json file er in. Dit omdat het aantal parameters van (3rd) plugins heel verschillend is.

[DataGhost]

Tazzios schreef op vrijdag 22 november 2019 @ 12:31:
website cms`en gebruiken meestal 1 parameterveld met een json file er in. Dit omdat het aantal parameters van (3rd) plugins heel verschillend is.

Dan kan je net zo goed gewoon tekstbestanden in een directory neerzetten. Zeker met agenda-dingen en herhalingen wil je de velden direct kunnen benaderen en sorteren.

[Tazzios]

in mariadb kun je in de SQLquery direct een json extract doen en kun je ze dus ook direct sorteren e.d.

[DataGhost]

Ik wens je daar heel veel succes mee op het moment dat je een set van rijen wilt hebben met json-attr X between Y and Z uit een tabel met 10 miljoen rijen. Op het moment dat je dat soort dingen wilt gaan doen is het altijd beter om daar gewoon losse kolommen voor te maken, en als je een index op een json-attribuut wilt zetten in MariaDB moet je dat gewoon doen door er een kolom van te maken (een soort view op je json-kolom). Dus met je ene parameterveld kan je helemaal niks nuttigs, behalve inderdaad ongestructureerde json opslaan.

@TS: er zijn voor heel wat dingen nog geen "best" practices, uiteindelijk is een heleboel afhankelijk van je einddoel. Voor het opslaan van kalender-entries zijn er volgens mij behoorlijk wat oplossingen te vinden in verschillende richtingen. Wat mij betreft is er geen doodzonde aan het hebben van rijen met redelijk wat null fields als dat betekent dat je joins makkelijker/performanter worden. Dat zou bijvoorbeeld van het ophalen van "alle afspraken vandaag" kunnen schelen of je 4 queries moet unionen (dagelijks, wekelijks, maandelijks, jaarlijks) of dat alles in 1 query kan met wat meer logica. Maar dat moet je uiteindelijk gewoon benchmarken

[Marco1994]

Je zou een enum genaamd recurrenceType (of iets anders genaamd) in je recurrence tabel op kunnen nemen. In die recurrence tabel heb je relaties naar dailyRecurrence, weeklyRecurrence, monthlyRecurrence en yearlyRecurrence. Die 4 tabellen hebben alle verschillende eigenschappen. Op basis van je RecurrenceType bepaal je welke include(join) je moet doen. Dit betekent dat als je RecurrenceType Weekly is, DailyRecurrenceId, MonthlyRecurrenceId en YearlyRecurrenceId null zijn en alleen WeeklyRecurrenceId een value heeft.

Ik kan op mn telefoon lastig een voorbeeld schrijven, maar ik denk dat je met een beetje fantasie en bovenstaand verhaal een eind moet kunnen komen. Echter zijn er natuurlijk meerdere wegen naar Rome.

[Jantje2000]

@Marco1994 daar heb ik ook aan zitten denken, en tot nu toe lijkt mij dat de meest juist oplossing. Ik dacht alleen dat er hier misschien nog betere ideeën zouden kunnen komen

[incaz]

RecurrencyType als enum, en dan de patronen zelf uitwerken in de backend. Daar kun je namelijk de code een stuk leesbaarder maken, (en testbaarder!)

Dan een koppeltabel met een recurrencyReeksId (maar dan in het Engels, ik weet even niet wat de beste vertaling is) en de Type-enum.
En dan verwijs je vanuit de afspraak alleen maar naar die reeksId. Je kunt dan andere afspraken uit dezelfde reeks makkelijk vinden (ik denk dat Android het zo doet, met de vraag "wil je deze verandering toepassen op alle afspraken in de reeks") en je kunt dus het type vinden.
Je kunt je patronen dan zo ingewikkeld maken als je leuk vindt, in de backend, en het enige wat belangrijk is is dat die code vervolgens de juiste nieuwe afspraken genereert (en de juiste reeks verwijdert.) Hier zijn de tests voor.

[armageddon_2k1]

Als er geen einddatum voor herhaling gaat zijn kan je het hoe dan ook niet volledig in je DB oplossen en moet er een stuk business logic bij. Tenzij je een infinite DB hebt natuurlijk. Wat dat betreft zijn de oplossingen van incaz en Jantje2000 goede uitgangspunten.

[incaz]

Ik besef me nu trouwens dat het nog geen antwoord op de vraag is, omdat je eigenlijk vroeg hoe je dan de dag/dagen moet opslaan bij een wekelijks patroon, of dagnummers bij de maand... Sorry.
Daar moet ik nog even over nadenken want die is wel lastig. Een vrij algemene attribute-tabel kan een optie zijn, maar het wordt hoe dan ook best snel complex als je het allemaal uitwerkt.

Dus misschien is dat wel een goed begin, om in elk geval alvast eens op te schrijven als tree welke gegevens je nodig hebt per type, en hoe complex je dat wilt maken. (De laatste vrijdag van de maand, of erger nog, elke dinsdag na de tweede maandag in september elke vier jaar... pfoeh. Dan kan freeflow json inderdaad wel aantrekkelijk lijken.)

[Jantje2000]

incaz schreef op vrijdag 22 november 2019 @ 20:39:
Ik besef me nu trouwens dat het nog geen antwoord op de vraag is, omdat je eigenlijk vroeg hoe je dan de dag/dagen moet opslaan bij een wekelijks patroon, of dagnummers bij de maand... Sorry.
Daar moet ik nog even over nadenken want die is wel lastig. Een vrij algemene attribute-tabel kan een optie zijn, maar het wordt hoe dan ook best snel complex als je het allemaal uitwerkt.

Dus misschien is dat wel een goed begin, om in elk geval alvast eens op te schrijven als tree welke gegevens je nodig hebt per type, en hoe complex je dat wilt maken. (De laatste vrijdag van de maand, of erger nog, elke dinsdag na de tweede maandag in september elke vier jaar... pfoeh. Dan kan freeflow json inderdaad wel aantrekkelijk lijken.)

Ja precies.

Het gaat in deze applicatie om een terugkerende taak, en het zou in principe genoeg moeten zijn als er hetzelfde kan als in dit Outlook venster. Ik kan uiteraard gewoon veel serverside oplossen, maar het gaat me er voornamelijk om hoe ik zoiets op sla.

Gewoon heel veel kolommen, waarvan er standaard meerdere null zijn? Dat is dan het makkelijkst, maar dat is niet netjes

[incaz]

Heel veel kolommen werkt volgens mij niet fijn - handig om te schrijven maar vervolgens nogal vervelend om te gebruiken omdat je enge dingen moet doen als via de code tabellen joinen of over kolommen itereren.

Dus ik denk dat als je het door gaat werken je uitkomt op een paar eigenschappen van de recurrency, die je in de reeks-tabel kunt opslaan (naast type denk ik dat 'interval' er 1 is, waarbij die misschien nog gesplitst kan worden in waarde en eenheid.) En dat je dan een aparte attribute-tabel krijgt die grotendeels typevrij is, en waar je dan bv de dag-eigenschap stopt.

Dus je attribute-tabel ziet er dan uit als

code:

recurrencyId | attributeType | attributeValue
1 | 'day of week' | 1 #maandag en vrijdag voor recurrency1
1 | 'day of week' | 5 
2 | 'day of month' | 15 #15e dag van de maand
3 | 'day of month' | -1 # laatste dag van de maand
4 | 'month' | 1 # januari en juli voor id4
4 | 'month' | 7

Twee dingen waarbij je dan eigenlijk al een beetje op de zaken vooruit wilt lopen:
1. In het begin denk ik dat de AttributeType geheel wordt bepaald door je RecurrencyType: een weektype bepaalt dat je dagen van de week aangeeft. Maar dit is ook een mogelijkheid waar ik makkelijk zie dat je complexiteit wilt toevoegen, dus ik zou er vanaf het begin al gewoon een eigen Enum van maken.
2. de attributeValue zou ik voor de zekerheid typeloos of string maken ook al stop je er eerst vooral ints in. Parsen kan in de code op basis van je attributeType.

[Matis]

Misschien gooi ik de knuppel in het hoenderhok, maar heb je al eens verder gekeken dan SQL?
Mogelijk dat je dit veel eenvoudiger in een MongoDB collectie kunt oplossen.
Je kunt dan, per terugkeerpatroon, een aparte klasse maken (desnoods met overerving) en alle losse attributen in een document plaatsen.
Met de juiste indexering en slimme overerving is het bloedje snel.

Daarnaast in het JSON gebaseerd, iets dat in één van de eerste posts al werd voorgesteld, maar toen in Mariadb.

Kijk er eens naar en laat je overtuigen door de elegantie van MongoDB.

[sig69]

Volgens mij heb je aan een aantal kolommen zoals @incaz zegt wel genoeg. De rest los je in je applicatie op.
Je kan er ook een cron expression van bakken, sla je die op. En die parse je weer in je applicatie, en los je het daar weer op.

[Jantje2000]

@incaz goed verhaal! Ik ga daar inderdaad eens naar kijken en er zijn inderdaad kolommen, zoals interval die je noemt, die gelijk blijven.

@Matis Daar dacht ik ook aan, echter is dit onderdeel maar een klein stukje van de applicatie, dus dan vind ik een aparte database speciaal hiervoor een beetje overkill

[Matis]

Jantje2000 schreef op zaterdag 23 november 2019 @ 07:14:
@Matis Daar dacht ik ook aan, echter is dit onderdeel maar een klein stukje van de applicatie, dus dan vind ik een aparte database speciaal hiervoor een beetje overkill

Tja, dat is natuurlijk een keuze en jouw goed recht als ontwikkelaar.

In onze applicatie maken we gebruik van 3 verschillende databases: Mongo, Mariadb en Elastic. Iedere database voor zijn eigen kwaliteiten. Ook draaien we nog redis, maar dat is eigenlijk alleen maar voor de caching.

In alle reacties in dit topic proef ik dat de SQL oplossing "net niet" is wat je wilt.

Je zou het een keer kunnen proberen. Een proof of concept maken en Mongo een kans geven. Misschien val je ook wel voor de charmes van MongoDB

[ Voor 24% gewijzigd door Matis op 23-11-2019 08:03 ]

[jeanj]

Tja je kan dit op meerdere manieren modelleren in SQL en andere opties zoals mongo ken ik niet, ik ben geen professional maar dit is even mijn eerste analyse, voor dit specifieke geval

Als eerste heb je de generiek informatie van de afspraak, zoals het onderwerp. Dit plaats je allemaal in de tabel afspraak, elk veld zijn eigen kolom. Elke afspraak heeft een begin tijd en eind tijd, dus die kan je daar ook bij plaatsen. Duur is afgeleid uit die twee, en makkelijk uit te rekenen, dus tenzij er een noodzaak is zou ik die niet opslaan. Als je de optie gehele dag wil dan vul je het met 0:00 en 23:59:59. Maar er is niks tegen om er een eigen veld voor te maken, als een boolean.

De begin en eind datum, die zijn eigenlijk ook generiek. Geen eind datum is het veld leeg laten, daarmee is die optie gecoverd.
Het aantal keer kan je afhankelijk van type terugkeer patroon, en om te rekenen naar de eind datum.
Dan ben je alleen kwijt welk aantal is ingevoegd, dus je zou er voor kunnen kiezen dat in te vullen in een extra veld, dan is dat tevens de flag dat die optie is gebruikt

Dan nog het terug keer patroon, ook onderdeel van het afspraak veld, optie geen/null, als er geen patroon is. De parameters zelf zou ik een aparte tabel opnemen, en alle parameters naar een integer mappen, zo te zien is er behalve gehele getallen geen andere vrije user input

EventId | EventName | ....
1 Afspraak1

ParameterID | EventId | ParameterName | ParameterValue
1 1 Aantalweken 1
2 1 Vrijdag 1
3

En zijn natuurlijk ook andere opties

[Woy]

Je bent hier eigenlijk op zoek naar het antwoord op de vraag welke vorm van inheritance mapping je het beste toe kunt passen. Hier kun je er wat meer over lezen: https://www.entityframewo...rategy-in-code-first.aspx ( Gewoon eerste google hit die ik vond. Zoeken op Table per Type, Table per Hierarchy zal je een hoop resultaten geven )

Er is helaas niet echt 1 perfecte oplossing.

[ Voor 16% gewijzigd door Woy op 23-11-2019 10:32 ]

[incaz]

SQL Server ondersteunt JSON vind ik: MSDN: Combining relational and NoSQL concepts in SQL Server | SQL Database En... en https://docs.microsoft.co...ver?view=sql-server-ver15
En dit is wellicht een goede kans om daar mee te experimenteren. (Niets helpt vaak zo om vat te krijgen op een functionaliteit als het ook echt zinnig in kunnen zetten.)

[Lethalis]

Het is niet voor niets dat bij moderne systeemontwikkeling zoveel mogelijk in code wordt opgelost en de database grotendeels puur als implementatiedetail wordt gezien.

Kortom, los dit in de domeinlaag op met validatie en sla de attributen gewoon met een default waarde op als ze niet gevuld zijn

NULL probeer ik zelf te mijden.

FluentValidation vind ik persoonlijk mooi trouwens. En dan losse validators die je kunt unit testen.

[ Voor 31% gewijzigd door Lethalis op 23-11-2019 11:48 ]

[drm]

Als je geen praktische implicatie kunt verzinnen die je problemen oplevert, moet je helemaal niet verder gaan met moeilijk doen over je datamodel of de implementatie daarvan. Je modelleert om feitelijke problemen op te lossen, niet omdat het "goed" modelleren op zichzelf enige waarde heeft.

Dus: een zwik nullable columns is veruit de eenvoudigste oplossing en dat zou ik dus daarom gewoon doen. Elke tegenwerping zou gebaseerd moeten zijn op een praktische implicatie die reëel en verifieerbaar is. Anders ben je gewoon theoretisch aan 't muggenziften, en modelleren op de toekomstige (dus hypothetische) praktijk wordt zwaar (!) overschat. Denk liever na hoe je je voorbereid op een toekomst die nog niet vast staat (dat is namelijk de werkelijke wereld), bijvoorbeeld door een goede strategie te verzinnen wat je doet als je je datamodel aan moet passen.

[Jantje2000]

drm schreef op zondag 24 november 2019 @ 21:24:
Als je geen praktische implicatie kunt verzinnen die je problemen oplevert, moet je helemaal niet verder gaan met moeilijk doen over je datamodel of de implementatie daarvan. Je modelleert om feitelijke problemen op te lossen, niet omdat het "goed" modelleren op zichzelf enige waarde heeft.

Dus: een zwik nullable columns is veruit de eenvoudigste oplossing en dat zou ik dus daarom gewoon doen. Elke tegenwerping zou gebaseerd moeten zijn op een praktische implicatie die reëel en verifieerbaar is. Anders ben je gewoon theoretisch aan 't muggenziften, en modelleren op de toekomstige (dus hypothetische) praktijk wordt zwaar (!) overschat. Denk liever na hoe je je voorbereid op een toekomst die nog niet vast staat (dat is namelijk de werkelijke wereld), bijvoorbeeld door een goede strategie te verzinnen wat je doet als je je datamodel aan moet passen.

Goed punt. Ik doe dit inderdaad omdat het mooi en netjes moet, niet omdat het anders niet zou werken inderdaad.

Dan doe ik wel gewoon wat nullable columns inderdaad, en dat is ook nog gemakkelijker te bouwen

[Matis]

Met deze redenatie is database normalisatie ook niet meer nodig.

Ik blijf ervan overtuigd dat dit schreeuwt om een NoSQL oplossing vraagt.

[Jantje2000]

Matis schreef op maandag 25 november 2019 @ 10:00:
Met deze redenatie is database normalisatie ook niet meer nodig.

Ik blijf ervan overtuigd dat dit schreeuwt om een NoSQL oplossing vraagt.

Dat zou inderdaad een betere oplossing zijn. Ben ik helemaal met je eens. Maar ik loop hier een stageopdracht, en ze vinden een aparte database daarvoor onzin ( en niet nodig en te duur voor de extra hosting)

Dus dan is dat niet echt een oplossing. Maar als ik inderdaad niet had gezeten met de eisen hier, had ik waarschijnlijk MongoDb of Azure CosmosDb er bij gepakt ofzo.

[Woy]

Matis schreef op maandag 25 november 2019 @ 10:00:
Ik blijf ervan overtuigd dat dit schreeuwt om een NoSQL oplossing vraagt.

Als dit het enige punt is om naar NoSql te grijpen is het ook niet echt een goede keuze. Er zijn genoeg oplossingen om dit binnen een RDBMS voor elkaar te krijgen.

Natuurlijk is dit een probleem die je binnen een NoSQL omgeving niet hebt, maar het is wel fijn om naar het complete systeem te kijken of dat wel een goede keuze is. Om nu puur voor een recurrence veld een compleet nieuwe persistentie te gaan gebruiken lijkt mij niet echt verstandig.

[incaz]

drm schreef op zondag 24 november 2019 @ 21:24:
Dus: een zwik nullable columns is veruit de eenvoudigste oplossing en dat zou ik dus daarom gewoon doen. Elke tegenwerping zou gebaseerd moeten zijn op een praktische implicatie die reëel en verifieerbaar is.

Prima. Nullable columns zijn lastig omdat je ze meestal lastig zijn in het lezen en gebruik. Schrijven is makklijk, maar als je bv een setje van nullable columns maakt voor de dagen van de week, is het al ontzettend irritant om daar over te itereren zonder hele onelegante hacks.
Mocht je ooit een ander soort query willen, om overzichten te maken, dan is het al helemaal drama, dan moet je al die columns apart gaan benoemen en combineren.

Juist vanuit de praktische applicatie blijkt het net even verder door-normaliseren totdat je dergelijke eigenschappen onder elkaar hebt en niet naast elkaar, het makkelijkst. Eenvoudig op te vragen, eenvoudig te doorzoeken, eenvoudig te veranderen, en eenvoudig te schrijven. Alleen even nadenken hoe je het doet.

Anders ben je gewoon theoretisch aan 't muggenziften, en modelleren op de toekomstige (dus hypothetische) praktijk wordt zwaar (!) overschat. Denk liever na hoe je je voorbereid op een toekomst die nog niet vast staat (dat is namelijk de werkelijke wereld), bijvoorbeeld door een goede strategie te verzinnen wat je doet als je je datamodel aan moet passen.

... en vanuit het nadenken over hoe je je voorbereidt op een toekomst die nog niet vaststaat komt ook de wens tot normaliseren: dat is namelijk flexibel aan te passen en vereist geen verandering van je datamodel. Dat is ook handig omdat je dan minder gedoe hebt met bv oude backups terugplaatsen. Niks theoretisch muggenziften... op alle manieren kwam ik in de reele wereld steeds weer uit op: geen eindeloze reeks van nullable columns als je daarover moet itereren. Dan horen ze verticaal in een tabel.