[C#,SQL]unieke data in database

[niekvanruler]

We hebben een database met een klantentabel en een adressentabel.
Adressen hebben een kolom adrestype, voor factuuradres en afleveradres
Elke klant kan meerdere adressen hebben, maar van elk adrestype maar 1

Op klantis en adrestype in adrestabel hebben we een promary key gezet.

Als we nu voor een klant een tweede adres van hetzelfd type inserten wordt er een exception gegenereerd. Dit kunnen we via een try catch afvangen

Is dit een goede manier of zou het handiger zijn om in de code dit af te vangen, zodat de insert nooit plaatsvindt ?

[pjvandesande]

Je kunt toch eerst gewoon een count query uitvoeren om te kijken of het niet al bestaad, exceptions horen niet de flow van je app te bepalen.

[kenneth]

Ik zou het juist niet in de flow van je applicatie opnemen omdat je dan je applicatie mag gaan aanpassen zodra je een nieuw adrestype toevoegt in de database (ervan uitgaande dat dat een aparte tabel met 1:N relatie is op Adres). De database is er voor de constraints, dus geen dingen dubbel gaan doen, en dit kan de database veel beter dan jij

Maargoed, het is ook altijd een grijs gebied of het uitvoeren van iets onmogelijks bij de flow van je app hoort.

[pjvandesande]

kenneth schreef op vrijdag 09 december 2005 @ 08:41:
Ik zou het juist niet in de flow van je applicatie opnemen omdat je dan je applicatie mag gaan aanpassen zodra je een nieuw adrestype toevoegt in de database (ervan uitgaande dat dat een aparte tabel met 1:N relatie is op Adres). De database is er voor de constraints, dus geen dingen dubbel gaan doen, en dit kan de database veel beter dan jij

Maargoed, het is ook altijd een grijs gebied of het uitvoeren van iets onmogelijks bij de flow van je app hoort.

Nee, jij hoort eerst te contreleren of je data valid is, daarna of de data ook valid is voor in je databank (of er geen dubbelen zijn etc) en daarna hoor jij pas een instert uit te voeren.

Dit hoort dus duidelijk wel aan de kant van je applicatie! En hoe wil je op je Exception gaan reageren, je krijgt namelijk een SqlException, dat zegt verder nog weinig. Het hoeft namelijk geen dubbelen data te betekenen, misschien wel een fout in je query of data die te groot is voor een veld.

Nee, dit hoor je duidelijk niet met exceptions te regelen.

[joopst]

mijn voorkeur heeft het om het allebei te doen.

enerzijds het in de database onmogelijk maken om corrupte data in te voeren. anderzijds je app zo maken dat er ook geen foute data ingevoerd kan worden.

het is af te raden om je app-flow te baseren op exceptions, daar zijn ze nl niet voor bedoeld.

[EfBe]

EN unique constraint EN exception afvangen. Vooraf testen is niet thread safe.

Daarbij aangetekend dat adressen uitnormaliseren niet verstandig is: je gebruikt die entities nl. nooit apart, maar altijd in combinatie met de eigenaar van het adres.

[ Voor 53% gewijzigd door EfBe op 09-12-2005 10:13 ]

[bigbeng]

EfBe schreef op vrijdag 09 december 2005 @ 10:09:
EN unique constraint EN exception afvangen. Vooraf testen is niet thread safe.

Huh?

Ik ben nog niet heel lang in .Net bezig, maar daar kun je toch wel voor zorgen door op de juiste manier te coden? JIj zegt (zoals ik het begrijp) dat het doen van 2 queries per definitie niet thread safe is, kun je dat toelichten?

[kenneth]

Los van thread safety kan iemand anders natuurlijk tussendoor stiekem een record invoegen na jouw test en voor jouw feitelijke insert.

EfBe: Wat nu als je meerdere adressen per persoon hebt? Wat hier dus het geval is.

[niekvanruler]

EfBe schreef op vrijdag 09 december 2005 @ 10:09:
EN unique constraint EN exception afvangen. Vooraf testen is niet thread safe.

Daarbij aangetekend dat adressen uitnormaliseren niet verstandig is: je gebruikt die entities nl. nooit apart, maar altijd in combinatie met de eigenaar van het adres.

Wat bedoel je met adressen uitnormaliseren ?
Hoe zou jij het oplossen dan ?

[bigbeng]

kenneth schreef op vrijdag 09 december 2005 @ 10:17:
Los van thread safety kan iemand anders natuurlijk tussendoor stiekem een record invoegen na jouw test en voor jouw feitelijke insert.

EfBe: Wat nu als je meerdere adressen per persoon hebt? Wat hier dus het geval is.

Dat kan met een samengestelde insert toch wel, dat los je meestal op met een transaction en degene die het eerst commit heeft succes, de ander krijgt de uniqueness violation.

[pjvandesande]

kenneth schreef op vrijdag 09 december 2005 @ 10:17:
Los van thread safety kan iemand anders natuurlijk tussendoor stiekem een record invoegen na jouw test en voor jouw feitelijke insert.

EfBe: Wat nu als je meerdere adressen per persoon hebt? Wat hier dus het geval is.

Transactions, locking, dat hoeft geen probleem te zijn hoor.

[kenneth]

Mja, dan ga je m.i. wel erg ver om koste wat kost exceptions te voorkomen. Maar jullie vinden mij dan waarschijnlijk weer te makkelijk in het gebruiken van exceptions

bigbeng schreef op vrijdag 09 december 2005 @ 10:23:
de ander krijgt de uniqueness violation.

Dat resulteert toch in een exceptie?

[pjvandesande]

kenneth schreef op vrijdag 09 december 2005 @ 11:29:
Mja, dan ga je m.i. wel erg ver om koste wat kost exceptions te voorkomen. Maar jullie vinden mij dan waarschijnlijk weer te makkelijk in het gebruiken van exceptions

Hoezo te ver, alles moet gewoon valid zijn voordat je het gaat opslaan.
Je controleert een getal toch ook voordat je hem in de databank zet zodat je niet "aaa" in een Number field drukt?

[kenneth]

Natuurlijk, maar het gaat hier om referentiële integriteit, waar de validatie veel dynamischer is, itt geslacht char(1) dat ten alle tijde IN ('M', 'V') moet zijn.

[EfBe]

questa schreef op vrijdag 09 december 2005 @ 10:24:
[...]
Transactions, locking, dat hoeft geen probleem te zijn hoor.

Jawel, want je kunt niet voorkomen dat iemand een record INSERT of WIJZIGT zonder de complete table te locken. Table locks zijn niet gewenst, want je helpt je scalability ermee omzeep

T: thread A test, 'Sjaaqstraat 1313' komt niet voor
T+1: thread B insert 'Sjaaqstraat 1313'.
T+2: thread A insert 'Sjaaqstraat 1313' met de veronderstelling dat dit goed gaat. Niet dus. Dit is niet te voorkomen, want thread A heeft geen lock op de complete tabel. Thread B kan ook een update uitvoeren op een row dus alleen een table lock stop dit.

bigbeng schreef op vrijdag 09 december 2005 @ 10:14:
[...]
Huh?
Ik ben nog niet heel lang in .Net bezig, maar daar kun je toch wel voor zorgen door op de juiste manier te coden? JIj zegt (zoals ik het begrijp) dat het doen van 2 queries per definitie niet thread safe is, kun je dat toelichten?

Zie boven.

Je wijzigt niet hetzelfde record, dus locking kan niet.

niekvanruler schreef op vrijdag 09 december 2005 @ 10:17:
[...]
Wat bedoel je met adressen uitnormaliseren ?
Hoe zou jij het oplossen dan ?

Het is een klassieke fout om 'Adres' uit te normaliseren in een aparte tabel. Het punt is nl. dat dit niets toevoegt aan je model, alleen maar narigheid en traagheid. Het probleem is nl, dat normaliseren gedaan wordt om redundantie te verminderen. Maar, omdat adressen in een database in erg veel gevallen uniek zijn, is dit geen probleem.

Een andere reden is dat het lastig is adres data te hergebruiken. Wanneer je adressen uitnormaliseert heb je dus een AdresID of iets dergelijks aan de FK kant. Echter hoe ga je een adres meerdere keren gebruiken? Dat komt ten eerste zelden voor en ten tweede is het lastig, want je moet de gebruiker vragen een adres op te geven, dat dan gaan zoeken in de db of dat er al is en dan vragen of dit inderdaad het adres is.

Je kunt dit voorkomen door bv het adres op te slaan als postcode en huisnr. Deze elementen kun je dan als key gebruiken in een postcode tabel met adressen die je extern betrekt.

In theorie is er niets mis met het uitnormaliseren van een adres, echter in de praktijk is de relatie veelal 1:1 omdat het lastig is het een 1:n relatie te maken.

[ Voor 91% gewijzigd door EfBe op 09-12-2005 12:05 ]

[BertS]

EfBe schreef op vrijdag 09 december 2005 @ 11:51:Het is een klassieke fout om 'Adres' uit te normaliseren in een aparte tabel. Het punt is nl. dat dit niets toevoegt aan je model, alleen maar narigheid en traagheid. Het probleem is nl, dat normaliseren gedaan wordt om redundantie te verminderen. Maar, omdat adressen in een database in erg veel gevallen uniek zijn, is dit geen probleem.

Een andere reden is dat het lastig is adres data te hergebruiken. Wanneer je adressen uitnormaliseert heb je dus een AdresID of iets dergelijks aan de FK kant. Echter hoe ga je een adres meerdere keren gebruiken? Dat komt ten eerste zelden voor en ten tweede is het lastig, want je moet de gebruiker vragen een adres op te geven, dat dan gaan zoeken in de db of dat er al is en dan vragen of dit inderdaad het adres is.

Je kunt dit voorkomen door bv het adres op te slaan als postcode en huisnr. Deze elementen kun je dan als key gebruiken in een postcode tabel met adressen die je extern betrekt.

In theorie is er niets mis met het uitnormaliseren van een adres, echter in de praktijk is de relatie veelal 1:1 omdat het lastig is het een 1:n relatie te maken.

In deze situatie gaat het niet om het uitnormaliseren van adressen om 1 adres met meerdere klanten te delen volgens mij.
Een heel geldige reden kan zijn dat je bij een relatie meerdere adressen hebt (postadres/factuuradres/aanmaningsadres/kerstkaartadres/magazijn/etc). In dat geval lijkt het me toch wel handig om ze in een aparte tabel te zetten (ipv PostadresStraat, FactuuradresStraat, AanmaningsadresStraat....)
Dit is zeker het geval als je bijv. een document wilt genereren, waarbij de gebruiker een adres uit een lijstje moet kunnen selecteren.

[ Voor 5% gewijzigd door BertS op 09-12-2005 13:10 ]

[bigbeng]

EfBe schreef op vrijdag 09 december 2005 @ 11:51:
[...]

Jawel, want je kunt niet voorkomen dat iemand een record INSERT of WIJZIGT zonder de complete table te locken. Table locks zijn niet gewenst, want je helpt je scalability ermee omzeep

T: thread A test, 'Sjaaqstraat 1313' komt niet voor
T+1: thread B insert 'Sjaaqstraat 1313'.
T+2: thread A insert 'Sjaaqstraat 1313' met de veronderstelling dat dit goed gaat. Niet dus. Dit is niet te voorkomen, want thread A heeft geen lock op de complete tabel. Thread B kan ook een update uitvoeren op een row dus alleen een table lock stop dit.


[...]

Zie boven.

Je wijzigt niet hetzelfde record, dus locking kan niet.

Je doet meerdere inserts/updates achter elkaar, dus je moet toch met transactions werken. Een lock hoeft in principe alleen maar read-only te worden vastgelegd, dus het enige wat je aan scalability verliest is bulk update/insert met meerderen tegelijkertijd. Met transactions kun je zelfs zonder locks werken. Dit betekent wel grotere kans op fouten als gevolg van transaction commits die niet werken, maar goed, dit kun je ook weer ondervangen.
En gezien het feit dat we niet genoeg weten van het systeem, is moeilijk in te schatten of meerdere mensen op hetzelfde record tegelijkertijd kunnen veranderen. Als dit niet zo is, dan is transactions gebruiken in principe voldoende beveiliging en de kans op foutmeldingen als gevolg van commits minimaal.

Wat betreft uitnormaliseren van het adres, dat hangt af van de kans dat er nog een 3e/4e/5e etc adrestype bijkomt. Als dat aan de orde is kan het je programmeerwerk schelen door een extra adrestype in je database toe te voegen die dan automatisch door je systeem wordt opgepakt. Als je het allemaal in 1 tabel prakt dan heb je in zo'n geval een datamodelwijziging en een applicatiewijziging te pakken.
Ik ben het wel met Efbe eens dat bij kleine kans dat er nog meer adrestypen bijkomen, je het net zo goed in de klanttabel kunt opslaan, dat scheelt weer een join.

En tenslotte wat betreft thread safety: je kunt toch gewoon een thread maken die verantwoordelijk is voor het verwerken van veranderingen in je klant/adres deel van de applicatie? Op die manier kan er steeds maar 1 iemand tegelijkertijd een wijziging daarop doorvoeren. Dit gaat wel ten koste van scalability, maar gaat thread safety dat niet altijd als het gaat om niet-atomaire acties die "tegelijkertijd" moeten worden uitgevoerd?

[EfBe]

bee-es schreef op vrijdag 09 december 2005 @ 13:10:
In deze situatie gaat het niet om het uitnormaliseren van adressen om 1 adres met meerdere klanten te delen volgens mij.
Een heel geldige reden kan zijn dat je bij een relatie meerdere adressen hebt (postadres/factuuradres/aanmaningsadres/kerstkaartadres/magazijn/etc). In dat geval lijkt het me toch wel handig om ze in een aparte tabel te zetten (ipv PostadresStraat, FactuuradresStraat, AanmaningsadresStraat....)
Dit is zeker het geval als je bijv. een document wilt genereren, waarbij de gebruiker een adres uit een lijstje moet kunnen selecteren.

Wat jou handig lijkt, waar is dat op gebaseerd? Want hoe worden die adressen ingegeven? In een 'klanteninvulscherm' ? Indien ja, daar staan textboxen op voor straat e.d.? Dan heb je geen reet aan je aparte tabel.

bigbeng schreef op vrijdag 09 december 2005 @ 13:25:
[...]
Je doet meerdere inserts/updates achter elkaar, dus je moet toch met transactions werken. Een lock hoeft in principe alleen maar read-only te worden vastgelegd, dus het enige wat je aan scalability verliest is bulk update/insert met meerderen tegelijkertijd. Met transactions kun je zelfs zonder locks werken. Dit betekent wel grotere kans op fouten als gevolg van transaction commits die niet werken, maar goed, dit kun je ook weer ondervangen.

Je hoeft mij niets te vertellen over transacties hoor

Maar wat je zegt is volledig niet relevant. Thread A en thread B. Thread A kijkt in de tabel. Geen 'straat ABC'. Dan gaat thread B record 1000 wijzigen, van 'straat XYZ' in 'straat ABC'. Dan gaat A 'straat ABC' inserten. *poef*. M.a.w.: test is overbodig, gewoon inserten en dan de fout afvangen en terugleiden naar de gebruiker dat het adres al in gebruik is (of anders afhandelen, whatever er is besloten voor dat geval). Die test stopt men er soms in om die fout niet af te vangen, maar dat is dus onzin, die komt geheid.

Het gaat mis omdat A test op een waarde die NA de test maar VOOR de insert wordt toegevoegd aan de tabel in een andere thread. A kan locken wat A wil, maar dat helpt niets, want A zou dan alle rows moeten locken pal voor de test tot aan pal na de insert. Immers, op geen andere manier kan A voorkomen dat B iets wijzigt in een _ANDERE_ row dan A mee gaat werken.

Transacties zonder locks bestaan overigens niet, tenzij je een mode gebruikt waarin je een snapshot krijgt van de set waarop je modificaties uitvoert.

En gezien het feit dat we niet genoeg weten van het systeem, is moeilijk in te schatten of meerdere mensen op hetzelfde record tegelijkertijd kunnen veranderen. Als dit niet zo is, dan is transactions gebruiken in principe voldoende beveiliging en de kans op foutmeldingen als gevolg van commits minimaal.

Het ging om wat juist was. Testen en dan doen is niet juist in dit geval want het levert fouten op. Dan kan dan in jouw ogen wel 'minimaal' zijn, maar dat zijn zg. 'famous last words', die je altijd opbreken. Daarom moet je zorgen dat je software in 100% van de gevallen foutloos is, in theorie, en met tests checken of je software implementatie(!) van je ontwerp inderdaad een juiste implementatie is. Indien ja, dan is je software in de praktijk ook 100% foutloos.

Wat betreft uitnormaliseren van het adres, dat hangt af van de kans dat er nog een 3e/4e/5e etc adrestype bijkomt. Als dat aan de orde is kan het je programmeerwerk schelen door een extra adrestype in je database toe te voegen die dan automatisch door je systeem wordt opgepakt. Als je het allemaal in 1 tabel prakt dan heb je in zo'n geval een datamodelwijziging en een applicatiewijziging te pakken.

Adrestype is in het geval van 'adres' een attribute van de relatie (objectified):
[CustomerID* | AdresID* | AdresType]

Echter wie het zo uitnormaliseert zoals hierboven geschetst heeft geen voordeel van die normalisatie want Adres wordt niet hergebruikt. Dit is het gevolg van hoe 'adres' in de praktijk wordt toegepast. In theorie is het wel herbruikbaar natuurlijk, het is een entity als alle andere, maar de attributes van adres neigen naar een 1:1 relatie met customer, waardoor je customer en adres kan mergen.

En tenslotte wat betreft thread safety: je kunt toch gewoon een thread maken die verantwoordelijk is voor het verwerken van veranderingen in je klant/adres deel van de applicatie? Op die manier kan er steeds maar 1 iemand tegelijkertijd een wijziging daarop doorvoeren. Dit gaat wel ten koste van scalability, maar gaat thread safety dat niet altijd als het gaat om niet-atomaire acties die "tegelijkertijd" moeten worden uitgevoerd?

Die thread krijgt het dan wel erg druk en is direct de bottleneck van je pipeline naar / van de db.

Beter is functionality locking, waardoor gebruikers alleen die elementen kunnen wijzigen nadat ze die eerst hebben geclaimd. Zodoende kun je het werk wat gedaan moet worden beter structureren en krijg je een hogere efficiency, want persoon A gaat dan niet iets wijzigen aan data die persoon B ook net zit te wijzigen want in dat geval kan B beter die wijziging van A OOK doen en A iets anders. Dat scheelt dataverlies door een of ander concurrency mechanisme (optimistic/pessimistic, maakt niet uit)

[ Voor 76% gewijzigd door EfBe op 09-12-2005 17:56 ]

[Verwijderd]

EfBe schreef op vrijdag 09 december 2005 @ 17:41:
[...]

Wat jou handig lijkt, waar is dat op gebaseerd? Want hoe worden die adressen ingegeven? In een 'klanteninvulscherm' ? Indien ja, daar staan textboxen op voor straat e.d.? Dan heb je geen reet aan je aparte tabel.

De truc van meerdere adressen is om ze niet direct in het klantenscherm te laten invullen. Je (her)gebruikt gewoon een adres-subscherm. Bijkomend voordeel is hierbij dat je de adressen alleen uit de database ophaalt indien je ze nodig hebt. Dit scheelt geheugen op de server en client en ook nog een netwerk-verkeer.

[EfBe]

Verwijderd schreef op vrijdag 09 december 2005 @ 20:33:
[...]

De truc van meerdere adressen is om ze niet direct in het klantenscherm te laten invullen. Je (her)gebruikt gewoon een adres-subscherm. Bijkomend voordeel is hierbij dat je de adressen alleen uit de database ophaalt indien je ze nodig hebt. Dit scheelt geheugen op de server en client en ook nog een netwerk-verkeer.

Klopt, en dan zou ik ze uit een postcode tabel halen, zodat je postcode + huisnmr invult en dan het adres vanzelf aangeleverd krijgt, foutloos.

Echter wanneer je dat niet doet heb je initieel een lege adressentabel. die vult men dus gedurende het gebruik van het programma. De ellende is dan: moet de gebruiker een nieuw adres invullen of door de adressen browsen die er al zijn. Dat kan tijdrovend zijn.

[ Voor 16% gewijzigd door EfBe op 10-12-2005 12:10 ]

[kenneth]

EfBe, volgens mij beschrijf jij een N:N relatie Klant:Adres

Het gaat juist om een 1:N:

- Piet Jansen
-- Factuuradres: Postbus 4321, Huizen
-- Priveadres: Kerkstraat 20, Hilversum
-- Bezoekadres: Stationsplein 14, Hilversum

[BertS]

kenneth schreef op zaterdag 10 december 2005 @ 12:44:
EfBe, volgens mij beschrijf jij een N:N relatie Klant:Adres

Het gaat juist om een 1:N:

- Piet Jansen
-- Factuuradres: Postbus 4321, Huizen
-- Priveadres: Kerkstraat 20, Hilversum
-- Bezoekadres: Stationsplein 14, Hilversum

Inderdaad.
Ik heb zelf deze week een applicatie gezien die dat zo gebruikt. Die hebben diverse typen, waaronder Postadres, Factuuradres en Aanmaningsadres.
Indien er geen adres is aangemaakt als 'Aanmaningsadres', wordt het Factuuradres gebruikt. Als er geen Factuuradres is wordt het Postadres gebruikt.

Bij drie adrestypen kan het dus goed een 1-N relatie zijn, en geen 1-3 (1 klant heeft niet altijd 3 adressen, wat EfBe ook lijkt te veronderstellen).
In de situatie van TS lijkt dit ook het geval te zijn:

Een klant kan meerdere adressen hebben, maar van elk adrestype maar 1.

[EfBe]

kenneth schreef op zaterdag 10 december 2005 @ 12:44:
EfBe, volgens mij beschrijf jij een N:N relatie Klant:Adres

Het gaat juist om een 1:N:

- Piet Jansen
-- Factuuradres: Postbus 4321, Huizen
-- Priveadres: Kerkstraat 20, Hilversum
-- Bezoekadres: Stationsplein 14, Hilversum

klant adres IS in theorie ook m:n, want je kunt meerdere bedrijven op hetzelfde adres hebben. Of meerdere klanten: pa, ma, zoonlief.

'AdresType' is echter gerelateerd aan customer, niet aan adres. Immers, een adres kan gebruikt worden voor zowel factuuradres als postadres, en dan gebruik je dus dezelfde adresid-customer maar verschillende adrestype values. (adrestype moet dus in mijn voorbeeld ook in de PK, error van mij)