-- 'attrs' keeps the attributes list in CSV form
CREATE TABLE categories (
  id            INT UNSIGNED NOT NULL UNIQUE AUTO_INCREMENT,
  name          CHAR(255) NOT NULL UNIQUE DEFAULT '',
  parent        INT UNSIGNED NOT NULL DEFAULT '',
  attrs         CHAR(255) NOT NULL DEFAULT '',
  time          TIMESTAMP,
  PRIMARY KEY(id),
  KEY(name(16)),
  KEY(parent)
);

-- _[id] is the id of the category as existent in 'categories' table
CREATE TABLE cat_[id] (
  id            INT UNSIGNED NOT NULL UNIQUE AUTO_INCREMENT,
  name          CHAR(255) NOT NULL UNIQUE DEFAULT '',
  time          TIMESTAMP,
  ... attribute
  ... attribute
  ... attribute
  PRIMARY KEY(id),
  KEY(name(16))
);

-- bloat table to speed up common search patterns
CREATE TABLE items_all (
  id            INT UNSIGNED NOT NULL UNIQUE AUTO_INCREMENT,
  name          CHAR(255) NOT NULL DEFAULT '',
  category      CHAR(255) NOT NULL DEFAULT '',
  cat_id        INT UNSIGNED NOT NULL,
  PRIMARY KEY(id),
  KEY(name(16)),
  KEY(category(16)),
  KEY(cat_id),
);