XZKVmqwc

1SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
2SET time_zone = "+00:00";
3 
4/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
5/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
6/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
7/*!40101 SET NAMES utf8mb4 */;
8 
9CREATE DATABASE IF NOT EXISTS `zalando` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
10USE `zalando`;
11 
12DROP TABLE IF EXISTS `address`;
13CREATE TABLE IF NOT EXISTS `address` (
14  `id` int(11) NOT NULL AUTO_INCREMENT,
15  `street` varchar(127) NOT NULL,
16  `adendum` varchar(255) DEFAULT NULL,
17  `zip` char(5) NOT NULL,
18  `city` varchar(127) NOT NULL,
19  `country` varchar(127) NOT NULL,
20  PRIMARY KEY (`id`)
21) ENGINE=InnoDB DEFAULT CHARSET=utf8;
22 
23DROP TABLE IF EXISTS `endpoint`;
24CREATE TABLE IF NOT EXISTS `endpoint` (
25  `id` int(11) NOT NULL AUTO_INCREMENT,
26  `first_name` varchar(127) NOT NULL,
27  `last_name` varchar(127) NOT NULL,
28  `address_id` int(11) NOT NULL,
29  PRIMARY KEY (`id`),
30  KEY `address_id` (`address_id`)
31) ENGINE=InnoDB DEFAULT CHARSET=utf8;
32 
33DROP TABLE IF EXISTS `endpoint_alternative`;
34CREATE TABLE IF NOT EXISTS `endpoint_alternative` (
35  `id` int(11) NOT NULL AUTO_INCREMENT,
36  `original_address_id` int(11) NOT NULL,
37  `alternative_endpoint_id` int(11) NOT NULL,
38  PRIMARY KEY (`id`),
39  KEY `original_address_id` (`original_address_id`),
40  KEY `alternative_endpoint` (`alternative_endpoint_id`)
41) ENGINE=InnoDB DEFAULT CHARSET=utf8;
42 
43DROP TABLE IF EXISTS `endpoint_comment`;
44CREATE TABLE IF NOT EXISTS `endpoint_comment` (
45  `id` int(11) NOT NULL AUTO_INCREMENT,
46  `endpoint_id` int(11) NOT NULL,
47  `comment` text NOT NULL,
48  `user_id` int(11) NOT NULL,
49  PRIMARY KEY (`id`),
50  KEY `endpoint_id` (`endpoint_id`),
51  KEY `user_id` (`user_id`)
52) ENGINE=InnoDB DEFAULT CHARSET=utf8;
53 
54DROP TABLE IF EXISTS `route`;
55CREATE TABLE IF NOT EXISTS `route` (
56  `id` int(11) NOT NULL AUTO_INCREMENT,
57  `start_address_id` int(11) NOT NULL,
58  `destination_address_id` int(11) DEFAULT NULL,
59  `endpoint_id` int(11) DEFAULT NULL,
60  `dropoff_not_before` time DEFAULT '21:00:00',
61  `dropoff_not_after` time DEFAULT '21:00:00',
62  `pickup_not_before` time DEFAULT '06:00:00',
63  `pickup_not_after` time DEFAULT '20:00:00',
64  `complete` tinyint(4) NOT NULL,
65  PRIMARY KEY (`id`),
66  KEY `start_address_id` (`start_address_id`),
67  KEY `endpoint_id` (`endpoint_id`)
68) ENGINE=InnoDB AUTO_INCREMENT=3456 DEFAULT CHARSET=utf8;
69 
70DROP TABLE IF EXISTS `user`;
71CREATE TABLE IF NOT EXISTS `user` (
72  `id` int(11) NOT NULL AUTO_INCREMENT,
73  `name` varchar(127) NOT NULL,
74  PRIMARY KEY (`id`)
75) ENGINE=InnoDB DEFAULT CHARSET=utf8;
76 
77 
78ALTER TABLE `endpoint`
79  ADD CONSTRAINT `FK_endpoint_address` FOREIGN KEY (`address_id`) REFERENCES `address` (`id`);
80 
81ALTER TABLE `endpoint_alternative`
82  ADD CONSTRAINT `FK_alt_endpoint` FOREIGN KEY (`alternative_endpoint_id`) REFERENCES `endpoint` (`id`),
83  ADD CONSTRAINT `FK_orig_address` FOREIGN KEY (`original_address_id`) REFERENCES `address` (`id`);
84 
85ALTER TABLE `endpoint_comment`
86  ADD CONSTRAINT `FK_comment_endpoint` FOREIGN KEY (`endpoint_id`) REFERENCES `endpoint` (`id`),
87  ADD CONSTRAINT `FK_comment_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`id`);
88 
89ALTER TABLE `route`
90  ADD CONSTRAINT `FK_order_endpoint` FOREIGN KEY (`endpoint_id`) REFERENCES `endpoint` (`id`),
91  ADD CONSTRAINT `FK_start_address` FOREIGN KEY (`start_address_id`) REFERENCES `address` (`id`);
92 
93/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
94/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
95/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;