Mysql workbench, normaliseren
Ik ben mij aan het inlezen over normalisatie en probeer nu het programma mysql workbench uit.
Op de Nederlandse pagina's krijg je veel verwijzingen naar het database ontwerp van Yapf en soms kom je een artikel op deze website tegen.
Op basis hiervan heb ik even een opzetje gemaakt( welke uiteraard niet af is), hieronder de code zoals ik hem terug krijg van het programma:
Code (php)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
-- MySQL Script generated by MySQL Workbench
-- 01/12/15 15:13:48
-- Model: New Model Version: 1.0
-- MySQL Workbench Forward Engineering
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `mydb` ;
-- -----------------------------------------------------
-- Table `mydb`.`postcodes`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`postcodes` ;
CREATE TABLE IF NOT EXISTS `mydb`.`postcodes` (
`postcodeID` INT NOT NULL,
`postcode` VARCHAR(45) NOT NULL,
`straatnaam` VARCHAR(45) NOT NULL,
`plaats` VARCHAR(45) NOT NULL,
PRIMARY KEY (`postcodeID`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`adressen`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`adressen` ;
CREATE TABLE IF NOT EXISTS `mydb`.`adressen` (
`adresID` INT NOT NULL,
`postcodeID` INT NOT NULL,
PRIMARY KEY (`adresID`),
CONSTRAINT `postcodeID`
FOREIGN KEY (`postcodeID`)
REFERENCES `mydb`.`postcodes` (`postcodeID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`klanten`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`klanten` ;
CREATE TABLE IF NOT EXISTS `mydb`.`klanten` (
`klantID` INT NOT NULL AUTO_INCREMENT,
`voornaam` VARCHAR(45) NOT NULL,
`tussenvoegsel` VARCHAR(45) NOT NULL,
`achternaam` VARCHAR(45) NOT NULL,
`adresID` INT NOT NULL,
`huisnummer` VARCHAR(45) NOT NULL,
`huisnummer_toevoeging` VARCHAR(45) NOT NULL,
PRIMARY KEY (`klantID`),
INDEX `adresID_idx` (`adresID` ASC),
CONSTRAINT `adresID`
FOREIGN KEY (`adresID`)
REFERENCES `mydb`.`adressen` (`adresID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`artikelen`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`artikelen` ;
CREATE TABLE IF NOT EXISTS `mydb`.`artikelen` (
`artikelID` INT NOT NULL AUTO_INCREMENT,
`artikelnaam` VARCHAR(45) NOT NULL,
PRIMARY KEY (`artikelID`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`orders`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`orders` ;
CREATE TABLE IF NOT EXISTS `mydb`.`orders` (
`orderID` INT NOT NULL AUTO_INCREMENT,
`artikelID` INT NOT NULL,
`klantID` INT NOT NULL,
`aantal` DECIMAL(10,0) NOT NULL,
`prijs` DECIMAL(5,2) NOT NULL,
PRIMARY KEY (`orderID`),
INDEX `klantenID_idx` (`klantID` ASC),
INDEX `artikelID_idx` (`artikelID` ASC),
CONSTRAINT `klantenID`
FOREIGN KEY (`klantID`)
REFERENCES `mydb`.`klanten` (`klantID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `artikelID`
FOREIGN KEY (`artikelID`)
REFERENCES `mydb`.`artikelen` (`artikelID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
-- 01/12/15 15:13:48
-- Model: New Model Version: 1.0
-- MySQL Workbench Forward Engineering
SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
-- -----------------------------------------------------
-- Schema mydb
-- -----------------------------------------------------
CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ;
USE `mydb` ;
-- -----------------------------------------------------
-- Table `mydb`.`postcodes`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`postcodes` ;
CREATE TABLE IF NOT EXISTS `mydb`.`postcodes` (
`postcodeID` INT NOT NULL,
`postcode` VARCHAR(45) NOT NULL,
`straatnaam` VARCHAR(45) NOT NULL,
`plaats` VARCHAR(45) NOT NULL,
PRIMARY KEY (`postcodeID`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`adressen`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`adressen` ;
CREATE TABLE IF NOT EXISTS `mydb`.`adressen` (
`adresID` INT NOT NULL,
`postcodeID` INT NOT NULL,
PRIMARY KEY (`adresID`),
CONSTRAINT `postcodeID`
FOREIGN KEY (`postcodeID`)
REFERENCES `mydb`.`postcodes` (`postcodeID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`klanten`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`klanten` ;
CREATE TABLE IF NOT EXISTS `mydb`.`klanten` (
`klantID` INT NOT NULL AUTO_INCREMENT,
`voornaam` VARCHAR(45) NOT NULL,
`tussenvoegsel` VARCHAR(45) NOT NULL,
`achternaam` VARCHAR(45) NOT NULL,
`adresID` INT NOT NULL,
`huisnummer` VARCHAR(45) NOT NULL,
`huisnummer_toevoeging` VARCHAR(45) NOT NULL,
PRIMARY KEY (`klantID`),
INDEX `adresID_idx` (`adresID` ASC),
CONSTRAINT `adresID`
FOREIGN KEY (`adresID`)
REFERENCES `mydb`.`adressen` (`adresID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`artikelen`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`artikelen` ;
CREATE TABLE IF NOT EXISTS `mydb`.`artikelen` (
`artikelID` INT NOT NULL AUTO_INCREMENT,
`artikelnaam` VARCHAR(45) NOT NULL,
PRIMARY KEY (`artikelID`))
ENGINE = InnoDB;
-- -----------------------------------------------------
-- Table `mydb`.`orders`
-- -----------------------------------------------------
DROP TABLE IF EXISTS `mydb`.`orders` ;
CREATE TABLE IF NOT EXISTS `mydb`.`orders` (
`orderID` INT NOT NULL AUTO_INCREMENT,
`artikelID` INT NOT NULL,
`klantID` INT NOT NULL,
`aantal` DECIMAL(10,0) NOT NULL,
`prijs` DECIMAL(5,2) NOT NULL,
PRIMARY KEY (`orderID`),
INDEX `klantenID_idx` (`klantID` ASC),
INDEX `artikelID_idx` (`artikelID` ASC),
CONSTRAINT `klantenID`
FOREIGN KEY (`klantID`)
REFERENCES `mydb`.`klanten` (`klantID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `artikelID`
FOREIGN KEY (`artikelID`)
REFERENCES `mydb`.`artikelen` (`artikelID`)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB;
SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
Dit geeft mij de volgende diagram:
Ik weet dat het niet geoptimaliseerd is, maar ik ben niet bekend met de kleurtjes en kraaienpootjes en op YAPF missen er afbeeldingen.
Ik zou denken dat ik goed op weg ben, kan er iemand een blik op werken?
ps: varchar 45 is een standaard waarde in de workbench, het gaat mij om het diagram en de opzet er van.
Gewijzigd op 12/01/2015 15:23:47 door E vH
Als je echter een adres-ID alleen koppelt aan een postcode-ID, dan kan die tabel er ook uit.
Per klant sla je dan postcode-ID (in plaats van adres-ID) plus huisnummmer en huisnummertoevoeging op.
Als je per order meerdere artikelen wilt leveren, heb je daarvoor nog een extra tabel nodig.
Gewijzigd op 12/01/2015 15:48:47 door Ward van der Put
Ten tweede: Addressen normaliseren is inderdaad een ramp. Zelf sla ik al niet meer op dan een postcode en een huisnummer. Deze kun je dan online tegen een database aanhouden of je kunt een postcode database kopen. Als je dit niet doet kun je prima de postcode, huisnummer en plaatsnaam in de klantentabel opslaan (correct me if i'm wrong)
Tips:
Maak een orderregels tabel zet in deze tabel het orderID. Op deze manier kunt je meerdere artikelen aan een order toevoegen. Zet de prijs van de orderregel wel vast in die tabel en laat die niet uit artikelen komen. Zodra de prijs veranderd wil je niet dat de prijs van de order die een tijdje geleden geplaatst hebt veranderd.
Verder, keep up de good work :)
Code (php)
1
2
3
4
2
3
4
->0 nul of meer
->| n of meer
-|| n en alleen maar n
-|0 n of nul
->| n of meer
-|| n en alleen maar n
-|0 n of nul
Het ging mij niet zozeer om de tabellen, maar om de opzet van de diagram.
Voorbeeldje iets aangepast op basis van jullie reacties:
Of hoort het zo te zijn ( gelet op de lijntjes en kraaienpootjes en kleurtjes )?
Gewijzigd op 12/01/2015 16:53:37 door E vH
Ziet er mooi uit zo'n diagram, maar ik ben wel benieuwd of iedereen hier eerst zo'n chique diagram maakt, of gewoon even simpelweg een opzetje op papier krabbelt?
Ik weet dat er dingen ontbreken en ook uitgebreid kunnen worden, het betreft de opzet van de diagram.
Welke situatie is nou fout? afbeelding 1 of 2 (hierboven weergegeven ).
Edit:
>>> Ziet er mooi uit zo'n diagram, maar ik ben wel benieuwd of iedereen hier eerst zo'n chique diagram maakt, of gewoon even simpelweg een opzetje op papier krabbelt?
Ik krabbel op papier en begin ergens.
Ik heb een bestaande structuur welke ik bijna 1 op 1 over kan nemen en kan verbeteren, alleen ik heb de PK's en FK's etc nooit doorgevoerd...
Dus ik wilde het eens "anders" doen.
Met zo'n diagram krijg je toch wel een bepaalde database inzicht, lijkt me ergens wel handiger dan op papier... scheelt veel denk werk ;-)
Gewijzigd op 12/01/2015 17:49:05 door E vH
Nou, t ziet er mooi uit hoor :-)
Toevoeging op 12/01/2015 17:54:14:
>> Met zo'n diagram krijg je toch wel een bepaalde database inzicht, lijkt me ergens wel handiger dan op papier... scheelt veel denk werk ;-)
Maar dat moet je toch allemaal "uittekenen"? Dat lijkt me nog veel meer werk :)
(van PK's en FK's heb ik kaas gegeten en dan de "symbolen" begrijpen is ook weer iets wat je moet "snappen".)
Op zich is het een mooi programma, simpel, althans, zo komt het over.
Als je dit snapt, dan is het ook seconden werk om aan te passen, schrijven op papier duurt langer.
Vandaar de vraag of iemand mij kon vertellen of ik goed bezig was, los van de inhoud ;-)
Ik neem aan dat dit een foute manier is ( stel dat ik alle orders van een klant wil ophalen ):
Gewijzigd op 12/01/2015 18:08:08 door E vH
Dat is het mooie er nou van ;-)
Vandaar ook dat ik wil weten of ik goed op weg ben.
orderID in tabel orders zou de PK moeten zijn.
In orderregels is de (nep)sleutel regelID niet nodig. Je maakt van de combinatie orderID, artikelID de primary key. Je zal dan zien dat tussen orderregels en de tabellen orders en artikelen doorgetrokken lijntjes ontstaan.
Dat is het verschil tussen identifying en non-identifying. Identifying wil zeggen dat de FK in de child tabel deel uit maakt van de primary key.
Ger, hoe werkt dit precies? Twee velden, maar één PK? Hoe gebruik je die PK dan in een select query?
Daar heb je een samengestelde sleutel voor nodig: x en y alleen zijn ieder voor zich niet uniek, want één order (x) kan meerdere artikelen bevatten en één artikel (y) kan voorkomen in meerdere orders.
Eigenlijk nooit in een select query, daarom is een pseudo key ook overbodig.
Ja, dat begrijp ik ...
>> Eigenlijk nooit in een select query, daarom is een pseudo key ook overbodig.
Oké, maar stel dat je die orderregel wilt verwijderen? Misschien moet ik de vraag anders stellen. Als je van 2 velden ('abc' en 'def') één PK maakt, ontstaat er dan daadwerkelijk een nieuw PK-veld 'abcdef'?
Het punt bij een n : m-relatie is dat je altijd beide sleutels nodig hebt om één specifieke rij te vinden. Gebruik je slechts één sleutel, dan krijg je meerdere rijen: bij het ordernummer alle artikelen in die order en bij een artikelnummer alle orders met daarin dat ene artikel.
ps: @Ozzie, toevallig al wezen spelen met het programma? ;-)
Ozzie PHP op 12/01/2015 22:13:58:
Oké, maar stel dat je die orderregel wilt verwijderen? Misschien moet ik de vraag anders stellen. Als je van 2 velden ('abc' en 'def') één PK maakt, ontstaat er dan daadwerkelijk een nieuw PK-veld 'abcdef'?
Één van de regels bij normalisatie:
Non key columns are dependent on the key, and nothing but the whole key (, so help me Codd).
In de orderregels tabel zijn alleen aantal en prijs de non-key kolommen, dus wordt niet aan die regel voldaan, dus zou je het artikelnummer en het ordernummer aan de PK op regelID moeten toevoegen. Maar dat is maar gewoon een nietszeggend nummer dus kan je die kolom net zo goed weglaten.
Als je dan een specifieke orderregel wilt verwijderen of bijwerken moet je dus het order_id en artikelnummer controleren.
Gewijzigd op 13/01/2015 11:05:07 door Ger van Steenderen