neděle 8. června 2014

SQL Antipatterns

Při procházení knížek z nakladatelství The Pragmatic Bookshelf jsem narazil titul, který mě zaujal svým názvem.

Autor popisuje 24 vzorů na které naráží při používání klasických relačních databází. Problém popíše, navrhne možné řešení a s vědeckou metodičností rozebírá výhody a nevýhody jednotlivých variant.

Mě zaujaly dva vzory, které jsem mohl ihned aplikovat na projektu jdemenato.cz.








Enumerace

Standardním přístupem je enumeraci omezit pomocí omezení na sloupci.
CREATE TABLE Bugs (
   -- other columns
   status VARCHAR(20),
   status VARCHAR(20) check (status in ('NEW', 'IN PROGRESS', 'FIXED'))
); 
Lepším řešením je ale vytáhnout celou enumeraci do nové tabulky, kde hodnota enumerace je přímo primárním klíčem.
CREATE TABLE BugStatus (
   status VARCHAR(20) PRIMARY KEY
);

INSERT INTO BugStatus (status) VALUES ('NEW' ), ('IN PROGRESS' ), ('FIXED' );

CREATE TABLE Bugs (
   -- other columns
   status VARCHAR(20),
   FOREIGN KEY (status) REFERENCES BugStatus(status) ON UPDATE CASCADE
);
Lze se pak se pak krásně dotazovat na všechny hodnoty
SELECT status FROM BugStatus ORDER by status;
Elegantně jdou přidávat nové hodnoty do enumerace i
INSERT INTO BugStatus (status) VALUES ('DUPLICATE' );
a díky ON UPDATE CASCADE i nahrazovat nahrazovat historicky špatně zvolené.
UPDATE BugStatus SET status = 'INVALID' WHERE status = 'BOGUS' ;

Naivní stromy

Běžně jsem se setkal s tím, že stromovou struktura se řeší pomocí reference na sebe sama.
CREATE TABLE Comments (
   comment_id SERIAL PRIMARY KEY,
   parent_id BIGINT UNSIGNED,
   comment TEXT NOT NULL,
   FOREIGN KEY (parent_id) REFERENCES Comments(comment_id)
);
I pokud má vaše databáze podporu pro hierarchické dotazy, není to žádná hitparáda.
WITH CommentTree
   (comment_id, bug_id, parent_id, author, comment, depth)
AS (
   SELECT *, 0 AS depth FROM Comments
   WHERE parent_id IS NULL
UNION ALL
   SELECT c.*, ct.depth+1 AS depth FROM CommentTree ct
   JOIN Comments c ON (ct.comment_id = c.parent_id)
)
SELECT * FROM CommentTree WHERE bug_id = 1234;
Autor popisuje několik variant, jak vazby ukládat. Mě osobně se nejvíce líbilo řešení pomocí closure table - doporučuji k nastudování.