[DB] normalisatie / integratie

[Verwijderd]

Ben bezig met het normaliseren / integreren van enkele formulieren en kom er bij de integratie niet meer uit. Hieronder volgen 2 normalisaties welke vervolgens geintegreerd dienen te worden.

quote: Eerste normalisatie

0e normaalvorm:
Transportlijst:
Transportlijstdatum (primaire sleutel), {bestemmingsplaats, klantnr, postcode, ordernr, aantal.eenheden,

gewicht, kubieke.meter, kenteken}


1e normaalvorm:
Transportlijst:
Transportlijstdatum (primaire sleutel)

Transportlijstregel:
Transportlijstdatum (secundaire sleutel), bestemmingsplaats, klantnr, postcode, ordernr (primaire

sleutel), aantal.eenheden, gewicht, kubieke.meter, kenteken


2e normaalvorm:
Transportlijst:
Transportlijstdatum (primaire sleutel)

Transportlijstregel:
Transportlijstdatum (secundaire sleutel), ordernr (primaire sleutel), aantal.eenheden, gewicht,

kubieke.meter, kenteken

Ordergegevens:
ordernr (primaire sleutel), klantnr, bestemmingsplaats, postcode


3e normaalvorm:
Transportlijst:
Transportlijstdatum (primaire sleutel)

Transportlijstregel:
Transportlijstdatum (secundaire sleutel), ordernr (primaire sleutel), aantal.eenheden, gewicht,

kubieke.meter, kenteken

Ordergegevens:
ordernr (primaire sleutel), klantnr, postcode

Postcode:
postcode (primaire sleutel), bestemmingsplaats

En nu de tweede normalisatie.

quote: Tweede normalisatie

0e normaalvorm:
Vrachtbrief:
Vrachtbriefnr (primaire sleutel), chauffeur, kenteken, vrachtbriefdatum, {klantnaam, afdeling, tav, straat, huisnr, postcode, plaats, {ordernr, aantal.eenheden, aflevertijdstip, handtekening}}


1e normaalvorm:
Vrachtbrief:
Vrachtbriefnr (primaire sleutel), chauffeur, kenteken, vrachtbriefdatum

Bestemming:
vrachtbriefnr (secundaire sleutel), klantnaam (primaire sleutel), afdeling, tav, straat, huisnr, postcode, plaats

Leverregel:
klantnaam (secundaire sleutel), ordernr (primaire sleutel), aantal.eenheden, aflevertijdstip, handtekening


2e normaalvorm:
Vrachtbrief:
Vrachtbriefnr (primaire sleutel), chauffeur, kenteken, vrachtbriefdatum

Bestemming:
vrachtbriefnr (secundaire sleutel), klantnaam (primaire sleutel)

Leverregel:
klantnaam (secundaire sleutel), ordernr (primaire sleutel), aantal.eenheden, aflevertijdstip, handtekening

Klant:
klantnaam (primaire sleutel), afdeling, tav, straat, huisnr, postcode, plaats


3e normaalvorm:
Vrachtbrief:
Vrachtbriefnr (primaire sleutel), chauffeur, kenteken, vrachtbriefdatum

Bestemming:
vrachtbriefnr (secundaire sleutel), klantnaam (primaire sleutel)

Leverregel:
klantnaam (secundaire sleutel), ordernr (primaire sleutel), aantal.eenheden, aflevertijdstip, handtekening

Klant:
klantnaam (primaire sleutel), afdeling, tav, straat, huisnr, postcode

Postcode:
postcode (primaire sleutel), bestemmingsplaats

Zo dat waren de normalisaties. Nu kunnen we beginnen aan de integratie van beide gegevens.
Als eerste is er gecontroleerd op homoniemen en synoniemen. Deze zijn niet meer aanwezig. Hieropvolgend heb ik Tabellen met dezelfde sleutel samengevoegd. Dit is er mijn inziende maar één (namelijk postcode). En vervolgens heb ik hier staan, dat ik entiteittypes met dezelfde naam moet samenvoegen. En hier gaat het bij mij mis. Hoe doe ik dit precies? Iemand die mij op weg kan helpen?? Gelieve te helpen aan de hand van genoemde gegevens.

quote: Integratie tot nu toe

Transportlijst:
Transportlijstdatum (primaire sleutel)

Transportlijstregel:
Transportlijstdatum (secundaire sleutel), ordernr (primaire sleutel), aantal.eenheden, gewicht,

kubieke.meter, kenteken

Ordergegevens:
ordernr (primaire sleutel), klantnr, postcode

Vrachtbrief:
Vrachtbriefnr (primaire sleutel), chauffeur, kenteken, vrachtbriefdatum

Bestemming:
vrachtbriefnr (secundaire sleutel), klantnaam (primaire sleutel)

Leverregel:
klantnaam (secundaire sleutel), ordernr (primaire sleutel), aantal.eenheden, aflevertijdstip, handtekening

Klant:
klantnaam (primaire sleutel), afdeling, tav, straat, huisnr, postcode

Postcode:
postcode (primaire sleutel), bestemmingsplaats

[Verwijderd]

Is er nu werkelijk niemand die enig idee heeft ?

[djluc]

Ik denk dat je het beter even snel met acces kunt visualiseren want je moet best veel voorstellingsvermogen hebben als er zoveel tabellen zijn. Verder ben je nog net niet over de 24 uur heen

[curry684]

Je haalt enorm veel stunts uit met de standaard stelregel "een key mag nooit betekenisvolle data bevatten".

Ik mag bij jou niet eens m'n bedrijf van naam veranderen zonder de database te corrumperen

Doe eens snel aan alle tabellen liefst GUID's maar op z'n minst autoincrement integers toevoegen als primary en foreign keys.

Om in de woorden van DB-experts als Joe Celko te spreken: "once a user complains that he wants to see a primary key you're in big trouble."

[Verwijderd]

curry684 schreef op 14 januari 2004 @ 14:25:
Je haalt enorm veel stunts uit met de standaard stelregel "een key mag nooit betekenisvolle data bevatten".

Ik mag bij jou niet eens m'n bedrijf van naam veranderen zonder de database te corrumperen

Doe eens snel aan alle tabellen liefst GUID's maar op z'n minst autoincrement integers toevoegen als primary en foreign keys.

Om in de woorden van DB-experts als Joe Celko te spreken: "once a user complains that he wants to see a primary key you're in big trouble."

Helaas moet ik gebruik maken van de 3 stappen bij normaalvormen. Dit wat jij noemt is niet toegestaan. Zal dadelijk een link geven naar de stappen moet ik nog even opzoeken.

[curry684]

Normalisatie heeft niets van doen met het kiezen van een foute key, maar met het goed zetten van dependencies op die key.

Introduceer in de 0e NV eens voor de lol voor al je tabellen een extra veld 'OrderGUID', 'KlantGUID' etc. en merk dat dit niets van doen heeft met de verdere normalisatie, maar aan het einde wel een beter onderhoudbare en beheersbare database tot gevolg heeft.

Er is niets zo erg als op een productiedatabase erachter komen dat je een key verkeerd gekozen hebt: in jouw opzet mag een bedrijf niet van naam veranderen, mag TPG geen postcodes wijzigen, mag er maar 1 transport per dag plaatsvinden. Alle 3 zijn volstrekt foute aannames.

[curry684]

Even uit het blote hoofd in notepad:

TransportList:
TransportListId (PK), DriverId (FK), TruckId (FK), TransportListDate

TransportEntry:
TransportEntryId (PK), TransportListId (FK), OrderId (FK)

Order:
OrderId (PK), CustomerId (FK), OrderDate

OrderEntry:
OrderEntryId (PK), OrderId (FK), ItemId (FK), Quantity, Size

Customer:
CustomerId (PK), Name, AddressId (FK)

Truck:
TruckId (PK), Type, SizeCapacity, PurchaseDate, LicensePlateNumber
(unique constraint on LicensePlateNumber)

Driver:
DriverId (PK), Name, AddressId (FK), DateOfHiring, DateOfBirth, SocialSecurityNumber
(unique constraint on SocialSecurityNumber)

Address:
AddressId (PK), Address, HouseNumber, PostalCode, City, CountryId (FK)
(combined Unique Constraint on PostalCode + HouseNumber)

Country:
CountryId (PK), Name
(unique constraint on Name)

Volgens mij is dit ongeveer wat je zoekt, nu mag je zelf uitzoeken hoe je van jouw origineel naar dit resultaat komt

[ Voor 4% gewijzigd door curry684 op 14-01-2004 15:21 ]

[Creepy]

Verwijderd schreef op 14 januari 2004 @ 14:52:
[...]

Helaas moet ik gebruik maken van de 3 stappen bij normaalvormen. Dit wat jij noemt is niet toegestaan. Zal dadelijk een link geven naar de stappen moet ik nog even opzoeken.

"de 3 stappen"? Er zijn meer dan 4 normaalvormen hoor (dat wil niet zeggen dat je ze allemaal moet gebruiken, maar toch)

Ennuh.. mocht je na Curry's verhaal nog niet overtuigt zijn: Wat ga je nu doen als er 2 klanten dezelfde naam hebben?

[ Voor 9% gewijzigd door Creepy op 14-01-2004 15:32 ]

[curry684]

Creepy schreef op 14 januari 2004 @ 15:31:
[...]

"de 3 stappen"? Er zijn meer dan 4 normaalvormen hoor (dat wil niet zeggen dat je ze allemaal moet gebruiken, maar toch)

NV 4 en NV 5 zijn wel heel zeldzaam, alhoewel ik geloof ik in m'n voorbeeld hierboven tegen de 4 aanzit.

* curry684 gaat vanavond weer eens nalezen wat al die NV's ook alweer inhielden, ik klop liever gewoon direct een goed datamodel in

[whoami]

curry684 schreef op 14 januari 2004 @ 15:37:
[...]

NV 4 en NV 5 zijn wel heel zeldzaam, alhoewel ik geloof ik in m'n voorbeeld hierboven tegen de 4 aanzit.

NV4 en NV5 waren -dacht ik- het opnieuw denormaliseren (eventueel voor performance redenen bij data-warehouses enzo).

Over het PK verhaaltje; ik zorg er altijd voor dat m'n PK's niets zeggende velden zijn. Maw, ik ga altijd meestal een extra veld (auto-increment) aan m'n tabel toevoegen dat ik dan tot PK promoveer.

[ Voor 24% gewijzigd door whoami op 14-01-2004 15:41 ]

[Verwijderd]

Ook het gegeven dat een order altijd maar op een transportlijst voorkomt (transportlijstregel tabel met primary key ordernr) komt op mij een beetje vreemd over. Ik weet uit ervaring (online bestelling computersupplies) dat een enkele order soms in twee keer geleverd wordt.
Het lijkt mij dan ook dat orders en transportlijsten een veel-op-veel relatie hebben die je met een tussentabel hebt opgelost (maar dan moeten wel ordernr en transportlijstdatum samen primary key zijn)

Overigens wil ik absoluut geen afbreuk doen aan curry's verhaal, maar door het introduceren van 'lege betekenis' primary keys heb je in zo'n 'koppeltabel' wel een probleem : hoe zorg ik dat de combo ordernr en transportlijstnr samen uniek zijn als ik een extra primary key 'transportlijstregelnr' introduceer? Volgens mij zijn ordernr en transportlijstnr (niet datum uiteraard, zie verhaal curry) al 'lege' keys ter identificatie en zodoende hoeft er niet nog een key bij, maar da's mijn (wazige) idee...

Verder heb ik een beetje het gevoel dat TS omwille van de methode (normaliseren) zijn boerenverstand een beetje uit het oog verloren is :
- bestemmingsplaats lijkt mij van de klant (klantnr) afhangen, tenzij je van een systeem uitgaat dat een enkele klant verschillende afleveradressen en een vast factuuradres heeft, maar dan ontbreekt er nog wel meer aan je verhaal. Houd het simpel, een klant heeft EEN adres, dan
- via de koppeling van transportlijstregel via primary key ordernr concludeer ik dat jouw order hetzelfde is als een transportlijstregel. Volgens mij mag je na normaliseren ook nooit twee tabellen overhouden met dezelfde primary key anders moet je deze samenvoegen (logisch toch?).
- de onderlinge verhouding transportlijst-vrachtbrief is mij geheel onduidelijk. Is een transportlijst niet hetzelfde als een vrachtbrief?
- leverregel moet natuurlijk niet klantnaam als secundaire sleutel hebben! Klant kan je bepalen aan de hand van order (oftwel ordernr-klantnr koppeling) Ook hiervoor geldt weer dat je leverregel tabel een koppel-tabel tussen vrachtbrief en order is, dus moet (imho) met dubbele PK op ordernr en vrachtbriefnr

Stel voordat je gaat normaliseren altijd even de 'echte wereld' relaties vast van je 'objecten' en controleer na normaliseren of deze nog steeds kloppen (zo niet dan is er iets grondig fout gegaan).
dus :

- orders hebben 1..1 klanten
- klanten hebben 0..n orders
- vrachtbrieven bevatten 1..n orders
- orders kunnen op 0..n vrachtbrieven voorkomen (order met meerdere colli, is dit geen optie dan 0..1 vrachtbrieven).
- klanten hebben 1..1 adres
- adressen horen 1..1 klant (of 1..n klanten, dan losse tabellen adres/klant)
- postcode heeft 1..1 bestemmingsplaats
- bestemmingsplaatsen horen bij 1..n postcodes (indien plaats = plaatsnaam, niet volledig adres)

etc. etc.
DAN normaliseren, controleren of alles nog klopt, etc.
* Objecten die in beide richting een 1 op 1 relatie hebben (bijvoorbeeld klant-adres) horen in dezelfde tabel thuis.
* Dingen die een 1 op n relatie hebben in twee verschillende tabellen met aan de n-kant van de relatie de tabel met als foreign key het unieke id van de 1 kant van de relatie (bijvoorbeeld plaatsnaam-postcode)
* Dingen die een n op n relatie hebben koppel je met behulp van een koppeltabel, welke twee primary keys heeft en een n op 1 relatie met bijde 'orignele' tabellen. De originele tabellen hebben dan geen directe onderlinge relatie meer (bijvoorbeeld orders - vrachtbrieven)

edit : als access-gebruiker haal ik hierboven regelmatig primarykey en unique constraint door elkaar maar in access kan je nou eenmaal niet anders
PK = unique constraint in dit verhaal dus...

edit2 : EN TYP NIET ZO SNEL STELLTJE...

[ Voor 5% gewijzigd door Verwijderd op 14-01-2004 15:43 ]

[BrZ]

Transportlijst:
Transportlijstdatum (primaire sleutel)

Transportlijstregel:
Transportlijstdatum (secundaire sleutel), ordernr (primaire sleutel), aantal.eenheden, gewicht, kubieke.meter, kenteken

Ordergegevens:
ordernr (primaire sleutel), klantnr, postcode

Hmm, apart, 2 tabellen met dezelfde primary key

Die normaalvormen werken trouwens echt niet lekker voor mij. Het uiteindelijke model kan ik (bijna) altijd in 1x helemaal uitschrijven, met die normaalvorm ben ik een hele tijd bezig

[whoami]

Verwijderd schreef op 14 januari 2004 @ 15:40:
Overigens wil ik absoluut geen afbreuk doen aan curry's verhaal, maar door het introduceren van 'lege betekenis' primary keys heb je in zo'n 'koppeltabel' wel een probleem : hoe zorg ik dat de combo ordernr en transportlijstnr samen uniek zijn als ik een extra primary key 'transportlijstregelnr' introduceer?

Uniqueness kan je ook afdwingen door een unique constraint/index op die column(s) te leggen. Dat hoeft niet perse met een primary key.
Echter, in 'koppeltabellen' maak ik ook meestal [u]geen[/ub] extra auto-incr veld bij als PK. (Vandaar ook dat ik altijd doorstreept heb in m'n eerdere post).

[curry684]

whoami schreef op 14 januari 2004 @ 15:40:
[...]
Over het PK verhaaltje; ik zorg er altijd voor dat m'n PK's niets zeggende velden zijn. Maw, ik ga altijd meestal een extra veld (auto-increment) aan m'n tabel toevoegen dat ik dan tot PK promoveer.

GUID's zijn oneindig veel beter replicatable en nauwelijks tot niet merkbaar trager in de praktijk. Tevens beschermen ze je tegen fuckups met een foreign key die per ongeluk naar een primary key uit een verkeerde tabel verwijst als je een programmeerfout maakt of handmatig iets fout inklopt: de DRI zal dan altijd een error geven met GUID's, met identity-fields zelden.

Echter, in 'koppeltabellen' maak ik ook meestal [u]geen[/ub] extra auto-incr veld bij als PK. (Vandaar ook dat ik altijd doorstreept heb in m'n eerdere post).

Een koppeling zelf is dan ook geen uniek identificeerbaar veld zolang het enkel 2 foreign key's bevat. Een PK is dan idd overbodig.

Wat betreft unique constraints: zie [rml]curry684 in "[ DB] normalisatie / integratie"[/rml]

[ Voor 25% gewijzigd door curry684 op 14-01-2004 15:49 ]

[Creepy]

whoami schreef op 14 januari 2004 @ 15:40:
[...]

NV4 en NV5 waren -dacht ik- het opnieuw denormaliseren (eventueel voor performance redenen bij data-warehouses enzo).

De 4de NV is dat in elk geval niet. En ik dacht de 5de ook niet, maar dan zou ik toch echt m'n boeken over databases en normaliseren er weer eens bij moeten pakken

[Verwijderd]

een er-diagram zou het wel een stuk overzichtelijker maken

[Creepy]

Verwijderd schreef op 14 januari 2004 @ 17:24:
een er-diagram zou het wel een stuk overzichtelijker maken

Vindt je? De beschrijving van z'n tabellen met een PK's en FK's erbij zoals ie heeft gedaan lijkt me duidelijk genoeg. Een ER diagram lijkt me in dit geval niet veel duidelijkheid meer geven. (Dan nog geef ik de voorkeur aan een netwerk diagram zodat ook je tussentabellen erbij staan, maar goed ).

[curry684]

*trap*

Heef Rats nog iets aan onze hulp gehad?

[P_de_B]

@Curry:

Hoe ga jij om met 'lijsttabellen', bijvoorbeeld een tabel gewichtseenheid? (kg, gram, lbs) Gebruik je dan GeweenheidId(PK), Omschrijving of alleen GewichtsEenheid(Pk).

Oftwel, zijn er geen gevallen waarin je natural keys gebruikt?

Ik maak altijd gebruik van een autonummerId en een omschrijving, maar ik begin te neigen naar een natural key voor tabellen als het voorbeeld wat ik noemde.

offtopic:
toffe sig!

[curry684]

Zie de tabel Country in [rml]curry684 in "[ DB] normalisatie / integratie"[/rml]

Dus ja, een aparte GUID als PK en dan de omschrijving met een Unique constraint op naam. Het is in principe even snel en staat je toe om de tabel later uit te breiden.

Om een heel goed praktijkvoorbeeld te geven: stel dat ik Country wil uitbreiden met 'Language' zodat ik automatisch in de goede taal kan factureren voor een klant. Dan kan ik dus een nieuwe column "Language" toevoegen, de Unique constraint op 'Name' verwijderen en de volgende 2 regels toevoegen:

Belgium	Dutch
Belgium	French

Hiermee heb ik dan m'n model dynamisch uitgebreid zonder ook maar ergens aan de referential integrity te hoeven komen

Zelfs jouw tabel 'Gewichtseenheid' is uitbreidbaar, die kun je laten self-joinen op 'subdivision eenheid', bijvoorbeeld 'kg' naar 'g' met een extra kolom 'DivisionsPerEntry', waarmee je automatisch kilogrammen naar grammen kunt laten omrekenen en vice versa indien gewenst/geconfigureerd

Gouden regel: je kunt alles aan een tabel makkelijk wijzigen, behalve de referential integrity.