Miks mitte kasutada klassifikaatori tabeli, välisvõtme veeru ja sellel oleva välisvõtme kitsenduse asemel loendustüüpi veergu?

Sest see:

• vähendab lahenduse porditavust e ülekantavust,
• vähendab lahenduse paindlikkust, 
• suurendab keerukust,
• suurendab PostgreSQL näitel salvestusruumi kasutamist.

Kirjutan järgnevalt sellest pikemalt. Kõik järgnevad näited ja väited põhinevad PostgreSQLil.

Esitan väikese näite, et oleks paremini aru saada, millest jutt käib. Olgu meil andmebaas, kus on vaja registreerida teenuseid ja nende liike. Iga teenus on täpselt ühte liiki. Iga teenuse liik iseloomustab null või rohkemat teenust.

Disain 1

CREATE TABLE Teenuse_liik (teenuse_liik_kood SMALLINT,
nimetus VARCHAR(50) NOT NULL,
CONSTRAINT pk_teenuse_liik PRIMARY KEY (teenuse_liik_kood),
CONSTRAINT ak_teenuse_liik UNIQUE (nimetus));

INSERT INTO Teenuse_liik VALUES (1, 'Hooldus'), (2, 'Arendus');

CREATE TABLE Teenus (teenuse_kood INTEGER,
teenuse_liik_kood SMALLINT NOT NULL,
CONSTRAINT pk_teenus PRIMARY KEY (teenuse_kood),
CONSTRAINT fk_teenus_teenuse_liik FOREIGN KEY (teenuse_liik_kood) REFERENCES Teenuse_liik (teenuse_liik_kood) ON UPDATE CASCADE);

Disain 2

CREATE TYPE teenuse_liik AS ENUM ('Hooldus','Arendus');

CREATE TABLE Teenus (teenuse_kood INTEGER,
teenuse_liik teenuse_liik NOT NULL,
CONSTRAINT pk_teenus PRIMARY KEY (teenuse_kood));

Küsimus on, kas ja miks peaks eelistama disaini 1 disainile 2?

• Ülekantavus
  • Loendustüübi loomise ja selle tüübi haldamise laused pole kirjeldatud SQL standardis. Seega üleminekul teisele andmebaasisüsteemile pole alust õigustatud ootuseks, et uues süsteemis oleks võimalik samasugune loendustüübi kasutamise lahendus.
• Paindlikkus
  • Ka PostgreSQL enese dokumentatsioon ütleb, et loendustüüp on staatiliste väärtuste hulga jaoks. Küsimus on,  kas klassifikaatorite väärtuste hulk on ikkagi staatiline e ajas muutumatu. Elu näitab, et see väärtuste hulk siiski võib ajas muutuda. Need andmed muutuvad palju harvemini kui põhiandmed või tehinguandmed, kuid siiski muutuvad. Näiteks seisundiliikide hulk võib muutuda ettevõtte äriprotsesside ümberkujundamisel, teenuse- ja kaubaliikide hulk võib muutuda ettevõtte äristrateegia muutumisel ja lubatud soo tähiste hulk võib muutuda koos ühiskonna meelsuse ja seadusandluse muutumisega. Teisalt, kui keegi tahab teha loendustüüpi tõeväärtuste (TRUE ja FALSE) esitamiseks, siis see on vale tee, sest PostgreSQL toetab tüüpi BOOLEAN.
  • Loendustüübist ei saa väärtust eemaldada. Loendustüübist väärtuse eemaldamiseks tuleb tüüp kustutada ja uuesti luua, kuid kui tüüp on tabeli veeru puhul kasutusel, siis see on keerukas migreerimise protsess. Klassifikaatori tabelist saab DELETE lausega kustutada klassifikaatori väärtused, mida pole ühegi olemi iseloomustamiseks kasutatud. Klassifikaatori tabelisse saab lisada veeru, mis näitab, kas väärtus on aktiivselt kasutusel või mitte. Muutes klassifikaatori väärtuse mitteaktiivseks, saab keelata selle kasutamise uute olemite iseloomustamisel, samas kui olemasolevaid andmeid see ei mõjuta.
  • Kui mingit tüüpi klassifikaatori korral soovitakse hakata registreerima lisaandmeid (näiteks kirjeldus), siis disain 1 korral tuleb lisada klassifikaatori tabelisse uus veerg, kuid disain 2 korral tuleb luua uus tabel, kus dubleeritakse info loendustüüpi kuuluvate väärtuste tekstiliste nimetuste kohta.
  • Loendustüübi kasutamise korral on klassifikaatori väärtuste tekstiliste nimetuste maksimaalne pikkus määratud PostgreSQL NAMEDATALEN juhtparameetri väärtusega, mis enamasti tähendab, et nimetus saab olla kuni 63 baiti pikk (sõltub serveri sätetest). Klassifikaatori tabelite korral saab vastavalt vajadusel lubada lühemaid nimetusi ja pikemaid nimetusi.
  • Loendustüübi kasutamisel saab luua tüübi, milles olevate väärtuste tekstilised nimed on tühjad stringid või tühikutest koosnevad stringid (CREATE TYPE test AS ENUM ('', ' ', '  ');). Klassifikaatorite tabelite korral saab selliste nimetustega klassifikaatori väärtuste registreerimise CHECK kitsenduste abil keelata.
  • Loendustüüpi kuuluvate väärtuste omavahelise võrdlemise aluseks olev järjekord (kas väärtus "A" on suurem või väiksem kui väärtus "B") on määratud tüübi loomisel esitatud väärtuste järjekorraga. Selle muutmiseks tuleb tüüp kustutada ja uuesti luua (kui tüüp on veeru puhul kasutusel, siis see on keerukas migreerimise protsess). Klassifikaatori tabeli lahenduse korral saab UPDATE lause abil muuta koode ja nimetusi (kui need on võrdluse aluseks) või lisada tabelisse eraldi veeru, milles olevad andmed määravad klassifikaatori väärtuste järjekorra.
  • Loendustüüpi kuuluvaid väärtuseid esitavad tekstilised lipikud on ühes keeles. Kui soovida neid tõlkida, siis ei saa kasutada mitmekeelse sisu SQL-andmebaasis esitamise disainimustreid.
• Keerukus
  • Kui osa klassifikaatoreid on realiseeritud tabelitena ja osa loendustüüpidena, siis on klassifikaatorite andmete esitamiseks kaks eraldi lahendust, ühe asemel.
    • Uue klassifikaatori väärtuse lisamiseks tuleb disain 1 korral kasutada INSERT lauset ja disain 2 korral ALTER TYPE lauset.
    • Klassifikaatori väärtuse tekstilise nimetuse muutmiseks tuleb disain 1 korral kasutada UPDATE lauset ja disain 2 korral ALTER TYPE lauset.
    • Klassifikaatorite väärtuste nimekirja esitamiseks kasutajaliideses tuleb disain 1 korral teha päring andmebaasi põhiosast ja disain 2 korral teha päring süsteemikataloogist või kasutada funktsioone.
      • SELECT enumlabel AS nimetus
FROM pg_enum INNER JOIN pg_type ON pg_enum.enumtypid=pg_type.oid
WHERE pg_type.typname='teenuse_liik';
      • SELECT unnest(enum_range(null::teenuse_liik)) AS nimetus;
    • Eelnev tähendab omakorda, et klassifikaatori väärtuste haldamise tarkvarale ja klassifikaatorite väärtuseid lugevale tarkvarale on vaja anda senisest rohkem õiguseid (turvarisk).
• Salvestusruum
  • PostgreSQL seab igale loendustüüpi kuuluva väärtuse esitusele (tekstilisele lipikule) vastavusse nelja baidi suuruse täisarvu, mida kasutatakse tabelis, kus viidatakse tüüpi kuuluvatele väärtustele. Teiste sõnadega, klassifikaatori väärtusele osutava viite salvestamiseks kulub neli baiti. Klassifikaatori tabelite korral saan näiteks määrata, et kood on tüüpi SMALLINT ja sellisel juhul kulub viite salvestamiseks kaks baiti.
    • INSERT INTO Teenus (teenuse_kood, teenuse_liik) VALUES (1,'Hooldus'), (2, 'Hooldus');

SELECT pg_column_size(teenuse_liik) AS suurus_baitides
FROM Teenus;

Sama probleemi kirjeldatakse B. Karwini SQL antimustrite raamatus antimustris "Kirjelda väärtused veeru definitsioonis" (peatükk 11, "31  maitset"). Seal keskendutakse võimalike väärtuste kirjeldamisele CHECK kitsenduses, kuid mainitakse, et samasugused probleemid tekivad ka loendustüüpide kasutamisel.

Hinda postitust:
1 2 3 4 5

Keskmine hinne : Pole veel hinnanguid!