· 6 years ago · Dec 13, 2019, 11:38 PM
1create table if not exists accomodations
2(
3 id integer not null
4 constraint accomodations_pk
5 primary key,
6 name text,
7 host_id integer
8 constraint accomodations_hosts_id_fk
9 references hosts,
10 description text,
11 space text,
12 latitude numeric,
13 longitude numeric,
14 price integer,
15 amenities text,
16 location_id integer
17 constraint accomodations_neighbourhoods_id_fk
18 references neighbourhoods,
19 features_id integer
20 constraint accomodations_common_features_id_fk
21 references common_features
22);
23
24alter table accomodations owner to postgres;
25
26create trigger accomodations_upd
27 before delete
28 on accomodations
29 for each row
30 execute procedure update_accomodations();
31
32
33_______________________________________________________________________________________________________
34
35
36create table if not exists common_features
37(
38 property_type text,
39 room_type text,
40 beds numeric,
41 bathrooms numeric,
42 accommodates integer,
43 id integer not null
44 constraint common_features_pk
45 primary key
46);
47
48alter table common_features owner to postgres;
49
50create trigger features_upd
51 before delete
52 on common_features
53 for each row
54 execute procedure update_features();
55
56_______________________________________________________________________________________________________
57
58create table if not exists connections
59(
60 visitor_id integer
61 constraint connections_visitors_id_fk
62 references visitors,
63 listing_id integer
64 constraint connections_accomodations_id_fk
65 references accomodations
66);
67
68alter table connections owner to postgres;
69
70_______________________________________________________________________________________________________
71
72
73create table if not exists hosts
74(
75 id integer not null
76 constraint hosts_pk
77 primary key,
78 host_name text,
79 host_since text,
80 host_about text
81);
82
83alter table hosts owner to postgres;
84
85create trigger hosts_upd
86 before delete
87 on hosts
88 for each row
89 execute procedure update_hosts();
90
91create trigger neighbourhoods_upd
92 before delete
93 on hosts
94 for each row
95 execute procedure update_neighbourhoods();
96
97
98_______________________________________________________________________________________________________
99
100
101create table if not exists neighbourhoods
102(
103 neighborhood text,
104 city text,
105 suburb text,
106 id integer not null
107 constraint neighbourhoods_pk
108 primary key
109);
110
111alter table neighbourhoods owner to postgres;
112
113_______________________________________________________________________________________________________
114
115
116create table if not exists connections
117(
118 visitor_id integer
119 constraint connections_visitors_id_fk
120 references visitors,
121 listing_id integer
122 constraint connections_accomodations_id_fk
123 references accomodations
124);
125
126alter table connections owner to postgres;
127
128_______________________________________________________________________________________________________
129
130create table if not exists visitors
131(
132 id integer not null
133 constraint visitors_pk
134 primary key,
135 name text
136);
137
138alter table visitors owner to postgres;