Monday, 22.08.2011 – Zidar
Kad govorimo o projektovanju baze podataka, nekako uvek mislimo na to kako postaviti tabele i veze izmedju njih. Govoreci SQL jeziokom, oslanjamo se uglavnom na FOREIGN KEY ogranicenja. Cesto zaboravljamo da postoje i druga ogranicenja koja daju isti efekat a ponekad pojednostavljuju konacno resenja. Pokazacemo na jednom primeru kako se moze upotrebiti CHECK constraint umesto FOREIGN KEY, kao i kako se ista situacija razresava na ‘klasican nacin’ – upotrebo FOREIGN KEY ogranicenja.
Recimo da imamo tabelu Kupci, gde cuvamo podatke – o kupcima. Kupaca imamo dve vrste, pravna i fizicka lica. Pravna lica (preduzeca) imaju jednu vrstu identifikaciong broja, a fizicka lica imaju maticni broj gradjana. ID broj za pravna lica je osmocifreni broj, a maticni broj ima 18 cifara. Krenimo od ovakvog resenja:
IF OBject_ID(‘Kupci’) IS NOT NULL DROP TABLE Kupci
;
CREATE TABLE Kupci
(
KupacID varchar(12) NOT NULL PRIMARY KEY
, ImeKUPCA varchar(50) NOT NULL
, CONSTRAINT KupacID_samo_cifre CHECK (KupacID NOT LIKE ‘%[^0-9]%’)
, CONSTRAINT KupacID_Duzina CHECK (LEN (KupaciD) = 8 OR LEN(KupacID) = 18)
)
;
Zanemarimo za trenutak cinjenicu da smo u jednu kolonu strpali dve razlicite stvari. ID preduzeca i maticni broj gradjana nisu isto te smo time smo pravilo 1NF. Molim da zanemarite ovu cinjenicu. Nesto drugo zelimo da pokazemo.
Postavili smo ogranicenje da se u varchar kolonu mogu uneti samo cifre. Takodje smo ogranicili duzinu KupacID na 8 ili 18 karaktera, ili ili, ostale duzine nisu dozvoljene.
Da vidimo sta nismo pokrili ogranicenjima. Nista nas sprecava da za pravno lice unesemo 18 cifara a za fizicko lice 8 cifara. Pokusajmo da unesemo nekoliko redova. Prva dve komande padaju, zbog CHECK ogranicenja. Druge dve prolaze, i treba da prodju. Poslednje dve prolaze, a ne treba da prodju. Kompanija Big Brother Co, prolazi sa maticnim brojem gradjana, a gradjanin Donald Duck prolazi sa id brojem preduzeca.
INSERT INTO Kupci VALUES (‘1XX23’,‘KUpac A’);
INSERT INTO Kupci VALUES (‘123’,‘Export-Import Co’);
INSERT INTO Kupci VALUES (‘12345678’,‘Export-Import Co’);
INSERT INTO Kupci VALUES (‘123456789012345678’,‘Marko Markovic’);
INSERT INTO Kupci VALUES (‘987654321012345678’,‘Big Brother Co.’);
INSERT INTO Kupci VALUES (‘87654321’,‘Donald Duck’);
Zato bi trebalo da znamo koji kupac je pravno a koji je fizicko lice. Dodajmo kolonu za novi podatak.
IF OBject_ID(‘Kupci’) IS NOT NULL DROP TABLE Kupci
;
CREATE TABLE Kupci
(
KupacID varchar(18) NOT NULL PRIMARY KEY
, ImeKUPCA varchar(50) NOT NULL
, TipKupca char(1) NOT NULL CHECK ( TipKupca IN (‘P’,‘F’))
, CONSTRAINT KupacID_samo_cifre CHECK (KupacID NOT LIKE ‘%[^0-9]%’)
, CONSTRAINT KupacID_Duzina CHECK (LEN (KupaciD) = 8 OR LEN(KupacID) = 18)
)
;
Sva ogranicenja iz prvog pokusaja i dalje vaze, ali sad mozemo da idemo dalje. Sada imamo dve kolone koje su medjusobno zavisne. Ako je TipKupca = ‘P’ onda KupacID ima 8 cifara. Ako je TipKupca = ‘F’ onda KupacID ima 18 cifara. Sve ostalo nije dozvoljeno. Imamo dve varijable, a =TipKupca i b = LEN(KupacID), sa po dve moguce vrednosti. To nam daje 4 moguce kombinacije, od kojih su neke tacne a neke ne. Napravimo tablicu:
|
a=TipKupca
|
b=LEN(KupacID)
|
Rezultat
|
|
P
|
8
|
Dozvoljeno
|
|
P
|
18
|
Nije dozvoljeno
|
|
F
|
8
|
Nije Dozvoljeno
|
|
F
|
18
|
Dozvoljeno
|
Od cetiri kombinacije, dve su dozvoljene a dve nisu. Ogranicenje bi se moglo napisati ovako:
ALTER TABLE Kupci
ADD CONSTRAINT KupacID_Duzina_PF
CHECK (1 =
CASE
WHEN TipKupca = ‘P’ AND LEN(KupacID) = 8 THEN 1
WHEN TipKupca = ‘P’ AND LEN(KupacID) = 18 THEN 0
WHEN TipKupca = ‘F’ AND LEN(KupacID) = 8 THEN 0
WHEN TipKupca = ‘F’ AND LEN(KupacID) = 18 THEN 1
END
)
;
Testirajmo sada tabelu:
INSERT INTO Kupci VALUES (‘1XX23’,‘KUpac A’,‘P’);
INSERT INTO Kupci VALUES (‘123’,‘Export-Import Co’,‘F’);
INSERT INTO Kupci VALUES (‘12345678’,‘Export-Import Co’,‘P’);
INSERT INTO Kupci VALUES (‘123456789012345678’,‘Marko Markovic’,‘F’);
INSERT INTO Kupci VALUES (‘987654321012345678’,‘Big Brother Co.’,‘P’);
INSERT INTO Kupci VALUES (‘87654321’,‘Donald Duck’,‘F’);
Prve dve komande padaju, iz istog razloga kao i pre. Druge dve prolaze. Trece dve ne prolaze, zbog dodatnog uslova. Upotreba CASE funkcije u CHECK uslovu omogucila nam je da bolje kontrolisemo ulazne podatke. Zbog jednostavnosti postavili smo uslov po duzini niske karaktera a u realnosti mogli smo da ukljucimo sta god hocemo, pa cak i pozivanje korisnickih funkcija. Ako ste ikad napisali korisnicku funkciju koja proverava maticni broj gradjana, mozete je upotrebiti i ovde.
Sigurno je neko primetio da se zadatak moze resiti tipizacijom – razbijanjem tabele Kupci na vise tabela. Imali bismo tri tabele, Kupci, Kupci_P, Kupci_F. Tabele Kupci_P i Kupci_F sluze da razdvojimo tabelu Kupci na dva tipa, pravna i fizicka lica. Tabele Kupci_P i Kupci_F nazvacemo ‘subtip tabele’. Za svaku od subtip tabela morali smo da definisemo ceo skup ogranicenja.
IF OBject_ID(‘Kupci_F’) IS NOT NULL DROP TABLE Kupci_F
;
IF OBject_ID(‘Kupci_P’) IS NOT NULL DROP TABLE Kupci_P
;
IF OBject_ID(‘Kupci’) IS NOT NULL DROP TABLE Kupci
;
CREATE TABLE Kupci
(
KupacID int NOT NULL PRIMARY KEY
, ImeKUPCA varchar(50) NOT NULL
, TipKupca char(1) NOT NULL CHECK ( TipKupca IN (‘P’,‘F’))
, UNIQUE (KupacID, TipKupca)
)
;
CREATE TABLE Kupci_P
( KupacID int NOT NULL UNIQUE
, TipKupca char(1) NOT NULL CHECK (TipKupca = ‘P’)
, BrojPreduzeca char(8) NOT NULL PRIMARY KEY
, CHECK (BrojPreduzeca NOT LIKE ‘%[^0-9]%’)
, CHECK (LEN (KupaciD) = 8)
, CONSTRAINT fk_Kupci_P
FOREIGN KEY (KupacID, TipKupca)
REFERENCES Kupci (KupacID, TipKupca)
)
;
CREATE TABLE Kupci_F
(
KupacID int NOT NULL PRIMARY KEY
, TipKupca char(1) NOT NULL CHECK (TipKupca = ‘F’)
, MaticniBroj char(8) NOT NULL UNIQUE
, CHECK (MaticniBroj NOT LIKE ‘%[^0-9]%’)
, CHECK (LEN (KupaciD) = 18)
, CONSTRAINT fk_Kupci_F
FOREIGN KEY (KupacID, TipKupca)
REFERENCES Kupci (KupacID, TipKupca)
)
;
Primetite da je tip kupca ponovljen u subtip tabelama. Ovo je vazno jer neke knjige ne pominju da se tip entitete prenosi u subtip tabele. Tip kupca u tabeli kupci mora da se slaze sa tipom kupca u subtip tabelama. U subtip tabelama tip moze imati tacno jednu vrednost. Ovo sprecava da istog kupca upisemo u dve subtip tabele.
Metod sa subtipovima jeste relaciono ispravniji. Nema mesanja dve vrste podataka u istoj kolni. Medjutim, uvek postoji sansa da se u tabelu Kupci unese kupac, a da iz nekog razloga ne prenesemo kupca u subtip tabelu. Ima razlike i u pisanju kverija. Ako imate jednu tabelu, lako je povuci sve kupce iz nje. Sa tri tabele, pokusajte da ispisete sve kupce i njihove ID brojeve. Sta je jednostavnije:
SELECT * FROM Kupci — metod jedne tabele
ili
— metod tri tabele
SELECT
K.KupacID
, K.ImeKUPCA
, K.TipKupca
, BrojKupca = COALESCE (P.BrojPreduzeca, K.MaticniBroj)
LEFT JOIN Kupci_P AS P ON P.KupacID = K.KupacID
LEFt JOIN Kupci_F AS F ON F.KUpacID = K.KupacID
U metodu tri tabele imate KupacID i BrojKupca. Koja od te dve vrednosti u stvari odredjuej kupca?
Ispada da ponekad malkice denormalizacije moze biti i korisna stvar. Uz uslov da postavite sva potrebna ogranicenja.
Ima situacija kada se absolutno mora pribeci podtipovima. U nasem primeru subtip tabele imaju identicnu strukturu. Nije uvek tako. Ako bi za fizcka lica cuvali datum rodjena a za preduzeca ime direktora ili osobu za kontakt, onda nam ne ginu tri tabele. I tada bi mogli da koristimo jednu tabelu i napisemo uslove za validaciju nekolko kolona, ali bi to bilo mnogo komplikovanije nego u nasem primeru.
Objavljeno u: Best practice, Modelovanje podataka, MS SQL Server, Uncategorized | Ukupno komentara: 6 »
