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í.