Reading SQL Anti-Pattern

Anti-Patterns for Physical DB Design

9. Rounding-Errors

DO NOT USE FLOAT/REAL/DOUBLE PRECISION types

Problems

  • Not accurate
  • Cannot search with =

Solutions

Use NUMERIC/DECIMAL

10. 31 Flavors

Do not define limited value in column definition

e.g.)

-- NO!
CREATE TABLE Bugs (
  status VARCHAR(20) CHECK (status IN ('NEW', 'FIXED', 'xxx'))
);

MySQL has ENUM type, but this causes the same problems. Do not use ENUM.

Problems

  • You have to update CHECK restriction every time when you add/update/delete values.

Solutions

Create reference table

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.

(It depends!)

Problems

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

memo

  • Multiple index must be executed in right order.
  • Selectivity problem
  • MENTOR
    • Measurement
    • Explain
    • Nominate
    • Test
    • Optimize
    • Rebuild
  • Check
    • the slowest query
    • the most frequent query