DatabaseCreation

Опис

Релационата база на податоци е имплементирана во PostgreSQL според претходно дефинираниот ER и релационен модел.

DDL скриптата ги дефинира сите потребни табели, примарни и странски клучеви, ограничувања, проверки и default вредности.

Целосната скрипта е достапна во:

• ddl.sql​

Основни табели

Во почетниот дел од скриптата се креираат помошните табели кои понатаму се користат од останатите ентитети.

Пример за Role:

CREATE TABLE Role (
role_id     SERIAL NOT NULL PRIMARY KEY,
role_name   text   NOT NULL UNIQUE,
description text
);

На сличен начин се дефинирани Permission, Industry, Project_Status, Technology, Rating_Dimension и Subscription_Tier.

Корисници

Сите типови на корисници ги содржат заедничките податоци во табелата "User":

CREATE TABLE "User" (
user_id       SERIAL    NOT NULL PRIMARY KEY,
type          text      NOT NULL
CHECK (type IN ('client', 'vendor', 'management')),
first_name    text      NOT NULL,
last_name     text      NOT NULL,
email         text      NOT NULL UNIQUE,
password_hash text      NOT NULL,
is_active     bool      NOT NULL DEFAULT false,
last_login_at timestamp,
created_at    timestamp NOT NULL DEFAULT NOW(),
updated_at    timestamp NOT NULL DEFAULT NOW()
);

Полето type е ограничено со CHECK, така што може да има само една од трите дозволени вредности.

За конкретните типови на корисници се користат дополнителни табели. На пример:

CREATE TABLE Client_User (
user_id   int4 NOT NULL PRIMARY KEY,
client_id int4 NOT NULL,

CONSTRAINT fk_clientuser_user
FOREIGN KEY (user_id) REFERENCES "User" (user_id)
ON DELETE CASCADE
ON UPDATE CASCADE,

CONSTRAINT fk_clientuser_client
FOREIGN KEY (client_id) REFERENCES Client (client_id)
ON DELETE RESTRICT
ON UPDATE CASCADE
);

На истиот принцип се дефинирани и Vendor_User и Management_User.

Клиенти, продавачи и договори

Клиентот е поврзан со индустријата преку странски клуч:

CREATE TABLE Client (
client_id     SERIAL NOT NULL PRIMARY KEY,
industry_id   int4   NOT NULL,
company_name  text   NOT NULL,
contact_email text   NOT NULL,

CONSTRAINT fk_client_industry
FOREIGN KEY (industry_id) REFERENCES Industry (industry_id)
ON DELETE RESTRICT
ON UPDATE CASCADE
);

Врската помеѓу клиент и vendor е претставена преку договор:

CREATE TABLE Client_Vendor_Contract (
contract_id     SERIAL NOT NULL PRIMARY KEY,
client_id       int4   NOT NULL,
vendor_id       int4   NOT NULL,
contract_number text UNIQUE,
contract_title  text   NOT NULL,
start_date      date   NOT NULL DEFAULT CURRENT_DATE,
end_date        date,
total_value     numeric(10,2),
currency_code   text,
terms_summary   text,
is_active       bool      NOT NULL DEFAULT true,
created_at      timestamp NOT NULL DEFAULT NOW(),
updated_at      timestamp NOT NULL DEFAULT NOW(),

CONSTRAINT chk_cvc_dates
CHECK (end_date IS NULL OR end_date > start_date),

CONSTRAINT fk_cvc_client
FOREIGN KEY (client_id) REFERENCES Client (client_id),

CONSTRAINT fk_cvc_vendor
FOREIGN KEY (vendor_id) REFERENCES Vendor (vendor_id)
);

Со CHECK ограничувањето се спречува крајниот датум на договорот да биде пред почетниот.

Проекти

Секој проект е поврзан со договор и со тековен статус:

CREATE TABLE Project (
project_id   SERIAL        NOT NULL PRIMARY KEY,
contract_id  int4          NOT NULL,
status_id    int4          NOT NULL,
project_name text          NOT NULL,
start_date   date          NOT NULL DEFAULT CURRENT_DATE,
end_date     date,
budget       numeric(10,2) NOT NULL,
created_at   timestamp     NOT NULL DEFAULT NOW(),
updated_at   timestamp     NOT NULL DEFAULT NOW(),

CONSTRAINT chk_project_dates
CHECK (end_date IS NULL OR end_date > start_date),

CONSTRAINT fk_project_contract
FOREIGN KEY (contract_id)
REFERENCES Client_Vendor_Contract (contract_id),

CONSTRAINT fk_project_status
FOREIGN KEY (status_id)
REFERENCES Project_Status (status_id)
);

Технологиите користени на проектите се моделирани со many-to-many релација:

CREATE TABLE Project_Technology (
project_id    int4 NOT NULL,
technology_id int4 NOT NULL,

PRIMARY KEY (project_id, technology_id),

CONSTRAINT fk_projtec_project
FOREIGN KEY (project_id) REFERENCES Project (project_id)
ON DELETE CASCADE,

CONSTRAINT fk_projtec_technology
FOREIGN KEY (technology_id) REFERENCES Technology (technology_id)
ON DELETE RESTRICT
);

Историја на проектите

За промените на буџетот се користи посебна audit табела:

CREATE TABLE Project_Budget_Audit (
audit_id   SERIAL        NOT NULL PRIMARY KEY,
project_id int4          NOT NULL,
old_budget numeric(10,2) NOT NULL,
new_budget numeric(10,2) NOT NULL,
created_at timestamp     NOT NULL DEFAULT NOW(),
updated_at timestamp     NOT NULL DEFAULT NOW(),

CONSTRAINT fk_budgetaudit_project
FOREIGN KEY (project_id) REFERENCES Project (project_id)
);

Промените на статусот се зачувуваат во Project_Status_History, каде покрај проектот и статусот се чува и корисникот кој ја направил промената.

Reviews и оценки

За секој проект може да постои една рецензија:

CREATE TABLE Review (
review_id       SERIAL NOT NULL PRIMARY KEY,
project_id      int4   NOT NULL UNIQUE,
client_user_id  int4   NOT NULL,
review_date     date   NOT NULL DEFAULT CURRENT_DATE,
summary_text    text   NOT NULL,
is_published    bool   NOT NULL DEFAULT false,

CONSTRAINT fk_review_project
FOREIGN KEY (project_id) REFERENCES Project (project_id),

CONSTRAINT fk_review_clientuser
FOREIGN KEY (client_user_id) REFERENCES Client_User (user_id)
);

Оценките се чуваат одделно за секоја димензија:

CREATE TABLE Review_Score (
review_id    int4 NOT NULL,
dimension_id int4 NOT NULL,
score_value  int4 NOT NULL,

PRIMARY KEY (review_id, dimension_id),

CONSTRAINT fk_reviewscore_review
FOREIGN KEY (review_id) REFERENCES Review (review_id)
ON DELETE CASCADE,

CONSTRAINT fk_reviewscore_dimension
FOREIGN KEY (dimension_id)
REFERENCES Rating_Dimension (dimension_id)
);

Dispute систем

При оспорување на review се креира запис во Dispute_Ticket:

CREATE TABLE Dispute_Ticket (
ticket_id                     SERIAL NOT NULL PRIMARY KEY,
assigned_management_user_id   int4 DEFAULT NULL,
review_id                     int4 NOT NULL,
vendor_user_id                int4 NOT NULL,
reason                        text NOT NULL,
is_resolved                   bool NOT NULL DEFAULT false,
filed_at                      date NOT NULL DEFAULT CURRENT_DATE,
resolved_at                   timestamp,
resolution_note               text
);

Табелата е дополнително поврзана со Management_User, Review и Vendor_User преку странски клучеви.

Views

За почесто користените прикази и аналитички податоци се дефинирани PostgreSQL views. Тие ги обединуваат податоците од повеќе поврзани табели и овозможуваат поедноставен пристап до информации за проекти, договори, буџети, оценки, спорови и претплати.

Целосната скрипта е достапна во:

• views.sql​

Проекти по Vendor и Client

Погледот vw_projects_per_vendor ги прикажува сите проекти поврзани со одреден vendor, заедно со статусот, периодот и буџетот:

CREATE OR REPLACE VIEW vw_projects_per_vendor AS
SELECT v.vendor_id,
v.agency_name,
p.project_id,
p.project_name,
ps.status_name,
p.start_date,
p.end_date,
p.budget,
cvc.currency_code
FROM Project p
JOIN Client_Vendor_Contract cvc
ON cvc.contract_id = p.contract_id
JOIN Vendor v
ON v.vendor_id = cvc.vendor_id
JOIN Project_Status ps
ON ps.status_id = p.status_id
ORDER BY v.agency_name, ps.status_name, p.project_name;

Соодветниот поглед за клиентите е vw_projects_per_client:

CREATE OR REPLACE VIEW vw_projects_per_client AS
SELECT c.client_id,
c.company_name,
p.project_id,
p.project_name,
ps.status_name,
p.start_date,
p.end_date,
p.budget,
cvc.currency_code
FROM Project p
JOIN Client_Vendor_Contract cvc
ON cvc.contract_id = p.contract_id
JOIN Client c
ON c.client_id = cvc.client_id
JOIN Project_Status ps
ON ps.status_id = p.status_id
ORDER BY c.company_name, ps.status_name, p.project_name;

Буџети по Vendor и Client

За аналитички приказ на вкупниот број на проекти и нивниот буџет се користат vw_budget_per_vendor и vw_budget_per_client.

CREATE OR REPLACE VIEW vw_budget_per_vendor AS
SELECT v.vendor_id,
v.agency_name,
cvc.currency_code,
COUNT(p.project_id) AS project_count,
SUM(p.budget)       AS total_budget
FROM Project p
JOIN Client_Vendor_Contract cvc
ON cvc.contract_id = p.contract_id
JOIN Vendor v
ON v.vendor_id = cvc.vendor_id
GROUP BY v.vendor_id, v.agency_name, cvc.currency_code
ORDER BY v.agency_name, cvc.currency_code;

CREATE OR REPLACE VIEW vw_budget_per_client AS
SELECT c.client_id,
c.company_name,
cvc.currency_code,
COUNT(p.project_id) AS project_count,
SUM(p.budget)       AS total_budget
FROM Project p
JOIN Client_Vendor_Contract cvc
ON cvc.contract_id = p.contract_id
JOIN Client c
ON c.client_id = cvc.client_id
GROUP BY c.client_id, c.company_name, cvc.currency_code
ORDER BY c.company_name, cvc.currency_code;

Групирањето се врши и според currency_code, со што буџетите во различни валути не се собираат во една вредност.

Клиенти по индустрија

vw_clients_per_industry овозможува приказ на клиентските компании групирани според нивната индустрија:

CREATE OR REPLACE VIEW vw_clients_per_industry AS
SELECT i.industry_id,
i.industry_name,
c.client_id,
c.company_name,
c.contact_email
FROM Client c
JOIN Industry i
ON i.industry_id = c.industry_id
ORDER BY i.industry_name, c.company_name;

Просечна оценка по Vendor

За секој vendor се пресметува просечна оценка од сите Review_Score записи за неговите проекти:

CREATE OR REPLACE VIEW vw_avg_rating_per_vendor AS
SELECT v.vendor_id,
v.agency_name,
COUNT(DISTINCT r.review_id)   AS review_count,
ROUND(AVG(rs.score_value), 2) AS avg_rating
FROM Review_Score rs
JOIN Review r
ON r.review_id = rs.review_id
JOIN Project p
ON p.project_id = r.project_id
JOIN Client_Vendor_Contract cvc
ON cvc.contract_id = p.contract_id
JOIN Vendor v
ON v.vendor_id = cvc.vendor_id
GROUP BY v.vendor_id, v.agency_name
ORDER BY avg_rating DESC NULLS LAST;

COUNT(DISTINCT r.review_id) го прикажува бројот на рецензии, додека AVG ја пресметува просечната вредност од сите оценети димензии.

Нерешени Dispute Tickets

За потребите на dispute системот е креиран vw_unresolved_dispute_tickets, кој ги прикажува само тикетите што сè уште не се решени:

CREATE OR REPLACE VIEW vw_unresolved_dispute_tickets AS
SELECT dt.ticket_id,
dt.filed_at,
dt.reason,
r.review_id,
p.project_id,
p.project_name,
vu.user_id AS filed_by_vendor_user_id,
vu_u.first_name || ' ' || vu_u.last_name
AS filed_by_vendor_user,
mu.user_id AS assigned_management_user_id,
mu_u.first_name || ' ' || mu_u.last_name
AS assigned_to_management_user,
dt.created_at,
dt.updated_at
FROM Dispute_Ticket dt
JOIN Review r
ON r.review_id = dt.review_id
JOIN Project p
ON p.project_id = r.project_id
JOIN Vendor_User vu
ON vu.user_id = dt.vendor_user_id
JOIN "User" vu_u
ON vu_u.user_id = vu.user_id
LEFT JOIN Management_User mu
ON mu.user_id = dt.assigned_management_user_id
LEFT JOIN "User" mu_u
ON mu_u.user_id = mu.user_id
WHERE dt.is_resolved = false
ORDER BY dt.filed_at;

За management корисникот се користи LEFT JOIN, бидејќи нерешен dispute ticket може сè уште да нема доделен management корисник.

Број на проекти по статус

За статистички приказ на состојбата на проектите се користат два погледи кои го пресметуваат бројот на проекти по статус.

За vendor:

CREATE OR REPLACE VIEW vw_project_count_by_status_per_vendor AS
SELECT v.vendor_id,
v.agency_name,
ps.status_id,
ps.status_name,
COUNT(p.project_id) AS project_count
FROM Project p
JOIN Client_Vendor_Contract cvc
ON cvc.contract_id = p.contract_id
JOIN Vendor v
ON v.vendor_id = cvc.vendor_id
JOIN Project_Status ps
ON ps.status_id = p.status_id
GROUP BY v.vendor_id,
v.agency_name,
ps.status_id,
ps.status_name
ORDER BY v.agency_name, ps.status_name;

За client:

CREATE OR REPLACE VIEW vw_project_count_by_status_per_client AS
SELECT c.client_id,
c.company_name,
ps.status_id,
ps.status_name,
COUNT(p.project_id) AS project_count
FROM Project p
JOIN Client_Vendor_Contract cvc
ON cvc.contract_id = p.contract_id
JOIN Client c
ON c.client_id = cvc.client_id
JOIN Project_Status ps
ON ps.status_id = p.status_id
GROUP BY c.client_id,
c.company_name,
ps.status_id,
ps.status_name
ORDER BY c.company_name, ps.status_name;

Овие погледи овозможуваат брзо добивање на бројот на проекти во статуси како Draft, In Progress, Completed и останатите дефинирани статуси.

Договори по Vendor и Client

За приказ на договорите од двете перспективи се користат vw_contracts_per_vendor и vw_contracts_per_client.

CREATE OR REPLACE VIEW vw_contracts_per_vendor AS
SELECT v.vendor_id,
v.agency_name,
cvc.contract_id,
cvc.contract_number,
cvc.contract_title,
c.client_id,
c.company_name AS client_name,
cvc.start_date,
cvc.end_date,
cvc.total_value,
cvc.currency_code,
cvc.is_active
FROM Client_Vendor_Contract cvc
JOIN Vendor v
ON v.vendor_id = cvc.vendor_id
JOIN Client c
ON c.client_id = cvc.client_id
ORDER BY v.agency_name,
cvc.is_active DESC,
cvc.start_date DESC;

CREATE OR REPLACE VIEW vw_contracts_per_client AS
SELECT c.client_id,
c.company_name,
cvc.contract_id,
cvc.contract_number,
cvc.contract_title,
v.vendor_id,
v.agency_name AS vendor_name,
cvc.start_date,
cvc.end_date,
cvc.total_value,
cvc.currency_code,
cvc.is_active
FROM Client_Vendor_Contract cvc
JOIN Client c
ON c.client_id = cvc.client_id
JOIN Vendor v
ON v.vendor_id = cvc.vendor_id
ORDER BY c.company_name,
cvc.is_active DESC,
cvc.start_date DESC;

Со сортирање на is_active DESC, активните договори се прикажуваат пред историските договори.

Vendor претплати

За приказ на активните и историските претплати на агенциите е дефиниран vw_vendor_subscriptions:

CREATE OR REPLACE VIEW vw_vendor_subscriptions AS
SELECT v.vendor_id,
v.agency_name,
st.tier_id,
st.tier_name,
COALESCE(
vs.negotiated_price,
st.list_price,
0
) AS effective_price,
vs.start_date,
vs.end_date,
vs.is_active
FROM Vendor_Subscription vs
JOIN Vendor v
ON v.vendor_id = vs.vendor_id
JOIN Subscription_Tier st
ON st.tier_id = vs.tier_id
ORDER BY v.agency_name,
vs.is_active DESC,
vs.start_date DESC;

Полето effective_price ја користи договорената цена доколку постои. Во спротивно се користи стандардната цена од Subscription_Tier.

На овој начин views обезбедуваат готови прикази за најчестите оперативни и аналитички потреби на системот, без истите JOIN, GROUP BY и агрегатни операции да се повторуваат во секој прашалник.

Полнење со податоци

За тестирање на базата се користи seed_generator.py, кој генерира реалистични податоци и CSV датотеки за табелите.

Генерирањето се извршува со:

• seed_generator.py​

Податоците се генерираат според зависностите помеѓу табелите, така што се почитуваат дефинираните foreign key ограничувања.