Креирање на базата (DatabaseCreation)

Во оваа фаза се прикажани DDL скриптата за креирање на базата (2А), скриптите за генерирање и полнење на податоците (2Б) и погледите што апликацијата ги користи.

Содржина

1. 2А: DDL скрипта
2. 2Б: Податоци
3. Погледи
   1. Поглед 1: Детали за договор (vw_contract_details)
   2. Поглед 2: Детали за проект (vw_project_details)
   3. Поглед 3: Проекти по софтверска агенција (vw_projects_per_vendor)
   4. Поглед 4: Проекти по клиент (vw_projects_per_client)
   5. Поглед 5: Вкупен буџет по софтверска агенција (vw_budget_per_vendor)
   6. Поглед 6: Вкупен буџет по клиент (vw_budget_per_client)
   7. Поглед 7: Клиенти по индустрија (vw_clients_per_industry)
   8. Поглед 8: Просечна оценка по софтверска агенција (vw_avg_rating_per_vendor)
   9. Поглед 9: Нерешени спорови (vw_unresolved_dispute_tickets)
   10. Поглед 10: Број на проекти по статус по софтверска агенција (vw_project_count_by_status_per_vendor)
   11. Поглед 11: Број на проекти по статус по клиент (vw_project_count_by_status_per_client)
   12. Поглед 12: Договори по софтверска агенција (vw_contracts_per_vendor)
   13. Поглед 13: Договори по клиент (vw_contracts_per_client)
   14. Поглед 14: Претплати на софтверски агенции (vw_vendor_subscriptions)
   15. Поглед 15: Оценки по димензија по софтверска агенција (vw_vendor_rating_by_dimension)
   16. Поглед 16: Промени на буџетот по проект (vw_project_budget_changes)

2А: DDL скрипта

ddl.sql​

Скриптата ги креира 23-те табели од релациониот модел (RelationalModel) по редослед на зависност. Надворешните клучеви, проверките и составеното уникатно ограничување се именувани (fk_*, chk_*, uq_*), така што пораката за грешка при прекршување го именува правилото. Табелата за корисници се вика "User", со наводници, бидејќи USER е резервиран збор.

Табелите се поделени во шест модули:

• Идентитет и пристап: "User" е централната табела за сите корисници со type (client, vendor или management); трите подтипови Client_User, Vendor_User и Management_User го делат нејзиниот примарен клуч и го поврзуваат корисникот со клиентот, софтверската агенција или улогата на која припаѓа. Дозволите се доделуваат на улогите преку Role_Permission.
• Страни: Client (клиентска компанија, припаѓа на една Industry) и Vendor (софтверска агенција).
• Комерцијален дел: Client_Vendor_Contract е договорот меѓу клиент и софтверска агенција; Vendor_Subscription е претплатата на софтверската агенција на платформата по периоди, на ниво од Subscription_Tier (фиксна цена од ценовник или договорна цена).
• Испорака: Project припаѓа на договор, а не директно на клиент и софтверска агенција, и има тековен статус од Project_Status; Project_Technology ги поврзува проектите со Technology.
• Квалитет: Review е рецензијата на клиентот за завршен проект (најмногу една по проект), со оценки од 1 до 5 по секоја димензија од Rating_Dimension во Review_Score; Dispute_Ticket е спорот што корисник на софтверската агенција го поднесува врз рецензија, а го решава менаџмент корисник.
• Историја: Project_Status_History чува по една редица за секоја промена на статус, со точно еден актер (корисник на софтверската агенција или менаџмент корисник); Project_Budget_Audit чува стар и нов буџет за секоја промена на буџетот.

Проверки (CHECK):

• Датуми
  • крајниот датум е по почетниот: договор, претплата, проект
  • спорот не се решава пред да е поднесен
• Износи: сите се позитивни
  • буџет на проект, вредност на договор, цена од ценовник, договорна цена, стар и нов буџет во ревизијата
• Оценка: меѓу 1 и 5
• Формат
  • типот на корисник е една од client, vendor, management
  • е-поштата е во облик име@домен
  • валутата е три големи букви
• Непразен текст
  • име, презиме и лозинка на корисник
  • наслов на договор, име на проект
  • резиме на рецензија, причина и белешка на спор
• Конзистентна состојба
  • промена на статус има точно еден актер
  • решен спор има датум на решавање, доделен менаџмент корисник и белешка; нерешен нема датум
  • ниво на претплата има или цена од ценовник или дозвола за договорна цена, никогаш двете

UNIQUE:

• имињата во табелите со фиксни листи: улоги, дозволи, индустрии, статуси, технологии, димензии, нивоа
• е-поштата на корисникот
• бројот на договорот
• една рецензија по проект
• името на проектот во рамки на договорот (uq_project_contract_name)

Стандардни вредности:

• created_at и updated_at: NOW()
• CURRENT_DATE: почеток на договор, претплата и проект; датум на рецензија; датум на поднесување на спор
• Почетна состојба
  • нов корисник е неактивен (се активира по потврда)
  • нов договор и нова претплата се активни
  • нова рецензија е необјавена
  • нов спор е нерешен и недоделен

Бришење:

• сите надворешни клучеви се ON DELETE RESTRICT
• апликацијата не брише: корисниците се деактивираат, споровите се решаваат, а договорите и претплатите истекуваат

Делумни уникатни индекси. Две правила важат само за дел од редиците и не можат да се запишат како ограничување на ниво на табела:

-- WHERE: уникатноста важи само за редиците што го исполнуваат условот
CREATE UNIQUE INDEX uq_vendorsub_one_active
    ON Vendor_Subscription (vendor_id)
    WHERE is_active;

CREATE UNIQUE INDEX uq_dispute_open_per_vendor_review
    ON Dispute_Ticket (review_id, vendor_user_id)
    WHERE is_resolved = false;

Софтверска агенција има најмногу една активна претплата, а корисник на агенцијата најмногу еден отворен спор за иста рецензија.

2Б: Податоци

seed_generator.py​

load_seed.sql​

Податоците ги генерира скриптата seed_generator.py (Python, библиотека Faker за имиња и текст) како по една CSV датотека за секоја табела; скриптата load_seed.sql ги полни табелите по редослед на зависност. Податоците ги задоволуваат сите ограничувања од шемата и правилата од апликациската логика:

• Авторот на рецензија е корисник на клиентот од договорот на проектот, поднесувачот на спор е корисник на софтверската агенција од истиот договор, а актерот во историјата на статуси е корисник на софтверската агенција на проектот или менаџмент корисник.
• Секој проект минува низ животниот циклус Draft → Under Review → In Progress (→ On Hold) и завршува како Completed или Cancelled, или останува отворен; промените на статус се временски подредени внатре во траењето на проектот, а статусот на проектот е секогаш последната редица од историјата.
• Рецензии постојат само за завршени проекти (Completed или Cancelled), најмногу една по проект и со по една оценка за секоја од десетте димензии.
• Решените спорови имаат датум на решавање, доделен менаџмент корисник и белешка. Рецензија со отворен спор не е објавена.
• Промените на буџетот формираат синџир: стариот буџет на секоја промена е новиот од претходната, а последниот нов буџет е тековниот буџет на проектот.
• Периодите на претплата на една софтверска агенција не се преклопуваат и активен е најмногу последниот; договорна цена има само на нивоата што ја дозволуваат.
• Проектите лежат внатре во периодот на договорот, секој договор има барем еден проект, а секој клиент и секоја софтверска агенција има барем еден корисник. Е-поштата е во облик име.презиме@домен-на-компанијата.

Распределбите се избрани при генерирањето, за реалистични податоци:

• 78,5 % од проектите се Completed и 5,6 % Cancelled; останатите се отворени.
• Рецензијата е напишана 1 до 45 дена по крајот на проектот. Спор е поднесен за 15 % од рецензиите, а од рецензиите без отворен спор се објавени 70 %.
• Секоја софтверска агенција има скриен параметар на квалитет околу кој се влечат оценките на нејзините рецензии, па просечните оценки по агенција се распоредени од 1,66 до 4,61, а не сите околу 3.

Големина на табелите. Број на редици по полнењето:

Табела	Редици
Review_Score	10.000.000
Project_Status_History	5.946.160
Project_Technology	4.197.954
Project	1.400.000
Project_Budget_Audit	1.048.649
Review	1.000.000
Client_Vendor_Contract	250.000
Dispute_Ticket	143.894
"User"	77.000
Client_User	50.000
Vendor_User	25.000
Client	20.000
Vendor_Subscription	5.916
Vendor	5.000
Management_User	2.000
Role_Permission	40
Technology	30
Industry	20
Permission	15
Rating_Dimension	10
Project_Status	6
Role	5
Subscription_Tier	4

Шест табели имаат милион или повеќе редици; вкупно има 24,2 милиони редици и базата зафаќа 2,1 GB.

Погледи

views.sql​

Скриптата креира 16 погледи. Првите два, vw_contract_details и vw_project_details, се помошни: апликацијата не ги чита директно, туку врз нив се градат останатите.

Поглед 1: Детали за договор (vw_contract_details)

Помошен поглед: по една редица за секој договор со имињата на двете страни. Поврзувањето договор–клиент–софтверска агенција е напишано еднаш, а погледите за договори и за проекти го користат.

CREATE OR REPLACE VIEW vw_contract_details AS
SELECT cvc.contract_id,
       cvc.contract_number,
       cvc.contract_title,
       cvc.start_date,
       cvc.end_date,
       cvc.total_value,
       cvc.currency_code,
       cvc.is_active,
       c.client_id,
       c.company_name,
       v.vendor_id,
       v.agency_name
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;

Поглед 2: Детали за проект (vw_project_details)

Помошен поглед: проектот со името на статусот, договорот и двете страни. Од него читаат погледите по софтверска агенција и по клиент, погледите за оценки и погледот за промени на буџет. Оптимизаторот ја вметнува дефиницијата на помошниот поглед во прашалникот што го користи, па помошниот поглед не додава чекор во планот.

CREATE OR REPLACE VIEW vw_project_details AS
SELECT p.project_id,
       p.project_name,
       p.status_id,
       ps.status_name,
       p.start_date,
       p.end_date,
       p.budget,
       cd.contract_id,
       cd.contract_number,
       cd.currency_code,
       cd.client_id,
       cd.company_name,
       cd.vendor_id,
       cd.agency_name
FROM Project p
         -- договорот и двете страни доаѓаат од помошниот поглед
         JOIN vw_contract_details cd ON cd.contract_id = p.contract_id
         JOIN Project_Status ps ON ps.status_id = p.status_id;

Поглед 3: Проекти по софтверска агенција (vw_projects_per_vendor)

Сите проекти на секоја софтверска агенција со статус, датуми, буџет и валута. Апликацијата го чита филтриран по агенција на нејзината контролна табла и на нејзиниот профил, каде што клиентот го разгледува портфолиото на реални проекти пред избор на агенција.

CREATE OR REPLACE VIEW vw_projects_per_vendor AS
SELECT pd.vendor_id,
       pd.agency_name,
       pd.project_id,
       pd.project_name,
       pd.status_name,
       pd.start_date,
       pd.end_date,
       pd.budget,
       pd.currency_code
FROM vw_project_details pd
ORDER BY pd.agency_name, pd.status_name, pd.project_name;

Поглед 4: Проекти по клиент (vw_projects_per_client)

Сите проекти на една клиентска компанија низ сите нејзини договори и софтверски агенции. Апликацијата го чита филтриран по клиент на контролната табла на клиентот; оттука клиентот избира завршен проект за кој ќе остави рецензија.

CREATE OR REPLACE VIEW vw_projects_per_client AS
SELECT pd.client_id,
       pd.company_name,
       pd.project_id,
       pd.project_name,
       pd.status_name,
       pd.start_date,
       pd.end_date,
       pd.budget,
       pd.currency_code
FROM vw_project_details pd
ORDER BY pd.company_name, pd.status_name, pd.project_name;

Поглед 5: Вкупен буџет по софтверска агенција (vw_budget_per_vendor)

Бројот на проекти и збирот на нивните буџети за секоја софтверска агенција, по валута, за да не се собираат износи во различни валути. Финансиски преглед на обемот на работа на агенцијата: увид во сопствените перформанси и, за клиентот, показател за големината на агенцијата.

CREATE OR REPLACE VIEW vw_budget_per_vendor AS
SELECT pd.vendor_id,
       pd.agency_name,
       pd.currency_code,
       COUNT(pd.project_id) AS project_count,
       SUM(pd.budget)       AS total_budget
FROM vw_project_details pd
GROUP BY pd.vendor_id, pd.agency_name, pd.currency_code
ORDER BY pd.agency_name, pd.currency_code;

Поглед 6: Вкупен буџет по клиент (vw_budget_per_client)

Истата агрегација по клиент: вкупниот буџет што клиентот го ангажирал низ сите проекти и договори, по валута. Финансиски преглед на клиентот за следење на потрошениот буџет.

CREATE OR REPLACE VIEW vw_budget_per_client AS
SELECT pd.client_id,
       pd.company_name,
       pd.currency_code,
       COUNT(pd.project_id) AS project_count,
       SUM(pd.budget)       AS total_budget
FROM vw_project_details pd
GROUP BY pd.client_id, pd.company_name, pd.currency_code
ORDER BY pd.company_name, pd.currency_code;

Поглед 7: Клиенти по индустрија (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;

Поглед 8: Просечна оценка по софтверска агенција (vw_avg_rating_per_vendor)

Просечната оценка на секоја софтверска агенција преку сите димензии и бројот на рецензии, само од објавени рецензии, така што оспорена или необјавена рецензија не влијае на јавниот просек. Агенциите без објавена рецензија остануваат во резултатот со нула рецензии. Ова е јавната ранг-листа на софтверски агенции, главната функционалност на платформата; филтриран по агенција, погледот ја дава оценката прикажана на нејзиниот профил.

CREATE OR REPLACE VIEW vw_avg_rating_per_vendor AS
SELECT v.vendor_id,
       v.agency_name,
       COUNT(vr.review_id)                                        AS review_count,
       -- просек само од објавените рецензии: збир на збировите / збир на броевите,
       -- што е истиот просек како AVG, но без COUNT(DISTINCT); NULLIF штити од делење со нула
       ROUND(SUM(vr.score_sum) / NULLIF(SUM(vr.score_cnt), 0), 2) AS avg_rating
FROM Vendor v
         -- LEFT JOIN: агенција без објавена рецензија останува со review_count = 0
         -- потпрашалник: збир и број на оценки по рецензија, само објавени рецензии
         LEFT JOIN (SELECT pd.vendor_id,
                           r.review_id,
                           SUM(rs.score_value)::numeric AS score_sum,  -- ::numeric за децимален просек
                           COUNT(*)                     AS score_cnt
                    FROM Review r
                             JOIN Review_Score rs ON rs.review_id = r.review_id
                             JOIN vw_project_details pd ON pd.project_id = r.project_id
                    WHERE r.is_published = true
                    GROUP BY pd.vendor_id, r.review_id) vr ON vr.vendor_id = v.vendor_id
GROUP BY v.vendor_id, v.agency_name
ORDER BY avg_rating DESC NULLS LAST;

Поглед 9: Нерешени спорови (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,
       dt.vendor_user_id                        AS filed_by_vendor_user_id,
       -- имињата на поднесувачот (vusr) и на доделениот корисник (musr) од две
       -- поврзувања со "User"; musr е NULL додека спорот не е доделен (LEFT JOIN)
       vusr.first_name || ' ' || vusr.last_name AS filed_by_vendor_user,
       dt.assigned_management_user_id,
       musr.first_name || ' ' || musr.last_name AS assigned_to_management_user,
       dt.created_at,
       dt.updated_at,
       ven.vendor_id,
       ven.agency_name
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 Vendor ven ON ven.vendor_id = vu.vendor_id
         JOIN "User" vusr ON vusr.user_id = vu.user_id
         LEFT JOIN "User" musr ON musr.user_id = dt.assigned_management_user_id
WHERE dt.is_resolved = false
ORDER BY dt.filed_at;

Поглед 10: Број на проекти по статус по софтверска агенција (vw_project_count_by_status_per_vendor)

Бројот на проекти на секоја софтверска агенција по статус. Преглед на контролната табла на агенцијата (колку проекти се во тек, завршени, откажани) и, за клиентот, показател колку од проектите на агенцијата навистина се завршуваат.

CREATE OR REPLACE VIEW vw_project_count_by_status_per_vendor AS
SELECT pd.vendor_id,
       pd.agency_name,
       pd.status_id,
       pd.status_name,
       COUNT(pd.project_id) AS project_count
FROM vw_project_details pd
GROUP BY pd.vendor_id, pd.agency_name, pd.status_id, pd.status_name
ORDER BY pd.agency_name, pd.status_name;

Поглед 11: Број на проекти по статус по клиент (vw_project_count_by_status_per_client)

Истото по клиент: состојбата на портфолиото на една клиентска компанија низ сите нејзини софтверски агенции, на контролната табла на клиентот.

CREATE OR REPLACE VIEW vw_project_count_by_status_per_client AS
SELECT pd.client_id,
       pd.company_name,
       pd.status_id,
       pd.status_name,
       COUNT(pd.project_id) AS project_count
FROM vw_project_details pd
GROUP BY pd.client_id, pd.company_name, pd.status_id, pd.status_name
ORDER BY pd.company_name, pd.status_name;

Поглед 12: Договори по софтверска агенција (vw_contracts_per_vendor)

Сите договори на секоја софтверска агенција со името на клиентот, периодот, вредноста и валутата, прво активните, па најновите. Преглед на активните и историските договори на агенцијата; договорот е врската преку која проектите се врзуваат за клиент и агенција.

CREATE OR REPLACE VIEW vw_contracts_per_vendor AS
SELECT cd.vendor_id,
       cd.agency_name,
       cd.contract_id,
       cd.contract_number,
       cd.contract_title,
       cd.client_id,
       cd.company_name AS client_name,
       cd.start_date,
       cd.end_date,
       cd.total_value,
       cd.currency_code,
       cd.is_active
FROM vw_contract_details cd
ORDER BY cd.agency_name, cd.is_active DESC, cd.start_date DESC;

Поглед 13: Договори по клиент (vw_contracts_per_client)

Истото од страна на клиентот: сите негови договори со името на софтверската агенција, за прегледот на активните и историските договори на клиентската компанија.

CREATE OR REPLACE VIEW vw_contracts_per_client AS
SELECT cd.client_id,
       cd.company_name,
       cd.contract_id,
       cd.contract_number,
       cd.contract_title,
       cd.vendor_id,
       cd.agency_name AS vendor_name,
       cd.start_date,
       cd.end_date,
       cd.total_value,
       cd.currency_code,
       cd.is_active
FROM vw_contract_details cd
ORDER BY cd.company_name, cd.is_active DESC, cd.start_date DESC;

Поглед 14: Претплати на софтверски агенции (vw_vendor_subscriptions)

Сите периоди на претплата на секоја софтверска агенција со нивото и ефективната цена: договорната цена ако постои, инаку цената од ценовникот. Преглед на активната и историските претплати на агенцијата и основа за наплатата.

CREATE OR REPLACE VIEW vw_vendor_subscriptions AS
SELECT v.vendor_id,
       v.agency_name,
       st.tier_id,
       st.tier_name,
       -- договорената цена ако постои, инаку цената од ценовникот, инаку 0
       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;

Поглед 15: Оценки по димензија по софтверска агенција (vw_vendor_rating_by_dimension)

За секоја софтверска агенција и димензија на оценување: бројот на оценки, просекот, минимумот и максимумот, само од објавени рецензии. Деталниот приказ зад просечната оценка на профилот на агенцијата (квалитет на код, комуникација, почитување на рокови и останатите димензии), односно повеќедимензионалното оценување по кое платформата се разликува од решенијата со една оценка.

CREATE OR REPLACE VIEW vw_vendor_rating_by_dimension AS
SELECT pd.vendor_id,
       pd.agency_name,
       rd.dimension_id,
       rd.dimension_name,
       COUNT(*)                      AS score_count,
       ROUND(AVG(rs.score_value), 2) AS avg_score,
       MIN(rs.score_value)           AS min_score,
       MAX(rs.score_value)           AS max_score
FROM Review_Score rs
         JOIN Rating_Dimension rd ON rd.dimension_id = rs.dimension_id
         JOIN Review r ON r.review_id = rs.review_id
         JOIN vw_project_details pd ON pd.project_id = r.project_id
WHERE r.is_published = true
GROUP BY pd.vendor_id, pd.agency_name, rd.dimension_id, rd.dimension_name;

Поглед 16: Промени на буџетот по проект (vw_project_budget_changes)

Историјата на буџетот: по една редица за секоја промена на буџетот на проект, со стариот и новиот износ, разликата и процентуалната промена. Се чита филтриран по проект на страницата на проектот или по клиент и софтверска агенција за преглед на сите промени на една страна.

CREATE OR REPLACE VIEW vw_project_budget_changes AS
SELECT pba.audit_id,
       pd.project_id,
       pd.project_name,
       pd.vendor_id,
       pd.client_id,
       pd.currency_code,
       pba.old_budget,
       pba.new_budget,
       pba.new_budget - pba.old_budget AS delta,
       -- процентуална промена; NULLIF штити од делење со нула
       ROUND(100.0 * (pba.new_budget - pba.old_budget) / NULLIF(pba.old_budget, 0), 1) AS pct_change,
       pba.created_at                  AS changed_at
FROM Project_Budget_Audit pba
         JOIN vw_project_details pd ON pd.project_id = pba.project_id;