Repository navigation
Expand file tree
/
Copy pathall_tables.sql
More file actions
163 lines (162 loc) · 4.8 KB
/
Copy pathall_tables.sql
File metadata and controls
163 lines (162 loc) · 4.8 KB
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
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
CREATE TABLE log
(
lid serial primary key,
"File_Name" character varying(20),
"Sheet_Name" character varying(20),
"Date" character varying(25)
);
CREATE TABLE login
(
"Fname" character varying(20),
"Lname" character varying(20),
"Phone" character varying(15),
"Email" character varying(100),
"Password" character varying(100),
"Code" character varying(100),
"Flag" character varying(1)
);
CREATE TABLE person_details
(
pid serial primary key,
lid int8,
FOREIGN KEY(lid) REFERENCES log(lid),
sal character varying(10),
fname character varying(20),
lname character varying(20),
phone character varying(100),
email character varying(100),
mobile character varying(20),
dob character varying(20),
anniversary character varying(20),
inserted_at character varying(25),
updated_at character varying(25)
);
CREATE TABLE source_details
(
sid serial primary key,
src_name character varying(200),
src_desc character varying(200),
inserted_at character varying(25),
updated_at character varying(25)
);
CREATE TABLE company_details
(
cmpid serial primary key,
pid int8,
sid int8,
FOREIGN KEY(pid) REFERENCES person_details(pid),
FOREIGN KEY(sid) REFERENCES source_details(sid),
cmp_name character varying(200),
designation character varying(200),
fax character varying(40),
inserted_at character varying(25),
updated_at character varying(25)
);
CREATE TABLE address_details
(
aid serial primary key,
pid int8,
cmpid int8,
FOREIGN KEY(pid) REFERENCES person_details(pid),
FOREIGN KEY(cmpid) REFERENCES company_details(cmpid),
c_addr1 character varying(500),
c_addr2 character varying(500),
c_addr3 character varying(500),
city character varying(100),
pincode character varying(25),
country character varying(50),
inserted_at character varying(25),
updated_at character varying(25)
);
CREATE TABLE category_details
(
catid serial primary key,
cat_name character varying(200),
inserted_at character varying(25),
updated_at character varying(25)
);
CREATE TABLE person_source
(
psid serial primary key,
pid int8 REFERENCES person_details(pid),
sid int8 REFERENCES source_details(sid),
inserted_at character varying(25),
updated_at character varying(25)
);
CREATE TABLE person_category
(
pcid serial primary key,
pid int8 REFERENCES person_details(pid),
catid int8 REFERENCES category_details(catid),
inserted_at character varying(25),
updated_at character varying(25)
);
CREATE TABLE book_event_details
(
bookid serial primary key,
book_event character varying(25)
);
CREATE TABLE art_event_details
(
artid serial primary key,
art_event character varying(25)
);
CREATE TABLE food_promo_details
(
foodid serial primary key,
food_promo character varying(25)
);
CREATE TABLE alcohol_pairing_details
(
alcid serial primary key,
alcohol_pairing character varying(25)
);
CREATE TABLE fund_raiser_details
(
fundid serial primary key,
fund_raiser character varying(25)
);
CREATE TABLE fashion_event_details
(
fashionid serial primary key,
fashion_event character varying(25)
);
CREATE TABLE sports_event_details
(
sportsid serial primary key,
sports_event character varying(25)
);
CREATE TABLE vip_event_details
(
vipid serial primary key,
vip_event character varying(25)
);
INSERT INTO book_event_details("book_event") VALUES('yes');
INSERT INTO book_event_details("book_event") VALUES('no');
INSERT INTO art_event_details("art_event") VALUES('yes');
INSERT INTO art_event_details("art_event") VALUES('no');
INSERT INTO food_promo_details("food_promo") VALUES('yes');
INSERT INTO food_promo_details("food_promo") VALUES('no');
INSERT INTO alcohol_pairing_details("alcohol_pairing") VALUES('yes');
INSERT INTO alcohol_pairing_details("alcohol_pairing") VALUES('no');
INSERT INTO fund_raiser_details("fund_raiser") VALUES('yes');
INSERT INTO fund_raiser_details("fund_raiser") VALUES('no');
INSERT INTO fashion_event_details("fashion_event") VALUES('yes');
INSERT INTO fashion_event_details("fashion_event") VALUES('no');
INSERT INTO sports_event_details("sports_event") VALUES('yes');
INSERT INTO sports_event_details("sports_event") VALUES('no');
INSERT INTO vip_event_details("vip_event") VALUES('yes');
INSERT INTO vip_event_details("vip_event") VALUES('no');
CREATE TABLE person_event
(
peid serial primary key,
pid int8 REFERENCES person_details(pid),
bookid int8 REFERENCES book_event_details(bookid),
artid int8 REFERENCES art_event_details(artid),
foodid int8 REFERENCES food_promo_details(foodid),
alcid int8 REFERENCES alcohol_pairing_details(alcid),
fundid int8 REFERENCES fund_raiser_details(fundid),
fashionid int8 REFERENCES fashion_event_details(fashionid),
sportsid int8 REFERENCES sports_event_details(sportsid),
vipid int8 REFERENCES vip_event_details(vipid)
);