Database indeling advies nodig
Nu ben ik een systeem aan het maken voor een bedrijf dat verlichting installeert.
Elke opdracht, dus eigenlijk installatie, is een apart object in de database.
Bij installaties kan ook onderhoud worden uitgevoerd, zoals lampen vervangen of noem maar iets. En er kunnen algemene notities worden gekoppeld aan installaties. De notities (met hun datum) vormen samen een soort logboek bij de installatie.
Een installatieadres is vaak verschillend dan het klantadres.
Eenzelfde klant kan meerdere installaties aan zich gekoppeld krijgen.
Ik wil de grootte van de totale data in de database zo compact mogelijk houden, maar tegelijkertijd wil ik ook de queries om gegevens te selecteren zo snel en licht mogelijk kunnen maken.
Hieronder de twee database modellen die ik tot nu toe heb bedacht:
Optie 1
Optie 2
Waar het grotendeels om gaat is hoe ik adresgegevens opsla. Ik beschouw in het eerste model de adressen als een apart object, dat altijd één op één gekoppeld is aan een ander object dat een adres kan hebben, zoals een klant (factuuradres) en een installatie (als installatieadres). Door de adresgegevens als apart object onder te brengen, bespaart dit velden in de Customers tabel en in de Installations tabel.
Zou een Customer adres hetzelfde zijn als het Installation adres, dan hoeft er maar één rij te worden gemaakt in de Adresses tabel.
Zou je naar model 2 kijken, dan zie je dat ik de adresgegevens niet in een aparte tabel zet, maar gewoon als kolommen opsla in de Customers en Installations tabel. Is het klantadres dan hetzelfde als het installatieadres, dan zijn de waardes van die kolommen dus identiek aan elkaar. Dit kost dan tevens dubbel zo veel data opslag voor de adresgegevens.
Bij ongeveer 90% van de opdrachten is het klantadres en installatieadres hetzelfde. Vandaar dat ik allereerst met model 1 kwam, maar het nadeel hiervan is dat er meer tabellen zijn en meer 'koppelingen'. Daardoor zullen JOIN queries groter worden en mogelijk belastender zijn voor het systeem.
Wat denken jullie dat de beste aanpak zal zijn?
Gewijzigd op 10/08/2020 15:55:15 door Jan terhuijzen
Quote:
Ik wil de grootte van de totale data in de database zo compact mogelijk houden, maar tegelijkertijd wil ik ook de queries om gegevens te selecteren zo snel en licht mogelijk kunnen maken.
Maar de grootte doet er niet toe wanneer je de informatie efficiënt kunt raadplegen. Daarom denk ik dat een goed ontwerp, of beter gezegd, een ontwerp dat toegespitst is op het snel opvragen van gewenste informatie, (zo niet vele malen) belangrijker is dan het super efficiënt opslaan van gegevens. Ik bedoel diskruimte is nou niet direct een beperkende factor meer tegenwoordig?
Het lijkt mij inderdaad wel verstandig om het gebruik van adressen nader te bestuderen.
Quote:
Zou een Customer adres hetzelfde zijn als het Installation adres, dan hoeft er maar één rij te worden gemaakt in de Adresses tabel.
Dat klinkt in eerste instantie logisch, maar wat nu als de klant verhuist (zeg nooit nooit :p)? Dan heeft de installatie ineens op een ander adres plaatsgevonden? :)
Omdat adressen verschillende typen hebben, lijkt het mij in ieder geval belangrijk om onderscheid te maken tussen deze verschilende typen, je hebt nu al bijvoorbeeld:
- adres van de klant (bedrijf of particulier)
- adres van de installatie
- (standaard) factuuradres
en misschien kun je hier nog van afwijken met een custom factuuradres en/of andere adrestypen.
(NB of dit nu inhoudt dat dit tot uiting zou moeten komen als een expliciete adres-type-kolom is een tweede, en laat ik nu nog even in het midden, zie het vervolg)
Misschien is het een idee om, omdat het hier ook / met name om een soort van "historische" gegevens gaat, gewoon (elke) keer kopieën trekt van het origineel, of dat je in ieder geval ergens een letterlijke kopie, zoals die op dat moment geldig is in de tijd, bijhoudt. Deze adressen staan dan redundant opgeslagen, maar dit zorgt dan niet voor problemen wanneer een adres wijzigt omdat niet alles aan elkaar hangt.
Het adres van de installatie staat vast lijkt mij, dus in dat opzicht is het in zekere zin logisch om het adres op te slaan in de installatie-tabel zelf.
Aan de andere kant, je zou je ook kunnen laten sturen in het gebruik en de raadpleging van de adressen. Wat voor queries wil je op adressen loslaten? En hoe vaak komt dit voor? Als je makkelijk en snel in *alle* adressoorten wilt zoeken is het waarschijnlijk handiger om deze in één tabel onder te brengen, maar als dat minder voorkomt, of als je vaker zoekt in één soort adres (factuur, installatie) dan zou je je query of queries ook zo kunnen opbouwen dat deze specifieke tabellen raadplegen; het maakt dan in principe niet zoveel uit dat de adressen niet allemaal op dezelfde plek staan.
Er is voor verschillende soorten aanpakken iets te zeggen, maar ik zou dit voornamelijk sturen door het gebruik, en dan liefst op een manier die zorgt voor eenvoudige, snelle queries. Diskruimte komt wat mij betreft op de tweede plaats.
Gewijzigd op 10/08/2020 16:59:19 door Thomas van den Heuvel
Voor dit probleem heeft een database UDT's (User-Defined Types). Het idee is een beetje als stdClass, je kunt zelf eigenschappen bepalen, en dan:
Code (php)
1
2
2
INSERT INTO "tabel" ("mijntype") VALUES (('eigenschap1', 'eigenschap2', ..));
SELECT ("mijntype").eigenschap1, ("mijntype").eigenschap2, .. FROM "tabel"
SELECT ("mijntype").eigenschap1, ("mijntype").eigenschap2, .. FROM "tabel"
Dan kom je op iets als onderstaande:
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
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
CREATE SEQUENCE "geslacht_id_seq";
CREATE TABLE "geslacht" (
"id" bigint NOT NULL DEFAULT nextval('geslacht_id_seq'::regclass),
"naam" text NOT NULL,
"aanhef" text NOT NULL,
PRIMARY KEY ("id")
) WITH (OIDS=FALSE);
COMMENT ON TABLE "geslacht" IS 'Geslacht';
COMMENT ON COLUMN "geslacht"."id" IS 'ID';
COMMENT ON COLUMN "geslacht"."naam" IS 'Naam';
COMMENT ON COLUMN "geslacht"."aanhef" IS 'Aanhef';
INSERT INTO "geslacht" ("naam", "aanhef") VALUES ('Man', 'Dhr.'), ('Vrouw', 'Mw.');
-- adres als UDT
CREATE TYPE "adres" AS (
"straat" text,
"huisnummer" text, -- ivm. toevoegingen
"postcode" text,
"plaats" text,
"land" text
);
COMMENT ON TYPE "adres" IS 'Adres';
-- particuliere klant
CREATE SEQUENCE "klant_id_seq";
CREATE TABLE "klant" (
"id" bigint NOT NULL DEFAULT nextval('klant_id_seq'::regclass),
"aangemaakt" timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMPTZ,
"geslacht_fk" bigint NOT NULL,
"van" date NULL DEFAULT NULL,
"tot" date NULL DEFAULT NULL,
"achternaam" text NOT NULL,
"tussenvoegsel" text NOT NULL,
"voornamen" text NOT NULL,
"notitie" text NOT NULL DEFAULT E'',
"adres" adres NOT NULL,
PRIMARY KEY ("id"),
CONSTRAINT "klant_geslacht_fkey" FOREIGN KEY ("geslacht_fk")
REFERENCES "geslacht" ("id") MATCH SIMPLE ON UPDATE CASCADE ON DELETE RESTRICT
) WITH (OIDS=FALSE);
COMMENT ON TABLE "klant" IS 'Particuliere klant';
COMMENT ON COLUMN "klant"."id" IS 'Klantnummer'; -- geen dubbele PK klantnr / id
COMMENT ON COLUMN "klant"."aangemaakt" IS 'Aangemaakt';
COMMENT ON COLUMN "klant"."geslacht" IS 'Geslacht';
COMMENT ON COLUMN "klant"."van" IS 'Geboortedatum';
COMMENT ON COLUMN "klant"."tot" IS 'Overleden';
COMMENT ON COLUMN "klant"."achternaam" IS 'Achternaam';
COMMENT ON COLUMN "klant"."tussenvoegsel" IS 'Tussenvoegsel';
COMMENT ON COLUMN "klant"."voornamen" IS 'Voornamen';
COMMENT ON COLUMN "klant"."adres" IS 'Adres';
COMMENT ON COLUMN "klant"."notitie" IS 'Notitie';
-- installatie
CREATE SEQUENCE "installatie_id_seq";
CREATE TABLE "installatie" (
"id" bigint NOT NULL DEFAULT nextval('installatie_id_seq'::regclass),
"klant_fk" bigint NOT NULL,
"aangemaakt" timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
"voltooid" timestamptz NULL DEFAULT NULL,
"notitie" text,
"adres" adres NOT NULL DEFAULT E'',
PRIMARY KEY ("id"),
CONSTRAINT "installatie_klant_fkey" FOREIGN KEY ("klant_fk")
REFERENCES "klant" ("id") MATCH SIMPLE ON UPDATE CASCADE ON DELETE RESTRICT
) WITH (OIDS=FALSE);
CREATE TABLE "geslacht" (
"id" bigint NOT NULL DEFAULT nextval('geslacht_id_seq'::regclass),
"naam" text NOT NULL,
"aanhef" text NOT NULL,
PRIMARY KEY ("id")
) WITH (OIDS=FALSE);
COMMENT ON TABLE "geslacht" IS 'Geslacht';
COMMENT ON COLUMN "geslacht"."id" IS 'ID';
COMMENT ON COLUMN "geslacht"."naam" IS 'Naam';
COMMENT ON COLUMN "geslacht"."aanhef" IS 'Aanhef';
INSERT INTO "geslacht" ("naam", "aanhef") VALUES ('Man', 'Dhr.'), ('Vrouw', 'Mw.');
-- adres als UDT
CREATE TYPE "adres" AS (
"straat" text,
"huisnummer" text, -- ivm. toevoegingen
"postcode" text,
"plaats" text,
"land" text
);
COMMENT ON TYPE "adres" IS 'Adres';
-- particuliere klant
CREATE SEQUENCE "klant_id_seq";
CREATE TABLE "klant" (
"id" bigint NOT NULL DEFAULT nextval('klant_id_seq'::regclass),
"aangemaakt" timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMPTZ,
"geslacht_fk" bigint NOT NULL,
"van" date NULL DEFAULT NULL,
"tot" date NULL DEFAULT NULL,
"achternaam" text NOT NULL,
"tussenvoegsel" text NOT NULL,
"voornamen" text NOT NULL,
"notitie" text NOT NULL DEFAULT E'',
"adres" adres NOT NULL,
PRIMARY KEY ("id"),
CONSTRAINT "klant_geslacht_fkey" FOREIGN KEY ("geslacht_fk")
REFERENCES "geslacht" ("id") MATCH SIMPLE ON UPDATE CASCADE ON DELETE RESTRICT
) WITH (OIDS=FALSE);
COMMENT ON TABLE "klant" IS 'Particuliere klant';
COMMENT ON COLUMN "klant"."id" IS 'Klantnummer'; -- geen dubbele PK klantnr / id
COMMENT ON COLUMN "klant"."aangemaakt" IS 'Aangemaakt';
COMMENT ON COLUMN "klant"."geslacht" IS 'Geslacht';
COMMENT ON COLUMN "klant"."van" IS 'Geboortedatum';
COMMENT ON COLUMN "klant"."tot" IS 'Overleden';
COMMENT ON COLUMN "klant"."achternaam" IS 'Achternaam';
COMMENT ON COLUMN "klant"."tussenvoegsel" IS 'Tussenvoegsel';
COMMENT ON COLUMN "klant"."voornamen" IS 'Voornamen';
COMMENT ON COLUMN "klant"."adres" IS 'Adres';
COMMENT ON COLUMN "klant"."notitie" IS 'Notitie';
-- installatie
CREATE SEQUENCE "installatie_id_seq";
CREATE TABLE "installatie" (
"id" bigint NOT NULL DEFAULT nextval('installatie_id_seq'::regclass),
"klant_fk" bigint NOT NULL,
"aangemaakt" timestamptz NOT NULL DEFAULT CURRENT_TIMESTAMP,
"voltooid" timestamptz NULL DEFAULT NULL,
"notitie" text,
"adres" adres NOT NULL DEFAULT E'',
PRIMARY KEY ("id"),
CONSTRAINT "installatie_klant_fkey" FOREIGN KEY ("klant_fk")
REFERENCES "klant" ("id") MATCH SIMPLE ON UPDATE CASCADE ON DELETE RESTRICT
) WITH (OIDS=FALSE);
Je kunt dit voorbeeld doorzetten voor werknemers.
Mocht je met MySQL werken dan kan je veel dingen niet helaas, zoals gebruik maken van UDT's, datums opslaan met tijzone, en foreign keys gebruiken op engines anders dan InnoDB. Omdat je toch een applicatie nieuw ontwikkelt is gebruik van PostgreSQL de betere en minder tijdrovende optie.