SQL

PostgreSQL in table inheritance

ponedeljek, 19. marec 2007

Naletel sem na problem, ko sem moral imeti eno tabelo, recimo artiklov, vendar naj bi ti artikli imeli različne lastnosti, saj veste, hladilniki imajo ene lastnosti, štedilniki druge, pečice spet tretje, pa saj razumemo, kajne? Table inheritance v PostgreSQL bi kaj lahko rešil ta moj problem, sem ugotovil, pa poglejmo, če ga je res.

Kreirajmo najprej nekaj tabel naše namišljene spletne trgovine:

CREATE TABLE artikli (
id serial PRIMARY KEY,
ime text,
opis text,
cena money
);
CREATE TABLE artikli_vrsta_1 (
lastnost_1_1 text,
lastnost_1_2 text,
PRIMARY KEY (id)
) INHERITS (artikli);
CREATE TABLE artikli_vrsta_2 (
lastnost_2_1 integer,
lastnost_2_2 bool,
PRIMARY KEY (id)
) INHERITS (artikli);
CREATE TABLE artikli_vrsta_3 (
lastnost_3_1 text,
lastnost_3_2 text,
lastnost_3_3 integer,
lastnost_3_4 date,
PRIMARY KEY (id)
) INHERITS (artikli);

Artikle različice ena bomo dodajali v tabelo artikli_vrsta_1, različice dva v tabelo artikli_vrsta_2, različice tri v tabelo artikli_vrsta_3, še vedno pa bomo imeli dostopen seznam vseh artiklov s cenami v tabeli artikli.

Po želji dodajmo nekaj artiklov vsake vrste v pripadajočo tabelo, da se bomo lahko malo igrali s podatki.

Poskusimo:

SELECT p.relname, a.id, a.ime, a.opis, a.cena
FROM artikli a, pg_class p
WHERE p.oid = a.tableoid;

Dobimo nekaj takega:

relname |id|ime |opis |cena
---------------+--+---------+--------------+-------
artikli_vrsta_1| 1|Artikel 1|Opis artikla 1|$100.00
artikli_vrsta_1| 2|Artikel 2|Opis artikla 2| $20.00
artikli_vrsta_2| 3|Artikel 3|Opis artikla 3| $15.00
artikli_vrsta_2| 5|Artikel 5|Opis artikla 5| $45.00
artikli_vrsta_3| 4|Artikel 4|Opis artikla 4| $85.00

V koloni relname imamo še podatek o dejanski tabeli, v kateri se artikel nahaja, tako da lahko enostavno naredimo še eno poizvedbo za pridobitev ostalih podatkov.

Seveda lahko izvedemo še razna filtriranja po posameznih lastnostih znotraj skupine artiklov in podobno, omejitev je skoraj samo naša domišljija.

Do tukaj vse super, kaj pa če želim ustvarit še eno tabelo s podatki, ki so skupni vsem artiklom, vendar v tem primeru želim relacijo 1:M? Poglejmo:

CREATE TABLE artikli_dodatne_lastnosti (
id serial PRIMARY KEY,
artikel_id integer REFERENCES artikli (id),
lastnost_1 text,
lastnost_2 text
);

Poskusimo artiklu "Artikel 1" dodati eno teh dodatnih lastnosti:

INSERT INTO artikli_dodatne_lastnosti
VALUES (DEFAULT, 1, 'Lastnost 1', 'Lastnost 2');

Napaka!

ERROR: insert or update on
table "artikli_dodatne_lastnosti" violates foreign key
constraint "artikli_dodatne_lastnosti_artikel_id_fkey"
DETAIL: Key (artikel_id)=(1) is not present in table "artikli".

No, super, kaj pa zdaj? Nič, po nekaj urah študiranja sem obupal, ne gre. Vsaj ne s table inheritance in ne, če želim ohranit foreign key constraint, kar pa želim. Lahko se seveda motim, rešitve sprejemam na komentarje. Problem sem sicer rešil, ampak na povsem drug način. O tem pa čez nekaj dni.

1.
20. marec 2007, 20:07

Po mojem je lepše in enostavneje narediti tabelo artikli, potem pa tabele artikli_vrsta_1, artikli_vrsta_2,... samo z dodatnimi polji glede na artikle in jih nato povezati 1:1 z artikli.

2.
21. marec 2007, 00:57

David, imaš seveda prav, vendar je po svoje tudi bolj elegantno delat samo en INSERT namesto dveh, ali celo več, ko malo bolj razvejimo vrste artiklov.

Predstavljaj si še naslednje:
artikli
artikli_vrsta_1
artikli_vrsta_1_1
artikli_vrsta_1_1_1
artikli_vrsta_1_1_2
artikli_vrsta_1_2
artikli_vrsta_1_3
artikli_vrsta_2
artikli_vrsta_3

in tako dalje. Kaj kmalu pridemo do treh, štirih INSERTOV, lahko tudi več, kaj ni lepše pisati en INSERT, sistem pa naj poskrbi za konsistenco baze?

Kot vedno, zadevo razložim na enostavnejšem primeru zaradi lažjega razumevanja.

3.
22. marec 2007, 20:06

Se vsekakor strinjam, da so prednosti, če uporabiš inheritance. Sem hotel poveda, da če ne gre, se da seveda tudi drugače. Razen v primeru v tvojem odgovoru. Takrat se začne trpljenje... :)

4.
22. marec 2007, 20:51

Čez nekaj dni bom objavil svojo rešitev tega problema, ki je zelo podobna tvoji, le da reši še problem večkratnih insertov.

5.
23. marec 2007, 15:27

U, zelo me zanima.