Файл: Проектирование базы данных Книги клиентов и услуг на юридическом портале.pdf

Атрибуты	Тип	Ключ	Описание
id	Int	primary	Код роли пользователя
user_id	Int	foreign	Код пользователя
role_id	Int	foreign	Код роли

Рассмотрим подробнее объект «Роли» (role) таблица 9.

Таблица 9

Атрибуты	Тип	Ключ	Описание
id	Int	primary	Код роли
name	Varchar (32)		Наименование

Рассмотрим подробнее объект «Услуги» (service) таблица 10.

Таблица 10

Атрибуты	Тип	Ключ	Описание
id	Int	primary	Код услуги
name	Varchar (45)		Наименование
price	Int		Цена

Рассмотрим подробнее объект «Услуги по типу профиля» (profile_state_type_service) таблица 11. Это те услуги, которые предлагаются либо физическим, либо юридическим лицам.

Таблица 11

Атрибуты	Тип	Ключ	Описание
id	Int	primary	Код услуги по типу профиля
profile_state_type_id	Int	foreign	Код статуса
service_id	Int	foreign	Код услуги

Рассмотрим подробнее объект «Услуги профиля» (profile_service) таблица 12.

Таблица 12

Атрибуты	Тип	Ключ	Описание
id	Int	primary	Код услуги профиля
profile_id	Int	foreign	Код профиля
service_id	Int	foreign	Код услуги
order_date	Datetime		Дата заказа услуги
payment_date	Datetime		Дата оплаты услуги
payment_status	Tinyint(1)		Статус оплаты(Да/Нет)

Рассмотрим подробнее объект «Услуги менеджера» (user_service) таблица 13.

Таблица 13

Заключение

Атрибуты	Тип	Ключ	Описание
id	Int	primary	Код услуги менеджера
user_id	Int	foreign	Код пользователя
profile_service_id	Int	foreign	Код услуги профиля
start_date	Datetime		Дата принятия в работу
finish_date	Datetime		Дата исполнения
resolution	Varchar (45)

ПРОЕКТИРОВАНИЕ ФИЗИЧЕСКОЙ СТРУКТУРЫ БАЗЫ ДАННЫХ.

Рассмотрим реляционную модель взаимодействия клиента и менеджера при заказе и оказании юридических услуг Таблица 14.

Таблица 14

КЛИЕНТ (Код клиента, БИН, Тип клиента(юр. или физ. лицо), Контактное лицо, телефон контактного лица)

ЗАПРОС УСЛУГИ КЛИЕНТА( Код запроса, Код услуги, Код клиента, Дата, Оплата)

ЗАПРОСЫ НА МЕНЕДЖЕРЕ (код запроса, код менеджера, Дата начала обработки, Дата завершения оказания услуги, Резолюция(заключение),

Для приведения оказания услуг клиентам менеджерами реляционная модель была приведена к трем нормальным формам, а именно в Клиенте от атрибута Код клиента зависят все остальные атрибуты. То есть запись по каждому клиенту уникальна. Запрос услуги клиента уникален и связан вторичным ключом (внешним ключом) с кодом клиента. То есть по каждому клиенту может быть несколько уникальных запросов, которые клиенту необходимо оплатить. Запросы на менеджере -это те запросы, которые менеджер берет в работу. Код запроса и Код менеджера составные ключи, так как один запрос могут обрабатывать несколько менеджеров. Исходя из вышенаписанного можно подытожить что:

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

Рассмотрим созданные в СУБД структуры таблиц с первичным, внешним или вторичным ключами, а так же индексами, созданные по ER-модели БД ipravo.

Структура таблиц базы данных «Пользователи» рисунок 3.

Рисунок 3. «Пользователи».

Структура таблиц базы данных «Профиль пользователя» рисунок 4.

Рисунок 4. «Профиль пользователя».

Структура таблиц базы данных «Тип статуса» рисунок 5.

Рисунок 5. «Тип статуса».

Структура таблиц базы данных «Профиль организации» рисунок 6.

Рисунок 6. «Профиль организации».

Структура таблиц базы данных «Документы профиля» рисунок 7.

Рисунок 7. «Документы профиля».

Структура таблиц базы данных «Документы» рисунок 8.

Рисунок 8. «Документы».

Структура таблиц базы данных «Тип документа» рисунок 9.

Рисунок 9. «Тип документа».

Структура таблиц базы данных «Роль пользователя» рисунок 10.

Рисунок 10. «Роль пользователя».

Структура таблиц базы данных «Роли» рисунок 11.

Рисунок 11. «Роли».

Структура таблиц базы данных «Услуги» рисунок 12.

Рисунок 12. «Услуги».

Структура таблиц базы данных «Услуги по типу профиля» рисунок 13.

Рисунок 13. «Услуги по типу профиля».

Структура таблиц базы данных «Услуги профиля» рисунок 14.

Рисунок 14. «Услуги профиля».

Структура таблиц базы данных «Услуги менеджера» рисунок 15.

Рисунок 15. «Услуги менеджера».

Рассмотрим схему данных сгенерированную автоматически через СУБД MySQL Workbench 6.3 на рисунке 16.

Рисунок 16. Схема данных

2. Практическая часть

2.1 Контрольный пример решения задачи

Для добавления, обновления и изменения данных в БД в системе будут созданы формы, такие как:

Регистрация пользователя;
Отправка запроса клиента на получение услуги;
Прикрепление документов к профилю пользователя;
Добавление контактных лиц к организации;
Загрузка фото пользователя;
Анкета данных для предоставления услуги;
Загрузка документа менеджером;
Оплата услуги;
Добавление оповещений клиента о выполненной услуге;
Принятие менеджером услуги в работу и другие формы отображения отчетности по введённым в БД данным.

Рассмотрим некоторые примеры, как будут выглядеть входные формы и запросы по ним.

Ввод данных в таблицу с пользователями, регистрация пользователя/ клиента. Рисунок 17.

Рисунок 17. Регистрация пользователя/Клиента

Ввод нового пользователя в БД:

INSERT INTO `ipravodb`.`users` (`id`, `name`, `email`, `password`) VALUES ('11', 'Новый пользователь', 'new@gmail.ru', '$2y$10$2L2j0APCZuVbmk1bziPvoegR665nQQ3RONi5LtqiiqIwx/g52RDDe');

Запрос в БД показать всех пользователей приведён на рисунке ниже.

Рисунок 18. Запрос все пользователи

Получение пользователей, которые являются юридическими лицами Рисунок 19.

Рисунок 19. Запрос вытаскивает всех юридических лиц

Подсчет юредических лиц которые зарегистрированы в системе на Рисунке 20

Рисунок 20. Запрос количества юр. лиц

Написание резолюции или консультационной информации менеджером (юристом) в поле Комментарий Рисунок 21.

Рисунок 21. Окно изменения статуса выполнения услуги и комментарий к ней

На стороне сервера отправиться после сохранения данных отправится следующий запрос:

UPDATE `ipravodb`.`user__service` SET resolution`='Документы заверены. Курьер привезет' WHERE `id`='1';

2.2 Разработка интерфейса и реализация проекта

Структура программной системы обработки данных согласно построенным моделям, данных в среде, выбранной СУБД представлена ниже на рисунке 22.

Рисунок 22. Структура программной системы обработки данных

Личный кабинет клиента состоит из контактной информации (рисунок 23), уведомлений от менеджера, документов клиента, с перечнем получаемых услуг и всех его документов, историй услуг на портале (рисунок 24). Интерфейс у клиента и нотариального менеджера аналогичен за исключением информации во вкладке Мои услуги. У менеджера в это вкладке виден список услуг, которые он взял для рассмотрения (рисунок 25).

Рисунок 23. Контактная информация по клиенту

Рисунок 24. Часть с услугами и документами по клиенту

Рисунок 25. Список услуг в кабинете у менеджера-нотариуса

Запрос 1. Личный кабинет клиента:

select * from users as u

join profile as p

on u.id=p.user_id

join profile_legal as pl

on pl.profile_id=p.id

where u.id=5

Запрос 2. Личный кабинет менеджера:

select * from users as u

join profile as p

on u.id=p.user_id

where u.id=10

Запрос 3. Запрошенные услуги клиентом:

select * from profile_service as ps

join profile as p

on p.id=ps.profile_id

join users as u

on u.id=p.user_id

left join service as s

on s.id=ps.service_id

where p.user_id=5

order by ps.order_date desc

Запрос 4. Запрошенные услуги менеджером:

select * from user_service1 as us

join users as u

on u.id=us.user_id

join profile_service ps

on ps.id=us.profile_service_id

left join service as s

on s.id=ps.service_id

where us.user_id=10

order by ps.order_date desc

Когда несколько услуг было обработано по клиенту, у него может высвечиваться общее число обработанных услуг. Запрос 5:

select count(*) from profile_service as ps

join profile as p

on p.id=ps.profile_id

join users as u

on u.id=p.user_id

left join service as s

on s.id=ps.service_id

where p.user_id=5

group by (p.user_id)

Форма «Документы» отображена на рисунке 26. В этой форме собраны все документы необходимые как клиенту для получения услуги, так и для менеджера (нотариуса или юриста) который предоставляет нотариальные услуги.