This repository has been archived by the owner on Aug 2, 2019. It is now read-only.
-
Notifications
You must be signed in to change notification settings - Fork 0
/
up.sql
92 lines (84 loc) · 3.66 KB
/
up.sql
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
81
82
83
84
85
86
87
88
89
90
91
92
CREATE TABLE IF NOT EXISTS addresses (
id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
house_number BIGINT NOT NULL,
street VARCHAR(255) NOT NULL,
district VARCHAR(255) NOT NULL,
city VARCHAR(255) NOT NULL,
postcode VARCHAR(255) NOT NULL,
PRIMARY KEY ( id )
);
CREATE TABLE IF NOT EXISTS patients (
id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL, -- SERIAL is an alias for BIGINT UNSIGNED AUTOINCREMENT NOT NULL
title VARCHAR(5) NOT NULL, -- A maxium of 5 characters for a title seems reasonable
first_name VARCHAR(255) NOT NULL, -- A maxium of 255 characters for a first_name seems reasonable
last_name VARCHAR(255) NOT NULL, -- A maxium of 255 characters for a last_name seems reasonable
dob DATE NOT NULL,
phone_number VARCHAR(15) NOT NULL, -- 15 is the maximum length in theory: https://en.wikipedia.org/wiki/Telephone_numbering_plan#International_numbering_plan
address_id BIGINT UNSIGNED NOT NULL,
PRIMARY KEY ( id ),
FOREIGN KEY (address_id)
REFERENCES addresses(id)
ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS appointments (
date DATE NOT NULL,
partner VARCHAR(255) NOT NULL,
patient_id BIGINT UNSIGNED, -- this is allowed to be null so that blank appointments can be booked, as specified now done as user 0
start TIME NOT NULL,
duration INT NOT NULL,
complete BOOL NOT NULL,
PRIMARY KEY(date,partner,start),
FOREIGN KEY(patient_id)
REFERENCES patients(id)
ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS treatments (
name VARCHAR(255) NOT NULL,
cost BIGINT UNSIGNED NOT NULL,
treatment_type ENUM('CHECKUP','HYGIENE','REPAIR'),
PRIMARY KEY(name)
);
CREATE TABLE IF NOT EXISTS treatment_applications(
treatment_name VARCHAR(255) NOT NULL,
appointment_date DATE NOT NULL,
appointment_time TIME NOT NULL,
partner VARCHAR(255) NOT NULL,
count INT NOT NULL,
PRIMARY KEY(treatment_name,appointment_date,appointment_time,partner,count),
FOREIGN KEY(treatment_name)
REFERENCES treatments(name)
ON DELETE CASCADE,
FOREIGN KEY(appointment_date,partner,appointment_time)
REFERENCES appointments(date,partner,start)
ON DELETE CASCADE
);
CREATE TABLE IF NOT EXISTS treatment_plans (
name VARCHAR(255) NOT NULL,
cost BIGINT UNSIGNED NOT NULL,
checkups INT UNSIGNED NOT NULL,
hygiene_visits INT UNSIGNED NOT NULL,
repairs INT UNSIGNED NOT NULL,
PRIMARY KEY(name)
);
CREATE TABLE IF NOT EXISTS patient_plans (
patient_id BIGINT UNSIGNED NOT NULL,
plan_name VARCHAR(255) NOT NULL,
used_checkups INT UNSIGNED NOT NULL,
used_hygiene_visits INT UNSIGNED NOT NULL,
used_repairs INT UNSIGNED NOT NULL,
start_date DATE NOT NULL,
PRIMARY KEY(patient_id,plan_name),
FOREIGN KEY(patient_id)
REFERENCES patients(id)
ON DELETE CASCADE,
FOREIGN KEY(plan_name)
REFERENCES treatment_plans(name)
ON DELETE CASCADE
);
INSERT INTO addresses (id,house_number,street,district,city,postcode) VALUES (1,0,'','','','');
INSERT INTO patients (id,title,first_name,last_name,dob,phone_number,address_id) VALUES (1,'','RESERVED','',CURDATE(),'', LAST_INSERT_ID() );
INSERT INTO treatment_plans (name, cost, checkups, hygiene_visits, repairs) VALUES ('Dental Repair', 3600, 2, 4, 2);
INSERT INTO treatment_plans (name, cost, checkups, hygiene_visits, repairs) VALUES ('Maintenance Plan', 1500, 2, 2, 0);
INSERT INTO treatment_plans (name, cost, checkups, hygiene_visits, repairs) VALUES ('NHS', 0, 0, 0, 0);
INSERT INTO treatment_plans (name, cost, checkups, hygiene_visits, repairs) VALUES ('NHS Kids', 0, 2, 2, 6);
INSERT INTO treatment_plans (name, cost, checkups, hygiene_visits, repairs) VALUES ('Oral', 2100, 2, 4, 0);