-
-
Notifications
You must be signed in to change notification settings - Fork 2
/
housing_inventory_model.sql
96 lines (89 loc) · 2.93 KB
/
housing_inventory_model.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
93
94
95
96
DROP SCHEMA IF EXISTS housing_inventory cascade;
CREATE SCHEMA housing_inventory;
DROP TABLE IF EXISTS housing_inventory.housing_inventory;
DROP TABLE IF EXISTS housing_inventory.housing_unit_eligibility;
DROP TABLE IF EXISTS housing_inventory.housing_unit_address;
DROP TABLE IF EXISTS housing_inventory.housing_unit_assignment;
CREATE TABLE housing_inventory.housing_inventory
(
housing_unit_id uuid NOT NULL,
date_created timestamp(6) without time zone,
date_updated timestamp(6) without time zone,
inactive boolean,
beds_capacity integer,
beds_current integer,
family_unit boolean,
in_service boolean,
project_id character varying(255),
user_id character varying(255),
vacant boolean,
created_by character varying(255),
last_modified_by character varying(255),
alias_name character varying(250),
schema_year integer,
project_group_code character varying(200),
CONSTRAINT housing_inventory_pkey PRIMARY KEY (housing_unit_id)
)
WITH (
OIDS=FALSE
);
CREATE TABLE housing_inventory.housing_unit_address
(
address_id uuid NOT NULL,
date_created timestamp(6) without time zone,
date_updated timestamp(6) without time zone,
inactive boolean,
address_city character varying(255),
addressline_1 character varying(255),
addressline_2 character varying(255),
address_state character varying(255),
zip_code integer,
housing_inventory_id uuid,
created_by character varying(255),
last_modified_by character varying(255),
CONSTRAINT housing_unit_address_pkey PRIMARY KEY (address_id),
CONSTRAINT fk_housing_unit_id FOREIGN KEY (housing_inventory_id)
REFERENCES housing_inventory.housing_inventory (housing_unit_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
OIDS=FALSE
);
CREATE TABLE housing_inventory.housing_unit_assignment
(
assignment_id uuid NOT NULL,
date_created timestamp(6) without time zone,
date_updated timestamp(6) without time zone,
inactive boolean,
checkout_date timestamp(6) without time zone,
client_id character varying(255),
household_id character varying(255),
housing_inventory_id uuid,
created_by character varying(255),
last_modified_by character varying(255),
CONSTRAINT housing_unit_assignment_pkey PRIMARY KEY (assignment_id),
CONSTRAINT fk_housing_unit_id FOREIGN KEY (housing_inventory_id)
REFERENCES housing_inventory.housing_inventory (housing_unit_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
OIDS=FALSE
);
CREATE TABLE housing_inventory.housing_unit_eligibility
(
eligibility_id uuid NOT NULL,
housing_unit_id uuid,
project_id uuid,
eligibility text,
date_created timestamp(6) without time zone,
date_updated timestamp(6) without time zone,
user_id character varying(255),
created_by character varying(255),
last_modified_by character varying(255),
inactive boolean,
project_group_code character varying(200),
CONSTRAINT housing_eligibility PRIMARY KEY (eligibility_id)
)
WITH (
OIDS=FALSE
);