Обзор базы данных stroy

Описание

В проекте используется учебная база данных stroy, которая описывает фрагмент информационной системы компании, занимающейся строительством и проектированием.

База данных содержит сведения:

– о сотрудниках и физических лицах;

– о должностях и организационной структуре;

– о контрагентах и их адресах;

– о проектах и руководителях проектов;

– о сотрудниках, задействованных в проектах;

– о платежах по проектам;

– о типах работ, заказываемых контрагентами;

– об окладах сотрудников и истории их изменения.

Эта структура позволяет решать как базовые задачи выборки и агрегации, так и более сложные аналитические задачи: расчёт накопительных итогов, обход иерархий, анализ платежей, формирование отчетных представлений.

Схема данных

В проекте используется схема:

set search\_path to stroy;

Все запросы выполняются внутри схемы stroy.

Основные группы таблиц

1. География и адреса

Эти таблицы используются для определения местоположения контрагентов и сотрудников.

Связи:

country 1 ─── \* city

city 1 ─── \* address

Пример использования в проекте:

– поиск платежей от контрагентов из города Жуковский, Россия.

2. Контрагенты и виды работ

Таблицы описывают заказчиков проектов и типы работ, которые они заказывают.

Связи:

customer 1 ─── \* customer\_type\_of\_work

type\_of\_work 1 ─── \* customer\_type\_of\_work

Пример использования в проекте:

– получение названий типов работ по каждому контрагенту в виде одной строки через string\_agg.

3. Сотрудники и физические лица

Эти таблицы описывают работников компании.

Ключевая связь:

person 1 ─── 1 employee

Таблица person хранит персональные атрибуты:

– фамилию;

– имя;

– отчество;

– вычисляемое ФИО;

– дату рождения;

– телефон;

– email;

– адрес регистрации.

Таблица employee хранит кадровые данные:

– дату найма;

– дату увольнения;

– служебное описание.

Примеры использования в проекте:

– расчёт общего возраста сотрудников, нанятых в 2022 году;

– поиск сотрудника по условию на фамилию;

– расчёт среднего возраста уволенных сотрудников.

4. Организационная структура и должности

Эти таблицы описывают иерархию компании и штатное расписание.

Ключевые связи:

company\_structure 1 ─── \* position

unit\_type 1 ─── \* company\_structure

grade\_salary 1 ─── \* position

Таблица company\_structure содержит иерархию подразделений:

– unit\_id — идентификатор подразделения;

– parent\_id — родительское подразделение;

– unit\_name — название;

– unit\_type — тип подразделения.

Пример использования в проекте:

– рекурсивный обход подразделения с unit\_id = 17 и всех его дочерних подразделений.

5. Сотрудники, должности и оклады

Эти таблицы используются для анализа актуальных окладов и историчности назначений.

Таблица employee\_position содержит:

– сотрудника;

– должность;

– текущий оклад;

– коэффициент ставки;

– дату начала актуальности назначения.

Пример использования в проекте:

– расчёт суммы фактических окладов сотрудников подразделения и всех дочерних подразделений по формуле:

salary × rate

6. Проекты

Таблица project является одной из центральных в проекте.

Она хранит:

– идентификатор проекта;

– название проекта;

– контрагента;

– руководителя проекта;

– массив сотрудников, задействованных на проекте;

– стоимость проекта;

– дату подписания;

– статус проекта;

– описание.

Примеры использования в проекте:

– подсчёт проектов, подписанных в 2023 году;

– определение бонуса руководителя по завершённым проектам;

– проверка, задействован ли сотрудник на проектах;

– расчёт суммы стоимости проектов по годам.

7. Платежи по проектам

Таблица project\_payment используется для анализа финансовых операций по проектам.

Она содержит:

– идентификатор платежа;

– проект;

– сумму платежа;

– тип платежа;

– планируемую дату оплаты;

– фактическую дату и время поступления оплаты.

Примеры использования в проекте:

– сумма фактически полученных платежей;

– накопительный итог плановых авансовых платежей;

– сквозная нумерация фактических платежей по годам;

– расчёт скользящего среднего платежей;

– получение последней фактической оплаты по проекту.

Enum-типы

В базе используются перечислимые типы.

project\_status

Возможные значения:

– На подписании;

– В работе;

– Завершен;

– Отменен.

payment\_type

Возможные значения:

– Авансовый;

– Промежуточный;

– Финальный;

– Гарантийное удержание;

– Корректировка.

Примеры использования в проекте:

– фильтрация завершённых проектов по статусу Завершен;

– фильтрация авансовых платежей по типу Авансовый.

Особенности структуры БД, важные для запросов

1. Массив сотрудников в проекте

В таблице project поле employees\_id хранит массив идентификаторов сотрудников.

Для проверки участия сотрудника в проекте используется оператор:

e.employee\_id = any(pr.employees\_id)

2. Иерархия подразделений

Таблица company\_structure имеет самоссылочную связь через parent\_id.

Это позволяет применять рекурсивный CTE:

with recursive units as (...)

3. Историчность окладов

Для расчёта текущих окладов используется таблица employee\_position, а не история изменений зарплаты.

В проекте важно было выбрать именно актуальный источник фактического оклада.

4. Платежи имеют плановую и фактическую даты

В таблице project\_payment отдельно хранятся:

– планируемая дата оплаты;

– фактическая дата и время поступления средств.

Это позволяет решать разные типы задач:

– анализ плановых платежей;

– анализ реально полученных денег.

Таблицы, задействованные в итоговом проекте