· 8 years ago · Feb 02, 2018, 07:54 AM
1CREATE TABLE IF NOT EXISTS `employees`(
2 `id` INT AUTO_INCREMENT PRIMARY KEY,
3 `first_name` VARCHAR(50),
4 `last_name` VARCHAR(50) NOT NULL,
5 `title` VARCHAR(50),
6 `notes` TEXT
7);
8
9INSERT INTO `employees`(`last_name`)
10 VALUES ('Petrov'),
11 ('Ivanov'),
12 ('Dimitrov');
13
14CREATE TABLE IF NOT EXISTS `customers`(
15 `account_number` VARCHAR(50) UNIQUE PRIMARY KEY,
16 `first_name` VARCHAR(50),
17 `last_name` VARCHAR(50) NOT NULL,
18 `phone_number` VARCHAR(50),
19 `emergency_name` VARCHAR(50),
20 `emergency_number` VARCHAR(50),
21 `notes` TEXT
22);
23
24INSERT INTO `customers`(`account_number`,`last_name`)
25 VALUES ('122','Petrov'),
26 ('123412','Ivanov'),
27 ('1212341','Dimitrov');
28
29CREATE TABLE IF NOT EXISTS `room_status`(
30 `id` INT AUTO_INCREMENT PRIMARY KEY,
31 `room_status` VARCHAR(50) NOT NULL,
32 `notes` TEXT
33);
34
35INSERT INTO `room_status`(`room_status`)
36 VALUES ('ready'),
37 ('empty'),
38 ('occupied');
39
40CREATE TABLE IF NOT EXISTS `room_types`(
41 `id` INT AUTO_INCREMENT PRIMARY KEY,
42 `room_type` VARCHAR(50) NOT NULL,
43 `notes` TEXT
44);
45
46INSERT INTO `room_types`(`room_type`)
47 VALUES ('single'),
48 ('double'),
49 ('appartment');
50
51
52CREATE TABLE IF NOT EXISTS `bed_types`(
53 `id` INT AUTO_INCREMENT PRIMARY KEY,
54 `bed_type` VARCHAR(50) NOT NULL,
55 `notes` TEXT
56);
57
58INSERT INTO `bed_types`(`bed_type`)
59 VALUES ('single'),
60 ('double'),
61 ('king');
62
63CREATE TABLE IF NOT EXISTS `rooms`(
64 `room_number` INT(4) NOT NULL PRIMARY KEY,
65 `room_type` VARCHAR(50) NOT NULL,
66 `bed_type` VARCHAR(50) NOT NULL,
67 `rate` DECIMAL NOT NULL,
68 `room_status` VARCHAR(50) NOT NULL,
69 `notes` TEXT
70);
71
72INSERT INTO `rooms`(`room_number`,`room_type`,`bed_type`,`rate`,`room_status`)
73 VALUES (124,'dsfs','dsfs',124.21,'dsfs'),
74 (1214,'dsfs','dsfs',1231.21,'dsfdfsds'),
75 (1224,'dsfs','dsfs',112114.21,'dssdfsfssdffs');
76
77CREATE TABLE IF NOT EXISTS `occupancies`(
78 `id` INT AUTO_INCREMENT PRIMARY KEY,
79 `employee_id` INT,
80 `date_occupied` DATETIME,
81 `account_number` VARCHAR(50),
82 `room_number` INT(4) NOT NULL,
83 `rate_applied` DECIMAL,
84 `phone_charge` DECIMAL,
85 `notes` TEXT
86);
87
88INSERT INTO `occupancies`(`room_number`)
89 VALUES (2),
90 (3),
91 (3);
92
93CREATE TABLE IF NOT EXISTS `payments`(
94 `id` INT AUTO_INCREMENT PRIMARY KEY,
95 `employee_id` INT,
96 `payment_date` DATETIME,
97 `account_number` VARCHAR(50) NOT NULL,
98 `first_date_occupied` DATETIME,
99 `last_date_occupied` DATETIME,
100 `total_days` INT(4),
101 `amount_charged` DECIMAL,
102 `tax_rate` DECIMAL,
103 `tax_amount` DECIMAL,
104 `payment_total` DECIMAL NOT NULL,
105 `notes` TEXT
106);
107
108INSERT INTO `payments`(`account_number`,`payment_total`)
109 VALUES ('1',3),
110 ('2',3),
111 ('1',2);