· 4 years ago · Jul 26, 2021, 07:42 PM
1CREATE TABLE IF NOT EXISTS ngz.roles (
2role_type TEXT PRIMARY KEY
3);
4
5CREATE TABLE IF NOT EXISTS ngz.employees (
6employee_id BIGSERIAL PRIMARY KEY,
7firstname_bg TEXT NOT NULL,
8lastname_bg TEXT NOT NULL,
9firstname_en TEXT NOT NULL,
10lastname_en TEXT NOT NULL,
11phone TEXT NOT NULL UNIQUE,
12email TEXT UNIQUE,
13role_type TEXT NOT NULL,
14
15CONSTRAINT employees_roles_fk
16 FOREIGN KEY(role_type)
17 REFERENCES ngz.roles(role_type)
18);
19
20CREATE TABLE IF NOT EXISTS ngz.addresses (
21address_id BIGSERIAL PRIMARY KEY,
22country TEXT NOT NULL,
23city TEXT NOT NULL,
24post_code TEXT NOT NULL,
25street TEXT NOT NULL
26);
27
28CREATE TABLE IF NOT EXISTS ngz.contacts (
29contact_id BIGSERIAL PRIMARY KEY,
30email TEXT NOT NULL,
31phone TEXT NOT NULL,
32firstname TEXT,
33lastname TEXT
34);
35
36CREATE TABLE IF NOT EXISTS ngz.warehouses (
37warehouse_id BIGSERIAL PRIMARY KEY,
38name TEXT NOT NULL UNIQUE,
39address_id BIGSERIAL NOT NULL,
40contact_id BIGSERIAL NOT NULL,
41
42CONSTRAINT warehouses_addresses_fk
43 FOREIGN KEY(address_id)
44 REFERENCES ngz.addresses(address_id),
45CONSTRAINT warehouses_contacts_fk
46 FOREIGN KEY(contact_id)
47 REFERENCES ngz.contacts(contact_id)
48);
49
50CREATE TABLE IF NOT EXISTS ngz.counterparties (
51counterparty_id BIGSERIAL PRIMARY KEY,
52name TEXT NOT NULL UNIQUE,
53address_id BIGSERIAL NOT NULL,
54post_code TEXT NOT NULL,
55bulstat TEXT NOT NULL UNIQUE,
56invoice_contact_id BIGSERIAL NOT NULL,
57
58CONSTRAINT warehouses_addresses_fk
59 FOREIGN KEY(address_id)
60 REFERENCES ngz.addresses(address_id),
61CONSTRAINT warehouses_contacts_fk
62 FOREIGN KEY(invoice_contact_id)
63 REFERENCES ngz.contacts(contact_id)
64);
65
66CREATE TABLE IF NOT EXISTS ngz.counterparies_orderContacts (
67counterparty_id BIGSERIAL REFERENCES counterparties(counterparty_id),
68order_contact_id BIGSERIAL REFERENCES contacts(contact_id),
69PRIMARY KEY (counterparty_id, order_contact_id)
70);
71
72CREATE TABLE IF NOT EXISTS ngz.currencies (
73currency_type TEXT PRIMARY KEY
74);
75
76CREATE TABLE IF NOT EXISTS ngz.cargo_types (
77cargo_type TEXT PRIMARY KEY
78);
79
80CREATE TABLE IF NOT EXISTS ngz.cargo_forms (
81form_type TEXT PRIMARY KEY
82);
83
84CREATE TABLE IF NOT EXISTS ngz.statuses (
85status_type TEXT PRIMARY KEY
86);
87
88CREATE TABLE IF NOT EXISTS ngz.directions (
89direction_type TEXT PRIMARY KEY
90);
91
92CREATE TABLE IF NOT EXISTS ngz.cargo_details (
93details_id BIGSERIAL PRIMARY KEY,
94direction_type TEXT NOT NULL,
95address TEXT NOT NULL,
96ref_number TEXT NOT NULL,
97date timestamptz NOT NULL,
98
99CONSTRAINT details_directions_fk
100 FOREIGN KEY(direction_type)
101 REFERENCES ngz.directions(direction_type)
102);
103
104
105CREATE TABLE IF NOT EXISTS ngz.cargos (
106cargo_id BIGSERIAL PRIMARY KEY,
107cargo_type TEXT NOT NULL,
108quantity DECIMAL NOT NULL,
109form_type TEXT NOT NULL,
110weight DECIMAL NOT NULL,
111lademetry DECIMAL NOT NULL,
112status_type TEXT NOT NULL,
113cargo_details_id BIGSERIAL NOT NULL,
114
115CONSTRAINT cargos_cargo_types_fk
116 FOREIGN KEY(cargo_type)
117 REFERENCES ngz.cargo_types(cargo_type),
118CONSTRAINT cargos_form_types_fk
119 FOREIGN KEY(form_type)
120 REFERENCES ngz.cargo_forms(form_type),
121CONSTRAINT cargos_statuses_fk
122 FOREIGN KEY(status_type)
123 REFERENCES ngz.statuses(status_type),
124CONSTRAINT cargos_cargo_details_fk
125 FOREIGN KEY(cargo_details_id)
126 REFERENCES ngz.cargo_details(details_id)
127);
128
129CREATE TABLE IF NOT EXISTS ngz.orders (
130order_id BIGSERIAL PRIMARY KEY,
131ref_number TEXT,
132counterparty_id BIGSERIAL NOT NULL,
133contact_id BIGSERIAL NOT NULL,
134amount NUMERIC NOT NULL,
135currency_type TEXT NOT NULL,
136employee_creator_id BIGSERIAL NOT NULL,
137COMMENT TEXT,
138
139CONSTRAINT orders_counterparties_fk
140 FOREIGN KEY(counterparty_id)
141 REFERENCES ngz.counterparties(counterparty_id),
142CONSTRAINT orders_contacts_fk
143 FOREIGN KEY(contact_id)
144 REFERENCES ngz.contacts(contact_id),
145CONSTRAINT orders_employees_fk
146 FOREIGN KEY(employee_creator_id)
147 REFERENCES ngz.employees(employee_id),
148CONSTRAINT orders_currencies_fk
149 FOREIGN KEY(currency_type)
150 REFERENCES ngz.currencies(currency_type)
151);
152
153CREATE TABLE IF NOT EXISTS ngz.orders_collaborators (
154order_id BIGSERIAL REFERENCES orders(order_id),
155collaborator_id BIGSERIAL REFERENCES employees(employee_id),
156PRIMARY KEY (order_id, collaborator_id)
157);
158
159CREATE TABLE IF NOT EXISTS ngz.courses (
160course_id BIGSERIAL PRIMARY KEY,
161referent_number TEXT NOT NULL,
162transporter_counterparty_id BIGSERIAL NOT NULL,
163truck_number TEXT NOT NULL,
164description TEXT NOT NULL,
165delayed_payment_days BIGSERIAL NOT NULL,
166gps_link TEXT NOT NULL,
167status TEXT NOT NULL,
168
169CONSTRAINT courses_counterparties_fk
170 FOREIGN KEY(transporter_counterparty_id)
171 REFERENCES ngz.counterparties(counterparty_id)
172);
173
174CREATE TABLE IF NOT EXISTS ngz.courses_cargos (
175course_id BIGSERIAL REFERENCES courses(course_id),
176cargo_id BIGSERIAL REFERENCES cargos(cargo_id),
177PRIMARY KEY (course_id, cargo_id)
178);
179