tokitoki-api/newtoki.sql at master · tokitoki-arrival/tokitoki-api · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
CREATE DATABASE `tokitoki`;
CREATE TABLE `Locations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `location` varchar(255) NOT NULL,
  `createdAt` datetime NOT NULL,
  `updatedAt` datetime NOT NULL,
  PRIMARY KEY (`id`)
);
CREATE TABLE `Properties` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `propertyName` varchar(255) NOT NULL,
  `locationId` int(11) NOT NULL,
  `createdAt` datetime NOT NULL,
  `updatedAt` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `locationId` (`locationId`),
  CONSTRAINT `Properties_ibfk_1` FOREIGN KEY (`locationId`) REFERENCES `Locations` (`id`)
);
CREATE TABLE `Units` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` varchar(255) NOT NULL,
  `propertyId` int(11) NOT NULL,
  `createdAt` datetime NOT NULL,
  `updatedAt` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `propertyId` (`propertyId`),
  CONSTRAINT `Units_ibfk_1` FOREIGN KEY (`propertyId`) REFERENCES `Properties` (`id`)
);
CREATE TABLE `Rooms` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `roomNo` varchar(255) NOT NULL,
  `unitId` int(11) NOT NULL,
  `createdAt` datetime NOT NULL,
  `updatedAt` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `unitId` (`unitId`),
  CONSTRAINT `Rooms_ibfk_1` FOREIGN KEY (`unitId`) REFERENCES `Units` (`id`)
);
CREATE TABLE `Bookings` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `roomId` int(11) NOT NULL,
  `guestName` varchar(255) NOT NULL,
  `checkIn` datetime NOT NULL,
  `checkOut` datetime NOT NULL,
  `status` enum('checked in','not checked in') NOT NULL,
  `createdAt` datetime NOT NULL,
  `updatedAt` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `roomId` (`roomId`),
  CONSTRAINT `Bookings_ibfk_1` FOREIGN KEY (`roomId`) REFERENCES `Rooms` (`id`)
);