Skip to content

Instantly share code, notes, and snippets.

@stwalkerster stwalkerster/schema.sql Secret
Created Dec 19, 2016

Embed
What would you like to do?
CREATE TABLE `commander` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `commander_name_uindex` (`name`)
) ENGINE=InnoDB;
CREATE TABLE `incidenttype` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`description` varchar(100) NOT NULL,
`technical` int(11) DEFAULT '0',
`hidden` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
UNIQUE KEY `incidenttype_description_uindex` (`description`)
) ENGINE=InnoDB;
CREATE TABLE `system` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL,
`x` decimal(10,5) DEFAULT NULL,
`y` decimal(10,5) DEFAULT NULL,
`z` decimal(10,5) DEFAULT NULL,
`notes` varchar(1024) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `system_name_uindex` (`name`)
) ENGINE=InnoDB;
CREATE TABLE `waypoint` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`system` int(11) DEFAULT NULL,
`trip` int(11) NOT NULL,
`number` int(11) NOT NULL,
`commander` int(11) DEFAULT NULL,
`reached` int(11) NOT NULL DEFAULT '0',
`sequence` int(11) NOT NULL,
`special` varchar(1) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `waypoint_system_id_fk` (`system`),
KEY `waypoint_trip_id_fk` (`trip`),
KEY `waypoint_commander_id_fk` (`commander`),
CONSTRAINT `waypoint_commander_id_fk` FOREIGN KEY (`commander`) REFERENCES `commander` (`id`),
CONSTRAINT `waypoint_system_id_fk` FOREIGN KEY (`system`) REFERENCES `system` (`id`),
CONSTRAINT `waypoint_trip_id_fk` FOREIGN KEY (`trip`) REFERENCES `trip` (`id`)
) ENGINE=InnoDB;
CREATE TABLE `trip` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL,
`departure` date DEFAULT NULL,
`from` int(11) DEFAULT NULL,
`to` int(11) DEFAULT NULL,
`arrival` date DEFAULT NULL,
`linkpage` varchar(255) DEFAULT NULL,
`linktag` varchar(255) DEFAULT NULL,
`departurestation` varchar(100) DEFAULT NULL,
`arrivalstation` varchar(100) DEFAULT NULL,
`jumprange` decimal(10,5) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `trip_system_id_fk` (`from`),
KEY `trip_system_to_id_fk` (`to`),
CONSTRAINT `trip_system_id_fk` FOREIGN KEY (`from`) REFERENCES `system` (`id`),
CONSTRAINT `trip_system_to_id_fk` FOREIGN KEY (`to`) REFERENCES `waypoint` (`id`)
) ENGINE=InnoDB;
CREATE TABLE `session` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`date` date NOT NULL,
`trip` int(11) NOT NULL,
`nextwaypoint` int(11) DEFAULT NULL,
`currentsystem` int(11) DEFAULT NULL,
`jumpsmade` int(11) DEFAULT NULL,
`madness` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `session_trip_id_fk` (`trip`),
KEY `session_system_id_fk` (`currentsystem`),
KEY `session_waypoint_id_fk` (`nextwaypoint`),
CONSTRAINT `session_system_id_fk` FOREIGN KEY (`currentsystem`) REFERENCES `system` (`id`),
CONSTRAINT `session_trip_id_fk` FOREIGN KEY (`trip`) REFERENCES `trip` (`id`),
CONSTRAINT `session_waypoint_id_fk` FOREIGN KEY (`nextwaypoint`) REFERENCES `waypoint` (`id`)
) ENGINE=InnoDB;
CREATE TABLE `incident` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`session` int(11) NOT NULL,
`type` int(11) NOT NULL,
`delta` int(11) NOT NULL,
PRIMARY KEY (`id`),
KEY `incident_session_id_fk` (`session`),
KEY `incident_incidenttype_id_fk` (`type`),
CONSTRAINT `incident_incidenttype_id_fk` FOREIGN KEY (`type`) REFERENCES `incidenttype` (`id`),
CONSTRAINT `incident_session_id_fk` FOREIGN KEY (`session`) REFERENCES `session` (`id`)
) ENGINE=InnoDB;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.