--- builds tables for the periodicals_db database
---
---
--- ERD:
---
---
---             +-------------+
---             |  Frequency  |
---             |-------------|
---             |   id        |
---             |   frequency |
---             +-------------+     
---                    |
---                    |
---                   /|\
---             +--------------------+               +------------------+
---             |  Journal           |               |  Issue           |
---             |--------------------|               |------------------|
---             |   id               |               |   id             |
---             |   issn             |              /|   journal        |
---             |   title            |---------------|   date           |
---             |   frequency        |              \|   arrival_date   |
---             |   subject          |               |   issue          |
---             |   publisher        |               |   volume         |
---             |   vendor           |               |   supplements    |
---             |   cost             |               |   status(boolean)|
---             |   bought           |               +------------------+
---             |   renewed          |
---             |   subscription_ends|
---             |   cancellable      |
---             |   notes            |
---             +--------------------+
---               \|/            \|/	
---                |              |
---                |              |
--- +-----------------------+   +--------------------+
--- |  Publisher            |   |  Vendor            |
--- |-----------------------|   |--------------------|
--- |   id                  |   |   id               |
--- |   publisher_id        |   |   vendor_id        |
--- |   name                |   |   name             |
--- |   address1            |   |   notes            |
--- |   address2            |   +--------------------+
--- |   notes               |
--- +-----------------------+
---
-------------------------------------------------------------------------

--- The following prevents consistency checks between tables.

CREATE TABLE Frequency (
  id int(11) NOT NULL auto_increment
  , frequency VARCHAR(255) DEFAULT ''
  , PRIMARY KEY (id)
) TYPE=InnoDB;

CREATE TABLE Publisher (
  id int(11) NOT NULL auto_increment
  , publisher_id VARCHAR (255) DEFAULT ''
  , name VARCHAR(255) DEFAULT ''
  , address1 VARCHAR(255) DEFAULT ''
  , address2 VARCHAR(255) DEFAULT ''
  , notes VARCHAR(255) DEFAULT ''
  , PRIMARY KEY (id)
) TYPE=InnoDB;

CREATE TABLE Vendor (
  id int(11) NOT NULL auto_increment
  , vendor_id VARCHAR(255) DEFAULT ''
  , name VARCHAR(255) DEFAULT ''
  , notes VARCHAR(255) DEFAULT ''
  , PRIMARY KEY (id)
)  TYPE=InnoDB;

CREATE TABLE Journal (
  id int(11) NOT NULL auto_increment
  , issn VARCHAR(255) NOT NULL
  , title VARCHAR(255) DEFAULT ''
  , frequency int(11) NOT NULL DEFAULT 0
  , subject VARCHAR(255) DEFAULT ''
  , publisher int(11) NOT NULL DEFAULT 0
  , vendor int(11) NOT NULL DEFAULT 0
  , cost VARCHAR(255) DEFAULT ''
  , bought DATE
  , renewed DATE
  , subscription_ends DATE
  , cancellable VARCHAR(255) DEFAULT ''
  , notes VARCHAR(255) DEFAULT ''
  , PRIMARY KEY (id)
  , KEY (frequency)
  , FOREIGN KEY (frequency) REFERENCES Frequency(id) ON DELETE CASCADE
  , KEY (publisher)
  , FOREIGN KEY (publisher) REFERENCES Publisher(id) ON DELETE CASCADE
  , KEY (vendor)
  , FOREIGN KEY (vendor) REFERENCES Vendor(id) ON DELETE CASCADE
) TYPE=InnoDB;

CREATE TABLE Issue (
  id int(11) NOT NULL auto_increment
  , journal int(11) NOT NULL DEFAULT 0
  , date VARCHAR(255) DEFAULT ''
  , arrival_date DATE
  , issue VARCHAR(255) DEFAULT ''
  , volume VARCHAR(255) DEFAULT ''
  , supplements VARCHAR(255) DEFAULT ''
  , status TINYINT(1) NOT NULL DEFAULT 0
  , PRIMARY KEY (id)
  , KEY (journal)
  , FOREIGN KEY (journal) REFERENCES Journal(id) ON DELETE CASCADE
) TYPE=InnoDB;

--- And now that the tables are set up, turn checking back on.
