aKCR2hKj

1drop database IF exists coop_model;
2create database coop_model;
3use coop_model;
4
5create table companies (
6	company_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
7	name VARCHAR(255),
8	type VARCHAR(255),
9	phone VARCHAR(255),
10	country VARCHAR(255),
11	street_first VARCHAR(255),
12	street_second VARCHAR(255),
13	city VARCHAR(255),
14	state VARCHAR(255),
15	code VARCHAR(255)
16);
17
18create table company_contacts (
19	contact_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
20	company_id INT NOT NULL,
21    foreign key(company_id) references companies(company_id)
22);
23create table company_addresses (
24	address_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
25	company_id INT NOT NULL NOT NULL,
26    foreign key(company_id) references companies(company_id)
27);
28
29create table organizations (
30	organization_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
31    company_id INT NOT NULL,
32    foreign key(company_id) references companies(company_id)
33);
34create table locations (
35	location_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL
36);
37
38 create table accounts (
39    account_id INT PRIMARY KEY NOT NULL,
40    foreign key(account_id) references organizations(organization_id)
41);
42
43 create table merchants (
44    merchant_id INT PRIMARY KEY NOT NULL,
45    foreign key(merchant_id) references organizations(organization_id)
46);
47 
48create table organization_contacts (
49	contact_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
50	organization_id INT NOT NULL,
51    foreign key(organization_id) references organizations(organization_id)
52);
53
54create table organization_addresses (
55	address_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
56	organization_id INT NOT NULL,
57    foreign key(organization_id) references organizations(organization_id)
58);
59create table organizations_locations(
60	organization_id INT NOT NULL,
61    location_id INT NOT NULL,
62    foreign key(organization_id) references organizations(organization_id),
63    foreign key(location_id) references locations(location_id)
64 );
65create table users (
66	user_id INT AUTO_INCREMENT PRIMARY KEY,
67	password VARCHAR(255) NOT NULL UNIQUE,
68    first_name VARCHAR(255),
69    last_name VARCHAR(255),
70    phone VARCHAR(255),
71	email VARCHAR(255) NOT NULL UNIQUE,
72	company_id INT,
73    foreign key(company_id) references companies(company_id)
74);
75 create table rights (
76	right_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
77    right_value VARCHAR(255)
78);
79create table users_rights(
80	user_id INT NOT NULL,
81    right_id INT NOT NULL,
82    foreign key(user_id) references users(user_id),
83    foreign key(right_id) references rights(right_id)
84 );
85
86create table roles (
87	role_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
88    type VARCHAR(255),
89    foreign key(role_id) references users(user_id)
90);
91
92create table types_table (
93	type_id INT AUTO_INCREMENT PRIMARY KEY UNIQUE NOT NULL,
94    type_value VARCHAR(255),
95    foreign key(type_id) references users(user_id)
96);
97 
98create table users_locations(
99	user_id INT NOT NULL,
100    location_id INT NOT NULL,
101    foreign key(user_id) references users(user_id),
102    foreign key(location_id) references locations(location_id)
103 );
104 
105 create table users_organizations(
106	user_id INT NOT NULL,
107    organization_id INT NOT NULL,
108    foreign key(user_id) references users(user_id),
109    foreign key(organization_id) references organizations(organization_id)
110 );
111 
112  create table teams (
113	team_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL
114);
115
116  create table teams_locations(
117	team_id INT NOT NULL,
118    location_id INT NOT NULL,
119    foreign key(team_id) references teams(team_id),
120    foreign key(location_id) references locations(location_id)
121 );
122 
123
124   create table teams_users(
125	team_id INT NOT NULL,
126    user_id INT NOT NULL,
127    foreign key(team_id) references teams(team_id),
128    foreign key(user_id) references users(user_id)
129 );
130 
131 create table teams_organizations(
132	team_id INT NOT NULL,
133    organization_id INT NOT NULL,
134    foreign key(team_id) references teams(team_id),
135    foreign key(organization_id) references organizations(organization_id)
136 );
137 
138create table notes (
139	location_id INT NOT NULL,
140    foreign key(location_id) references locations(location_id),
141	note_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL
142);
143
144create table images (
145	 location_id INT NOT NULL,
146    foreign key(location_id) references locations(location_id),
147	image_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL
148);
149
150create table attributes (
151	 location_id INT NOT NULL,
152    foreign key(location_id) references locations(location_id),
153	attribute_id INT AUTO_INCREMENT PRIMARY KEY
154);
155
156create table bookings (
157    location_id INT,
158    foreign key(location_id) references locations(location_id),
159	book_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL
160);
161  create table documents (
162	document_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL
163);
164
165 create table documents_type (
166    doctype_id INT PRIMARY KEY UNIQUE,
167    foreign key(doctype_id) references documents(document_id)
168);
169 
170
171 create table tasks (
172	task_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
173	user_id INT,
174    location_id INT,
175    isCompleted boolean,
176    title VARCHAR(255),
177    createdBy varchar(255),
178    type VARCHAR(255),
179    priority VARCHAR(255),
180    foreign key(location_id) references locations(location_id),
181    foreign key(user_id) references users(user_id)
182);
183
184 create table tasks_teams (
185	task_id INT  NOT NULL,
186    team_id INT NOT NULL,
187    foreign key(team_id) references teams(team_id),
188    foreign key(task_id) references tasks(task_id),
189    PRIMARY KEY(task_id, team_id)
190);
191
192create table tasks_users (
193	task_id INT  NOT NULL,
194    user_id INT NOT NULL,
195    foreign key(user_id) references users(user_id),
196    foreign key(task_id) references tasks(task_id),
197    PRIMARY KEY(user_id, task_id)
198);
199create table documents_locations(
200	document_id INT NOT NULL,
201    location_id INT NOT NULL,
202    foreign key(document_id) references documents(document_id),
203    foreign key(location_id) references locations(location_id)
204 );
205 
206 create table tasks_documents (
207	task_id INT AUTO_INCREMENT PRIMARY KEY NOT NULL,
208    document_id INT NOT NULL,
209    foreign key(document_id) references documents(document_id),
210    foreign key(task_id) references tasks(task_id)
211);
212 
213  create table documents_organizations(
214	document_id INT NOT NULL,
215    organization_id INT NOT NULL,
216    foreign key(document_id) references documents(document_id),
217    foreign key(organization_id) references organizations(organization_id)
218 );
219 
220 create table bills (
221	bill_id INT AUTO_INCREMENT PRIMARY KEY,
222    location_id INT NOT NULL,
223	task_id INT NOT NULL,
224    user_id INT NOT NULL,
225	organization_id INT NOT NULL,
226    foreign key(location_id) references locations(location_id),
227    foreign key(task_id) references tasks(task_id),
228    foreign key(organization_id) references organizations(organization_id),
229    foreign key(user_id) references users(user_id)
230);
231 insert into rights (right_value) VALUES
232 ('create'),
233 ('update'),
234 ('read'),
235 ('delete');