SQL Antipatterns, Volume 1

Antipattern: Assume You Must Use Files

Conceptually, an image is an attribute in a table. For example, the Accounts table may have a portrait_image column.

Phantom-Files/anti/create-accounts.sql

​CREATE​ ​TABLE​ Accounts (
  account_id      ​SERIAL​ ​PRIMARY​ ​KEY​,
  account_name    ​VARCHAR​(20),
  portrait_image  ​BLOB​
);

Likewise, you can store multiple images of the same type in a dependent table. For example, a bug may have multiple screenshots that illustrate it.

Phantom-Files/anti/create-screenshots.sql

​CREATE​ ​TABLE​ Screenshots (
  image_id          ​SERIAL​ ​NOT​ ​NULL​,
  bug_id            ​BIGINT​ ​UNSIGNED​ ​NOT​ ​NULL​,
  screenshot_image  ​BLOB​,
 caption ...