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