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

Содержание:

Введение

Актуальность данной темы заключается в том, что обычно данные хранятся в бумажном виде, возникает проблема поиска информации по клиентам, невозможность предоставления нотариальных и юридических услуг удаленно. Создание электронной Базы Данных (далее БД) позволяет экономить время при поиске информации о клиенте, возможность охватить большее число клиентов как из своего города, так и из других городов при этом не теряя время на дорогу.

Предметом данной работы выступает созданная БД. Главными объектами моей темы есть обеспечение юридическими услугами всех желающих, а также сбор информации по всем клиентам компании ТОО «ИнтерПраво» и историю представляемых услуг.

Цель и задачи работы. Целью курсовой работы является создание БД для сбора информации в едином источнике по клиентам компании ТОО «ИнтерПраво» и истории и анализа предоставленных услуг организацией.

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

БД планировалась для организации производства, хранения отчетности, регистрации новых клиентов. БД разработана в программной среде MySQL и может взаимодействовать, при необходимости, с другими базами данных.

1. Аналитическая часть

1.1 Описание предметной области. Постановка задачи

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

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

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

Основой для создания базы данных для фирмы стала необходимость каким-то образом сгруппировать всю информацию, которая производится в результате разнородной деятельности нотариусов и юристов в компании. В конечном этапе запроектирована БД позволит просматривать информацию о клиентах, удобно и быстро планировать работу по оказанию юридических услуг, вести предварительную запись клиентов по расписанию работы специалистов, оперативно оказывать юридические услуги напрямую специалисту и начислить ему заработную плату, идентифицировать клиента и работника, продажа юридических услуг; проводить различные типы оплат, наличными, банковской картой, анализировать востребованность различных направлений услуг.

1.2.Выбор СУБД.

Целью курсовой работы было создание интернет-системы для обработки заказов на оказание юридических услуг, сбора информации по клиентам, анализ по продажам услуг клиентам. Естественно, возникает вопрос, в каком виде должна храниться эта информация и с помощью каких средств её следует обрабатывать. Так как, например, информация о каждом отдельном клиенте представляет собой типичный набор данных (фамилия, имя, отчество, почтовый адрес, резидентство и т. д.), то очевидно, что в этом случае целесообразно хранить их в реляционной базе данных на сервере. Посредством запросов к базе данных пользователь может получать нужные ему сведения, а администратор может добавлять и изменять данные.

Выбор конкретной СУБД в качестве сервера баз данных осуществлялся исходя из тех преимуществ, которые она имеет перед другими, а также удобства работы с ней. В данном случае был выбрана клиент-серверная СУБД MySQL. Её архитектура изображена на рисунок 1.

Рисунок 1. Клиент-серверная архитектура MySQL

Самая подходящая для MySQL сфера применения - это Интернет, благодаря хорошей системе безопасности этого пакета, стабильной работе и высокому быстродействию. Для создания скриптов был выбран язык программирования PHP, а MySQL – самая популярная СУБД, которая поддерживается этим языком. В PHP есть множество функций, которые позволяют удобно и эффективно работать с базами данных – и это одна из причин выбора данной СУБД.

Рассмотрим преимущества MySQL:

1) Быстродействие

Благодаря внутреннему механизму многопоточности быстродействие MySQL весьма высоко. Для разработчиков MySQL скорость всегда являлась ключевым параметром. Новые возможности добавлялись в пакет MySQL только после того, как их удавалось реализовать без ущерба для производительности. Иногда это означало, что некоторые возможности добавлялись не так быстро, как хотелось бы пользователям, но зато всегда гарантировало быструю работу MySQL.

2) Безопасность

Довольно высокий уровень безопасности обеспечивается благодаря базе данных MySQL, создающейся при установке пакета и содержащей пять таблиц. При помощи этих таблиц можно описать, какой пользователь из какого домена с какой таблицей может работать и какие команды он может применять. Пароли, хранящиеся в базе данных, можно зашифровать при помощи встроенной в MySQL функции password().

3) Лицензия

Раньше лицензирование MySQL было немного запутанным; сейчас эта программа для некоммерческих целей распространяется бесплатно.

4) Открытость кода

Благодаря этому программист может сам добавлять в пакет нужные функции, расширяя его функциональность так, как ему требуется. За отдельную плату это могут сделать и сами авторы MySQL.

5) Простота использования

Для начала работы с MySQL не требуется сложной процедуры конфигурации. MySQL Server начнёт работать соответствующим образом сразу. По умолчанию выбираются значения, соответствующие минимальному использованию ресурсов диска и памяти. Для получения оптимальной производительности и для специальных условий (например, для проверки входа в систему), конечно же, потребуется дополнительная настройка. Чтобы помочь выполнить такую настройку, предлагаются соответствующие примеры файлов типовой конфигурации.

6) Сообщество

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

7) Переносимость

В настоящее время существуют версии программы для большинства распространенных компьютерных платформ. Это говорит о том, что вам не навязывают определенную операционную систему. Вы сами можете выбрать, с чем работать, например с Linux или Windows, но даже в случае замены ОС вы не потеряете свои данные и вам даже не понадобятся дополнительные инструменты для их переноса.

Конечно же, как и любое программное средство, СУБД MySQL не избавлена от некоторых недостатков. Например, можно назвать отсутствие вложенных запросов, что приводит к необходимости находить нужные значения отдельно и подставлять их в другой запрос непосредственно в CGI-сценарии, что, несомненно, сказывается на производительности.

Несмотря на это, СУБД MySQL была выбрана как наиболее подходящий сервер баз данных для интернет-ресурса «ipravo» для компании ТОО «ИнтерПраво».

Для построения ER-модели БД будет использоваться программа Microsoft Visio так как она построена исключительно на профессиональном уровне, что обеспечивает правильное построение технических проектов и моделей. Такая платформа дает по-настоящему эффективную и удобную работу. Позволяет легко и просто создавать технические проекты, диаграммы, модели, а также редактировать их, выравнивать, изменяя размеры.

1.3. Проектирование логической структуры базы данных 

На этом этапе логического проектирования необходимо определить типы и длины полей, связи между объектами. Для получения функциональной и удобной системы БД нормализована до третей нормальной формы. Представим описание объектов и связей между ними в виде ER-модели БД ipravo, которая предоставлена ниже.

Рисунок 2. ER-модель БД ipravo

Рассмотрим подробнее объект «Пользователи» (users) таблица 1.

Таблица 1

Атрибуты	Тип	Ключ	Описание
id	Int	primary	Код пользователя
name	Varchar (191)		Имя для приветсвия
email	Varchar (191)		Почта(Логин)
password	Varchar (191)		Пароль
is_active	Tinyint(1)		Активность записи пользователя
remember_token	Varchar(100)		Криптографический токен
created_at	timestamp		Когда создано
updated_at	timestamp		Когда обновлено

Рассмотрим подробнее объект «Профиль пользователя» (profile) таблица 2. У пользователя может быть несколько профилей, в связи с чем создана отдельная таблица от таблицы «Пользователи».

Таблица 2

Атрибуты	Тип	Ключ	Описание
id	Int	primary	Код профиля пользователя
user_id	Int	foreign	Код пользователя
profile_state_type_id	Smallint(5)		Статус: юр. или физ. лицо
phone	Varchar (32)		Телефон
email	Varchar (191)		Дополнительная почта
last_login_date	datetime		Дата последнего входа
is_resident	Tinyint(1)		Резидентство
full_name	Varchar (191)		ФИО пользователя

Рассмотрим подробнее объект «Тип статуса» (profile_state_type) таблица 3. Это справочник типов статусов клиента: Физическое и Юридическое лицо. Необходим для того чтобы на уровне БД разделить услуги для разных типов клиентов.

Таблица 3

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

Рассмотрим подробнее объект «Профиль организации» (profile_legal) таблица 4.

Таблица 4

Атрибуты	Тип	Ключ	Описание
id	Int	primary	Код статуса
profile_id	Int	foreign	Код профиля
company_name	Varchar (128)		Название организации
business_identification_number	Varchar (16)		БИН
contact_person	Varchar (128)		Контактное лицо (КЛ)
position	Varchar (256)		Должность КЛ
scope_activity	Varchar (1024)		Описание Деятельности

Рассмотрим подробнее объект «Документы профиля» (profile_document) таблица 5.

Таблица 5

Атрибуты	Тип	Ключ	Описание
id	Int	primary	Код статуса
profile_id	Int	foreign	Код профиля
document_id	Int	foreign	Код документа

Рассмотрим подробнее объект «Документы» (document) таблица 6.

Таблица 6

Атрибуты	Тип	Ключ	Описание
id	Int	primary	Код статуса
name	Varchar (256)		Наименование
path	Varchar (1024)		Путь к папке
document_type_id	Int	foreign	Код типа документа

Рассмотрим подробнее объект «Тип документа» (document_type) таблица 7.

Таблица 7

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

Рассмотрим подробнее объект «Роль пользователя» (user_role) таблица 8.

Таблица 8

Атрибуты	Тип	Ключ	Описание
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

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

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

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

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

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

Рассмотрим созданные в СУБД структуры таблиц с первичным, внешним или вторичным ключами, а так же индексами, созданные по 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 Контрольный пример решения задачи

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

1. Регистрация пользователя;
2. Отправка запроса клиента на получение услуги;
3. Прикрепление документов к профилю пользователя;
4. Добавление контактных лиц к организации;
5. Загрузка фото пользователя;
6. Анкета данных для предоставления услуги;
7. Загрузка документа менеджером;
8. Оплата услуги;
9. Добавление оповещений клиента о выполненной услуге;
10. Принятие менеджером услуги в работу и другие формы отображения отчетности по введённым в БД данным.

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

Ввод данных в таблицу с пользователями, регистрация пользователя/ клиента. Рисунок 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. В этой форме собраны все документы необходимые как клиенту для получения услуги, так и для менеджера (нотариуса или юриста) который предоставляет нотариальные услуги.

Рисунок 26. Форма «Документы»

Документы на этой форме отображаются по запросу 5:

SELECT * FROM

document as d

join profile_document as pd

on pd.document_id=d.id

where pd.profile_id=2

Результат такого запроса в СУБД показан на рисунке 27.

Рисунок 27. Результат отображения документов пользователя.

Заключение

По окончанию проведенной работы была создана система для предоставления юридических услуг квалифицированными специалистами в независимости от географического положения клиента и его возможности покидать дом. Так также создана возможность сбора информации по всем клиентам компании ТОО «ИнтерПраво» и истории представляемых услуг.

Цель и задачи работы были создание БД для сбора информации в едином источнике по клиентам компании ТОО «ИнтерПраво» и истории и анализа предоставленных услуг организацией. В итоге получили готовую БД, которая корректно заполняется, обновляется и легко выдает заказчику нужную

информацию и отчетность.

Список литературы

1. Попов И. И., Максимов Н. В., Голицына О. Л. Базы данных. – М.: ФОРУМ: Инфра-М, 2006. – 352 с.
2. Полякова Л. Н. Основы SQL. [Электронный ресурс] // Интернет-университет информационных технологий - ИНТУИТ.ру, 2004.–Режим доступа: http://www.intuit.ru/department/database/sql/.
3. Никандрова Ю.А. Интернет курс по дисциплине «Базы данных (Управление данными)» // Московский финансово-промышленный университет «Университет», 2012. Режим доступа: http://e-biblio.ru/book/bib/01_informatika/bazy_dannyx/sg.html

ПРИЛОЖЕНИЯ