SWDB/sql/01_structure.sql

DROP DATABASE IF EXISTS sw;
CREATE DATABASE sw CHARACTER SET utf8 COLLATE utf8_bin;

SET NAMES utf8;

CONNECT sw;

CREATE TABLE monster_base (
  id   INT          PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(128) NOT NULL
);

CREATE TABLE monster (
  id      INT          PRIMARY KEY AUTO_INCREMENT,
  name    VARCHAR(128) NOT NULL,
  name_aw VARCHAR(128) NOT NULL,
  element VARCHAR(32)  NOT NULL,
  stars   INT          NOT NULL DEFAULT 2,
  base    INT          REFERENCES monster_base.id
);

CREATE TABLE fusion (
  target       INT NOT NULL REFERENCES monster.id,
  target_stars INT NOT NULL,
  source       INT NOT NULL REFERENCES monster.id,
  source_stars INT NOT NULL,
  source_level INT NOT NULL,
  PRIMARY KEY (target, source)
);

CREATE TABLE monster_collection (
  id      INT     PRIMARY KEY AUTO_INCREMENT,
  monster INT     NOT NULL REFERENCES monster.id,
  stars   INT     NOT NULL DEFAULT 2,
  level   INT     NOT NULL DEFAULT 1,
  awaken  BOOLEAN NOT NULL DEFAULT 0
);