-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathPerformance & Alterations.txt
62 lines (52 loc) · 2.52 KB
/
Performance & Alterations.txt
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
Performance Notes
Issues[Update Board]~
Name Conventions; Revised Create Table statements; having duplicate Attribute names for IDs/Name could lead to confusion so I've renamed each set to be relevant to the Table they are in.
FK; Added Foreign Key Column names in each create table statement for indexing purposes later.
FK; Each FK column Starts with the Table name it comes from for reading ease.
Table view displays; When creating views change appearence of column headings to be user-friendly. i.e. not Eqi_Cat but "Equipment Category".
--------------------------------------------------------
Create Table with Constraint example:-
CREATE TABLE `department` (
`Dept_ID` int(3) NOT NULL,
`Name` varchar(15) NOT NULL,
`Floor` varchar(2) NOT NULL,
`Ward` varchar(3) NOT NULL,
`Hospital_ID` varchar(10) NOT NULL,
PRIMARY KEY (`Dept_ID`),
KEY `dept_hosp` (`Hospital_ID`),
CONSTRAINT `dept_hosp` FOREIGN KEY (`Hospital_ID`) REFERENCES `hospital` (`Hospital_ID`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
-------------------------------------------------------------
- Table Specific Alterations:-
Department
1.Moved Floor & Ward to Hospital as they should be parented by Hospital not Dept.
2.Will Join Dept to Hospital via Floor, ward. [Need to check whether joins need to have both columns created in both tables]
3.Hosp_ID will be FK.
Equipment
1.Possible composite key with Id & Barcode.
2.Added Equi_Pers (Equipment|Person) for FK
3.Added Equi_Dept (Equipment|Department) for FK
4.Added Equi_Main_Last_Cleaned (Equipment|Maintenance) for FK
IDs
1.Added User_Pass to table.
2.Need to research encryption for password storage.
3.Changed datatype for Patient to int - hoping can implement trigger for Staff only access by adding constraint for datatype
Maintenance
1. Added Hosp_ID for FK purposes
2. Added Equi_Main_Last_Cleaned (Equipment|Maintenance) for FK
Person
1. Changed names to be table specific
2. Changed First_Name to Forename for better readability
3. ID Constraint; Patient OR Staff [Research possiblity]
4. Research possiblity to add Pats_Hosp_ID constraint checking if inpatient
User
1. If trigger is successful, may not need User table so no amendments at present
----------------------------------------------------------
ALTER/INDEX alterations
1.Updated names of PK's
2.IDs & Person - Check can have 2 unique keys instead of PK for this table.
-----------------------------------------------------------
CONSTRAINT NAMES
Dept_Hosp = Department|Hospital
----------------------------------------------------------
Security Notes