Database schema for sequential art webcomic searchable index application

saindex_database.sql

-- Database: `sa_index`
--
CREATE DATABASE `sa_index` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `sa_index`;

-- --------------------------------------------------------

--
-- Structure for table `arc`
--

CREATE TABLE IF NOT EXISTS `arc` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `title` varchar(200) NOT NULL,
  `description` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Structure for table `arctocomic`
--

CREATE TABLE IF NOT EXISTS `arctocomic` (
  `arcid` bigint(20) NOT NULL,
  `comicid` bigint(20) NOT NULL,
  PRIMARY KEY (`arcid`,`comicid`),
  KEY `comicid` (`comicid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Structure for table `character`
--

CREATE TABLE IF NOT EXISTS `character` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) NOT NULL,
  `nickname` varchar(255) NOT NULL,
  `description` text NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Structure for table `charactertocomic`
--

CREATE TABLE IF NOT EXISTS `charactertocomic` (
  `comicid` bigint(20) NOT NULL,
  `characterid` bigint(20) NOT NULL,
  PRIMARY KEY (`comicid`,`characterid`),
  KEY `characterid` (`characterid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- --------------------------------------------------------

--
-- Structure for table `comic`
--

CREATE TABLE IF NOT EXISTS `comic` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `comicnumber` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `summary` text NOT NULL,
  `url` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `comicnumber` (`comicnumber`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Structure for table `tag`
--

CREATE TABLE IF NOT EXISTS `tag` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Structure for table `tagtocomic`
--

CREATE TABLE IF NOT EXISTS `tagtocomic` (
  `comicid` bigint(20) NOT NULL,
  `tagid` bigint(20) NOT NULL,
  PRIMARY KEY (`comicid`,`tagid`),
  KEY `tagid` (`tagid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


--
-- Constraints for table `arctocomic`
--
ALTER TABLE `arctocomic`
  ADD CONSTRAINT `arctocomic_ibfk_2` FOREIGN KEY (`comicid`) REFERENCES `comic` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `arctocomic_ibfk_1` FOREIGN KEY (`arcid`) REFERENCES `arc` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `charactertocomic`
--
ALTER TABLE `charactertocomic`
  ADD CONSTRAINT `charactertocomic_ibfk_2` FOREIGN KEY (`characterid`) REFERENCES `character` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `charactertocomic_ibfk_1` FOREIGN KEY (`comicid`) REFERENCES `comic` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `tagtocomic`
--
ALTER TABLE `tagtocomic`
  ADD CONSTRAINT `tagtocomic_ibfk_2` FOREIGN KEY (`tagid`) REFERENCES `tag` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `tagtocomic_ibfk_1` FOREIGN KEY (`comicid`) REFERENCES `comic` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;