· 5 years ago · Jul 20, 2020, 03:54 PM
1--new data type table
2CREATE TABLE DATA_TYPE
3(
4 DATA_TYPE_ID BIGSERIAL NOT NULL PRIMARY KEY,
5 TYPE VARCHAR(32) NOT NULL,
6 CODE VARCHAR(8) NOT NULL,
7 DESCRIPTION VARCHAR(128),
8 ROW_STATUS BOOLEAN NOT NULL DEFAULT true,
9 CREATED_BY VARCHAR(256) NOT NULL,
10 CREATED_DATE TIMESTAMPTZ DEFAULT Now() NOT NULL,
11 UPDATED_BY VARCHAR(256),
12 UPDATED_DATE TIMESTAMPTZ
13);
14
15--update institution columns
16ALTER TABLE INSTITUTION
17ADD INSTITUTION_TYPE_ID INTEGER NULL
18ALTER TABLE INSTITUTION
19ADD CONSTRAINT INSTITUTION_TYPE_ID_FKEY
20FOREIGN KEY (INSTITUTION_TYPE_ID)
21REFERENCES DATA_TYPE (DATA_TYPE_ID);
22
23ADD COLUMN CONTACT_NAME TYPE VARCHAR(64);
24ADD COLUMN CONTACT_PHONE TYPE VARCHAR(64);
25ADD COLUMN EMAIL VARCHAR(256);
26ADD COLUMN EMAIL_SUBJECT VARCHAR(256);
27
28--new facility table
29DROP TABLE IF EXISTS "FACILITY";
30CREATE TABLE FACILITY
31(
32 FACILITY_ID BIGSERIAL NOT NULL PRIMARY KEY,
33 NAME VARCHAR(256) NOT NULL UNIQUE,
34 FACILITY_TYPE_ID INTEGER NOT NULL,
35 CONTACT_NAME VARCHAR(64) NULL,
36 CONTACT_PHONE VARCHAR(64) NULL,
37 EMAIL VARCHAR(256) NULL,
38 EMAIL_SUBJECT VARCHAR(256) NULL,
39 INSTITUTION_ID INTEGER NOT NULL,
40 ROW_STATUS BOOLEAN NOT NULL DEFAULT true,
41 CREATED_BY VARCHAR(256) NOT NULL,
42 CREATED_DATE TIMESTAMPTZ DEFAULT Now() NOT NULL,
43 UPDATED_BY VARCHAR(256),
44 UPDATED_DATE TIMESTAMPTZ,
45 FOREIGN KEY (FACILITY_TYPE_ID) REFERENCES DATA_TYPE (DATA_TYPE_ID),
46 FOREIGN KEY (INSTITUTION_ID) REFERENCES INSTITUTION (INSTITUTION_ID)
47);
48
49--new unit table
50DROP TABLE IF EXISTS "UNIT";
51CREATE TABLE UNIT
52(
53 UNIT_ID BIGSERIAL NOT NULL PRIMARY KEY,
54 UNIT_TYPE_ID INTEGER NOT NULL,
55 FACILITY_ID INTEGER NOT NULL,
56 ROW_STATUS BOOLEAN NOT NULL DEFAULT true,
57 CREATED_BY VARCHAR(256) NOT NULL,
58 CREATED_DATE TIMESTAMPTZ DEFAULT Now() NOT NULL,
59 UPDATED_BY VARCHAR(256),
60 UPDATED_DATE TIMESTAMPTZ,
61 FOREIGN KEY (FACILITY_ID) REFERENCES FACILITY (FACILITY_ID)
62 FOREIGN KEY (UNIT_TYPE_ID) REFERENCES DATA_TYPE (DATA_TYPE_ID)
63);
64
65DROP TABLE IF EXISTS "EDU_FAMILY_SURVEY";
66CREATE TABLE EDU_FAMILY_SURVEY
67(
68 EDU_FAMILY_SURVEY_ID BIGSERIAL NOT NULL PRIMARY KEY,
69 HOUSEHOLD_ID VARCHAR(256) NOT NULL UNIQUE,
70 INSTITUTION_ID INTEGER NOT NULL,
71 FACILITY_ID INTEGER NOT NULL,
72 UNIT_ID INTEGER NOT NULL,
73 GENDER_ID INTEGER NULL,
74 RACE_ID INTEGER NULL,
75 ROW_STATUS BOOLEAN NOT NULL DEFAULT true,
76 CREATED_BY VARCHAR(256) NOT NULL,
77 CREATED_DATE TIMESTAMPTZ DEFAULT Now() NOT NULL,
78 UPDATED_BY VARCHAR(256),
79 UPDATED_DATE TIMESTAMPTZ,
80 FOREIGN KEY (INSTITUTION_ID) REFERENCES INSTITUTION (INSTITUTION_ID),
81 FOREIGN KEY (FACILITY_ID) REFERENCES FACILITY (FACILITY_ID),
82 FOREIGN KEY (UNIT_ID) REFERENCES UNIT (UNIT_ID),
83 FOREIGN KEY (GENDER_ID) REFERENCES DATA_TYPE (DATA_TYPE_ID),
84 FOREIGN KEY (RACE_ID) REFERENCES DATA_TYPE (DATA_TYPE_ID)
85);
86
87--populating tables
88
89INSERT INTO DATA_TYPE (TYPE,CODE,DESCRIPTION,CREATED_BY,CREATED_DATE) VALUES ('Institution','HOS','Hospital','System','07/17/2020');
90INSERT INTO DATA_TYPE (TYPE,CODE,DESCRIPTION,CREATED_BY,CREATED_DATE) VALUES ('Institution','SD','School District','System','07/17/2020');
91
92INSERT INTO DATA_TYPE (TYPE,CODE,DESCRIPTION,CREATED_BY,CREATED_DATE) VALUES ('Facility','School','School','System','07/17/2020');
93INSERT INTO DATA_TYPE (TYPE,CODE,DESCRIPTION,CREATED_BY,CREATED_DATE) VALUES ('Unit','Grade','Grade','System','07/17/2020');
94
95INSERT INTO DATA_TYPE (TYPE,CODE,DESCRIPTION,CREATED_BY,CREATED_DATE) VALUES ('Gender','M','Male','System','07/17/2020');
96INSERT INTO DATA_TYPE (TYPE,CODE,DESCRIPTION,CREATED_BY,CREATED_DATE) VALUES ('Gender','F','Female','System','07/17/2020');
97INSERT INTO DATA_TYPE (TYPE,CODE,DESCRIPTION,CREATED_BY,CREATED_DATE) VALUES ('Gender','N','Non-binary','System','07/17/2020');
98INSERT INTO DATA_TYPE (TYPE,CODE,DESCRIPTION,CREATED_BY,CREATED_DATE) VALUES ('Gender','PNA','Prefer not to answer','System','07/17/2020');
99
100INSERT INTO DATA_TYPE (TYPE,CODE,DESCRIPTION,CREATED_BY,CREATED_DATE) VALUES ('Race','AA','African American','System','07/17/2020');
101INSERT INTO DATA_TYPE (TYPE,CODE,DESCRIPTION,CREATED_BY,CREATED_DATE) VALUES ('Race','AP','Asian/Pacific Islander ','System','07/17/2020');
102INSERT INTO DATA_TYPE (TYPE,CODE,DESCRIPTION,CREATED_BY,CREATED_DATE) VALUES ('Race','CW','Caucasian/White','System','07/17/2020');
103INSERT INTO DATA_TYPE (TYPE,CODE,DESCRIPTION,CREATED_BY,CREATED_DATE) VALUES ('Race','NAA','Native American/Alaska Native ','System','07/17/2020');
104INSERT INTO DATA_TYPE (TYPE,CODE,DESCRIPTION,CREATED_BY,CREATED_DATE) VALUES ('Race','MR','Other/Multi-racial ','System','07/17/2020');
105INSERT INTO DATA_TYPE (TYPE,CODE,DESCRIPTION,CREATED_BY,CREATED_DATE) VALUES ('Race','PNA','Prefer not to answer','System','07/17/2020');
106
107INSERT INTO DATA_TYPE (TYPE,CODE,DESCRIPTION,CREATED_BY,CREATED_DATE) VALUES ('Ethnicity','HL','Hispanic/Latino','System','07/17/2020');
108INSERT INTO DATA_TYPE (TYPE,CODE,DESCRIPTION,CREATED_BY,CREATED_DATE) VALUES ('Ethnicity','NHL','Not Hispanic/Latino','System','07/17/2020');
109INSERT INTO DATA_TYPE (TYPE,CODE,DESCRIPTION,CREATED_BY,CREATED_DATE) VALUES ('Ethnicity','PNA','Prefer not to answer','System','07/17/2020');
110
111-- views based on data type
112CREATE OR REPLACE VIEW public.vw_institution_type
113 AS
114 SELECT data_type.data_type_id,
115 data_type.type,
116 data_type.row_status,
117 data_type.code,
118 data_type.description,
119 data_type.created_by,
120 data_type.created_date,
121 data_type.updated_by,
122 data_type.updated_date
123 FROM data_type
124 WHERE type = 'Institution' and row_status=true;
125
126CREATE OR REPLACE VIEW public.vw_facility_type
127 AS
128 SELECT data_type.data_type_id,
129 data_type.type,
130 data_type.row_status,
131 data_type.code,
132 data_type.description,
133 data_type.created_by,
134 data_type.created_date,
135 data_type.updated_by,
136 data_type.updated_date
137 FROM data_type
138 WHERE type = 'Facility' and row_status=true;
139
140CREATE OR REPLACE VIEW public.vw_unit_type
141 AS
142 SELECT data_type.data_type_id,
143 data_type.type,
144 data_type.row_status,
145 data_type.code,
146 data_type.description,
147 data_type.created_by,
148 data_type.created_date,
149 data_type.updated_by,
150 data_type.updated_date
151 FROM data_type
152 WHERE type = 'Unit' and row_status=true;
153
154CREATE OR REPLACE VIEW public.vw_gender_type
155 AS
156 SELECT data_type.data_type_id,
157 data_type.type,
158 data_type.row_status,
159 data_type.code,
160 data_type.description,
161 data_type.created_by,
162 data_type.created_date,
163 data_type.updated_by,
164 data_type.updated_date
165 FROM data_type
166 WHERE type = 'Gender' and row_status=true;
167
168CREATE OR REPLACE VIEW public.vw_race_type
169 AS
170 SELECT data_type.data_type_id,
171 data_type.type,
172 data_type.row_status,
173 data_type.code,
174 data_type.description,
175 data_type.created_by,
176 data_type.created_date,
177 data_type.updated_by,
178 data_type.updated_date
179 FROM data_type
180 WHERE type = 'Race' and row_status=true;
181
182CREATE OR REPLACE VIEW public.vw_ethnicity_type
183 AS
184 SELECT data_type.data_type_id,
185 data_type.type,
186 data_type.row_status,
187 data_type.code,
188 data_type.description,
189 data_type.created_by,
190 data_type.created_date,
191 data_type.updated_by,
192 data_type.updated_date
193 FROM data_type
194 WHERE type = 'Ethnicity' and row_status=true;