pizzaSQL/database_create_statements.txt at master · ppcharbo/pizzaSQL · 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
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
CUSTOMERS:
CREATE TABLE `customers` (
  `id` int NOT NULL,
  `name` varchar(45) DEFAULT NULL,
  `postal_code` varchar(45) NOT NULL,
  `phone` varchar(45) DEFAULT NULL,
  `adress` varchar(45) NOT NULL,
  PRIMARY KEY (`id`)
)

DESSERTS:
CREATE TABLE `desserts` (
  `id` int NOT NULL,
  `name` varchar(25) DEFAULT NULL,
  `price` double NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

DRINKS:
CREATE TABLE `drinks` (
  `id` int NOT NULL,
  `name` varchar(25) DEFAULT NULL,
  `price` double NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

ITEM TYPES:
CREATE TABLE `item_types` (
  `item_type` int NOT NULL,
  `item_name` varchar(25) NOT NULL,
  PRIMARY KEY (`item_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

MENUITEMS:
CREATE TABLE `menuitems` (
  `id` int NOT NULL,
  `name` varchar(25) DEFAULT NULL,
  `type` int DEFAULT NULL,
  `price` double NOT NULL,
  PRIMARY KEY (`id`),
  CONSTRAINT `type` FOREIGN KEY (`id`) REFERENCES `item_types` (`item_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

RIDERS:
CREATE TABLE `riders` (
  `id` int NOT NULL,
  `name` varchar(45) DEFAULT NULL,
  `last_order` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

ORDERS:
CREATE TABLE `orders` (
  `id` int NOT NULL,
  `idcustomer` int NOT NULL,
  `idrider` int NOT NULL,
  `discountCode` varchar(255) DEFAULT NULL,
  `price` int NOT NULL,
  `delivered` BOOLEAN DEFAULT NULL,
  PRIMARY KEY (`id`,`idcustomer`),
  KEY `idcustomer_idx` (`idcustomer`),
  KEY `idrider_idx` (`idrider`),
  CONSTRAINT `idcustomer` FOREIGN KEY (`idcustomer`) REFERENCES `customers` (`id`),
  CONSTRAINT `idrider` FOREIGN KEY (`idrider`) REFERENCES `riders` (`id`)
) ;

CREATE TABLE `orders_to_customers` (
  `idcustomer` int NOT NULL,
  `idorder` int NOT NULL,
  `idrider` int NOT NULL,
  PRIMARY KEY (`idcustomer`,`idorder`,`idrider`),
  KEY `idorder_idx` (`idorder`),
  KEY `idrider_idx` (`idrider`),
  CONSTRAINT `customer` FOREIGN KEY (`idcustomer`) REFERENCES `customers` (`id`),
  CONSTRAINT `order` FOREIGN KEY (`idorder`) REFERENCES `orders` (`id`),
  CONSTRAINT `rider` FOREIGN KEY (`idrider`) REFERENCES `riders` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;