Anti-Patterns for Physical DB Design
DO NOT USE
FLOAT/REAL/DOUBLE PRECISION types
- Not accurate
- Cannot search with
10. 31 Flavors
Do not define limited value in column definition
-- NO! CREATE TABLE Bugs ( status VARCHAR(20) CHECK (status IN ('NEW', 'FIXED', 'xxx')) );
ENUM type, but this causes the same problems. Do not use
- You have to update
CHECKrestriction every time when you add/update/delete values.
CREATE TABLE BugStatus ( status VARCHAR(20) PRIMARY KEY, active ENUM('INACTIVE', 'ACTIVE') NOT NULL DEFAULT 'ACTIVE' -- or BOOLEAN value );
11. Phantom Files
Anti-Pattern to save file such as image file outside of the database.
When you save file path in database and file itself out of database,
- When you delete the path from the DB, you have to delete the file itself from application code.
- Cannot rollback
- Access right
12. Index Shotgun
- Multiple index must be executed in right order.
- Selectivity problem
- the slowest query
- the most frequent query