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 преку странски клучеви.

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

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

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

Error: Failed to load processor bash

No macro or processor named 'bash' found

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