Нормализация базы данных
При разработке структуры базы данных важным процессом является нормализация. Под этим понимается правильное распределение подлежащих хранению данных по различным таблицам, из которых будет состоять БД. Существуют строгие математические определения того, что такое, когда набор таблиц находится в той или иной нормальной форме. Но дело в том, что при определенном навыке, человек чисто интуитивно способен создавать нормализованные базы данных.
Главной проблемой, которую необходимо решать при нормализации БД - это избыточность спроектированного набора таблиц. В целом суть задачи заключается в следующем: каждый факт, хранимый в БД, должен храниться один-единственный раз, поскольку дублирование может привести (и на практике непременно приводит, как только проект приобретает реальную сложность) к несогласованности между копиями одной и той же информации. Следует избегать любых неоднозначностей, а также избыточности хранимой информации.
Поскольку реляционные базы данных построены на солидном математическом фундаменте нормальные формы хорошо формализованы. Фактически это не что иное, как последовательное преобразование исходной базы данных к нормальной форме (НФ), при этом каждая следующая НФ обязательно включает в себя предыдущую (что, собственно, и позволяет разбить процесс на этапы и производить его однократно, не возвращаясь к предыдущим этапам). Всего в реляционной теории насчитывается 6 НФ.
На практике, как правило, ограничиваются 3НФ, ее оказывается вполне достаточно для создания надежной схемы БД. НФ более высокого порядка представляют скорее академический интерес из-за чрезмерной сложности.
Система находится в первой нормальной форме (1НФ) если каждый атрибут отношения должен хранить одно-единственное значение и не являться ни списком, ни множеством значений. Следует заметить, что, несмотря на внешнюю строгость данного определения, однозначно определить понятие атомарности зачастую оказывается довольно затруднительно, если заранее неизвестны семантика атрибута и его роль в обработке хранимых данных. Атрибут, который является атомарным в одном приложении, может оказаться составным в другом.
Схема отношения находится во второй нормальной форме (2НФ) относительно множества функциональных зависимостей, если она находится в первой и каждый неключевой атрибут полностью зависит от каждого ключа. Другими словами, отношение находится во 2НФ, если оно находится в 1НФ, и при этом все неключевые атрибуты зависят только от ключа целиком, а не от какой-то его части.
Схема отношения находится в третьей нормальной форме (3НФ) относительно множества функциональных зависимостей, если она находится в 2НФ и ни один из непервичных атрибутов в нем не является транзитивно зависимым от ключа. То есть, чтобы привести отношение к 3НФ, необходимо устранить функциональные зависимости между неключевыми атрибутами отношения (факты, хранимые в таблице, должны зависеть только от ключа).
Денормализация базы данных
Денормализация — это процесс модификации структуры таблиц нормализованной базы данных с целью повышения производительности за счет допущения некоторой управляемой избыточности данных. Единственным оправданием денормализации является попытка повышения скорости работы базы данных. Денормализованная база данных — это не то же самое, что ненормализованная. Денормализация базы данных представляет собой процесс понижения нормализации на один-два уровня.
Денормализация предполагает объединение некоторых из ранее разделенных таблиц и создание таблиц с дубликатами данных с целью уменьшения числа связываемых таблиц при доступе к данным, что должно уменьшить число требуемых операций ввода-вывода и нагрузку на центральный процессор.
Однако за денормализацию нужно платить. В денормализованной базе данных повышается избыточность данных, что может повысить производительность, но потребует больше усилий для контроля за связанными данными. Усложнится процесс создания приложений, поскольку данные будут повторяться и их труднее будет отслеживать. Кроме того, осуществление ссылочной целостности оказывается не простым делом — связанные данные оказываются разделенными по разным таблицам. Существует золотая середина между нормализацией и денормализацией, но чтобы найти ее, требуется знание и природы хранимых данных, и специфических требований бизнеса соответствующей компании.
Нам необходимо денормализовать отношение «пользователь-Интернет сессия» поскольку в первой версии биллинговой системы было обнаружено, что большая часть производительности системы теряется на выборке данных из таблицы сессий пользователей, поэтому было принято решение о введении избыточности данных. Также денормализации подверглись отношения «протоколы - сессии», «сессии - популярность стран » и «сессии - популярность сайтов».
Рассмотрим процесс денормализации более подробно.
На рисунке 16 показана номализованная связь «пользователь - Интернет сессия» в данном случае пользователю соответствует множество Интернет сессий. С точки зрения нормализации эта связь находится в третьей нормальной форме, однако на практике за день активного использования Интернет в учебном заведении в таблице actions накапливается более 80 тысяч записей, и через неделю выборка Интернет сессий пользователя занимает слишком много времени – это неприемлемо для управления в реальном времени.
Рисунок 16 – Нормализованная связь «пользователь - Интернет сессия»
Чтобы сократить нагрузку на сервер базы данных было решено хранить данные с разной степенью детализации. По прежнему в таблице Интернет сессий находятся данные о сессиях пользователей, однако с определенной периодичностью данные агрегируются, перемещаясь в таблицу архивных протоколов. Денормалиованная версия этого отношения представлена на рисунке 17.
Рисунок 17 – Структура хранения протоколов сессий пользователей
При такой структуре хранения в таблицу протоколов записываются группированные данные из таблицы сессий. Это позволяет в разы увеличить скорость выборки данных из базы, однако снижает скорость записи, поскольку перед записью данные необходимо сначала преобразовать к требуемому виду.
Аналогично поступим с таблицами популярных URL и стран, в результате получилась структура, представленная на рисунке 18.
Рисунок 18 – Структура хранения данных о популярных URL и странах
Здесь можно видеть, что информация о посещаемых пользователем ресурсах хранится в двух различных таблицах, в одной находится подробная информация по каждой сессии для каждого пользователя, в другой, которая и используется для определения предпочтений пользователей уже обработанная и усредненная информация. Все эти данные также агрегируются с определенной периодичностью, что позволяет снизить нагрузку на СУБД, а следовательно и увеличить быстродействие всей системы в целом.
Многоуровневая база данных
Чтобы не работать с массивами максимально детализированной информации, так как это может значительно снизить быстродействие системы, была введена многоуровневая архитектура базы данных.
Логично выделить 3 уровня:
1) максимально детализированная информация без какой-либо обработки;
2) классифицированная и первично агрегированная информация;
3) оперативная информация.
База первого уровня может понадобиться для разрешения спорных моментов с клиентами. Важно сохранять ее в исходном виде, т.к. возможно будет необходимо постфактум произвести перерасчет выставленных к оплате счетов с учетом скорректированных тарифов или, например, уточненных границ сетей, по которым делится трафик. Также такая подробная информация может быть необходима при анализе данных, собранных за длительный промежуток времени, например для прогнозирования потребления трафика в дальнейшем.
Не для каждого сервиса можно получить детализированную информацию о соединениях, но к этому надо стремиться. По крайней мере, при подсчете трафика через Proxy сервер это решается автоматически. Минусом является значительный объем, требующийся для хранения всех этих данных.
В разрабатываемой системе таблица протоколов сессий как раз содержит всю агрегированную информацию по всем сессиям пользователей за все время работы системы. Обращения к этой таблице занимают большое количество времени, однако данные из неё необходимы редко, поэтому это не сказывается на скорости работы системы в целом.
База второго уровня компактнее, чем первая, поэтому ее можно хранить за более продолжительный период времени. Например, после классификации трафика можно не хранить информацию о локальном трафике, если за него не взимается плата. Также с большой долей вероятности можно считать одним соединением несколько соединений с одним и тем же хостом, произошедшие в приблизительно одно время (типичная ситуация с многопоточными сетевыми клиентами).
Данный уровень представил в базе данных таблицами, в которых собрана информация о наиболее посещаемых Интернет ресурсах и стран. Записей в этих таблицах значительно меньше, чем в таблице протоколов, так как на данном уровне абстракции не требуется полная информация об Интернет сессиях пользователей.
Оперативная информация - наиболее грубая по отношению к остальным двум базам, но зато операции с ней можно совершать очень быстро, что позволяет сократить время реакции системы, которое будет обсуждаться ниже. На основе этой базы осуществляется принятие решений о предоставлении или прекращении предоставления услуг конкретному клиенту. В нашем случае необходимо хранить самую актуальную информацию о текущих Интернет сессиях пользователей, которая будет периодически агрегироваться, то есть записываться в хранилища более низкого уровня. Доступ к этой части базы данных требуется постоянно и от скорости получения данных на прямую зависит скорость принятия решения о разрешении/запрещении доступа и реакции на несанкционированные действия пользователя. Этим же временем определяется и погрешность, с которой будет учитываться потребленный пользователями трафик.
Даталогическое проектирование
Рассмотрим процесс даталогического проектирования. Любая СУБД оперирует с допустимыми для нее логическими единицами данных, а также допускает использование определенных правил композиции логических структур более высокого уровня из составляющих информационных единиц более низкого уровня. Кроме того, многие СУБД накладывают количественные и иные ограничения на структуру базы данных. Поэтому прежде чем приступить к построению даталогической модели, необходимо детально изучить особенности СУБД, определить факторы, влияющие на выбор проектного решения, ознакомиться с существующими методиками проектирования, а также провести анализ имеющихся средств автоматизации проектирования, возможности и целесообразности их использования. Хотя даталогическое проектирование является проектированием логической структуры базы данных, на него оказывают влияние возможности физической организации данных, предоставляемые конкретной СУБД. Поэтому знание особенностей физической организации данных является полезным при проектировании логической структуры.
Логическая структура базы данных, а также сама заполненная данными база данных являются отображением реальной предметной области. Поэтому на выбор проектных решений самое непосредственное влияние оказывает специфика отображаемой предметной области, отраженная в инфологической модели. Рассмотрим даталогическую модель, построенную с учетом особенностей MySQL 4.1.
Сведения о таблицах, содержащихся в базе данных, обобщены в таблице 8.
Таблица 8 - Сведения о таблицах
Название таблицы
|
Комментарий
|
users
|
Пользователи биллинговой системы
|
packets
|
Тарифы
|
groups
|
Группы администрирования системы
|
actions
|
События Интернет-сессий
|
protocols
|
Архив протоколов Интернет-сессий
|
ctry_popularity
|
Наиболее популярные регионы, посещаемые пользователями
|
ip2country
|
Справочник диапазонов IP адресов стран
|
url_denied_log
|
Список попыток пользователей нарушить запрещения на посещения определенных URL адресов
|
url_denied
|
Список запрещенных Интернет адресов
|
url_categories_denied
|
Список запрещенных категорий
|
url_categories_conflicts
|
Список конфликтных категорий
|
url_categories
|
Список категорий контента
|
url_categories_keywords
|
Список ключевых слов категорий контента
|
url_categories_unsensewords
|
Список слов, не несущих смысловой нагрузки
|
url_categories_match
|
Отношение категорий Web-сайтов и стран мира
|
url_log
|
Список посещенных пользователями URL
|
url_popularity
|
Список наиболее популярных у пользователей URL адресов
|
events
|
События пользовательского интерфейса
|
Более подробное описание таблиц БД представлено в таблицах 9 - 22.
Таблица 9 – Описание таблицы «Пользователи»
Название
|
Тип
|
Комментарий
|
user
|
varchar
|
Логин для входа пользователя в систему
|
password
|
varchar
|
Пароль пользователя
|
uid
|
int
|
Идентификатор пользователя(PK)
|
gid
|
int
|
Идентификатор группы пользователей(PK) (FK)
|
nick
|
varchar
|
Уменьшительное имя для отображения на сайте
|
fio
|
varchar
|
Фамилия Имя Отчество пользователя
|
gender
|
short
|
Пол пользователя
|
phone
|
varchar
|
Номер телефона пользователя
|
email
|
varchar
|
Адрес электронной почты пользователя
|
icq
|
varchar
|
Номер ICQ
|
url
|
varchar
|
Адрес домашней страницы пользователя
|
address
|
varchar
|
Адрес
|
rang
|
varchar
|
Ранг (отображение рядом с сообщениями или информацией)
|
group
|
int
|
Группа администрирования сайта
|
add_uid
|
int
|
Идентификатор добавившего администратора
|
city
|
varchar
|
Город
|
country
|
varchar
|
Страна
|
raiting
|
int
|
Рейтинг на сайте
|
signature
|
varchar
|
Подпись отображаемая на сайте
|
info
|
varchar
|
Дополнительная информация, которую пользователь указывает по желанию
|
prim
|
varchar
|
Примечание администратора
|
add_date
|
DateTime
|
Дата добавления
|
blocked
|
short
|
Блокировано: Если равно 1 – доступ запрещен
|
total_time
|
DateTime
|
Счетчик общего времени в секундах
|
total_traffic
|
long
|
Счетчик трафика в байтах
|
max_total_traffic
|
long
|
Общий лимит трафика для пользователя
|
max_month_traffic
|
long
|
Максимальный объем трафика на месяц
|
max_week_traffic
|
long
|
Максимальный объем трафика на неделю
|
max_day_traffic
|
long
|
Максимальный объем трафика на день
|
last_connection
|
DateTime
|
Дата и время последнего подключения
|
simultaneouse_use
|
int
|
Количество одновременных подключений для одного пользователя
|
Таблица 10 – Описание таблицы «Тарифы»
Название
|
Тип
|
Комментарий
|
packet
|
varchar
|
Название тарифа
|
gid
|
int
|
Идентификатор тарифа(PK)
|
blocked
|
short
|
Блокировано: Если равно 1 – доступ запрещен
|
total_time_limit
|
long
|
Общий лимит времени для тарифа
|
month_time_limit
|
long
|
Лимит времени для тарифа на месяц
|
week_time_limit
|
long
|
Лимит времени для тарифа на неделю
|
day_time_limit
|
long
|
Лимит времени для тарифа на день
|
total_traffic_limit
|
long
|
Общий лимит трафика для тарифа
|
month_traffic_limit
|
long
|
Максимальный объем трафика на месяц
|
Продолжение таблицы 10
|
Название
|
Тип
|
Комментарий
|
week_traffic_limit
|
long
|
Максимальный объем трафика на неделю
|
day_traffic_limit
|
long
|
Максимальный объем трафика на день
|
login_time
|
Datetime
|
Разрешенное время доступа в Интернет
|
simultaneous_use
|
int
|
Количество одновременных подключений под одним логином
|
port_limit
|
int
|
Количество подключений на тарифе
|
level
|
int
|
Уровень администраторов, которые могут назначать этот тариф
|
add_uid
|
int
|
Идентификатор администратора, добавившего пользователя
|
prim
|
varchar
|
Примечания администратора
|
rang
|
int
|
Ранг тарифа, определяющий долю трафика тарифного плана в общем объеме трафика
|
exceed_times
|
int
|
Максимальное дневное превышение трафика
|
Таблица 11 – Описание таблицы «События»
Название
|
Тип
|
Комментарий
|
user
|
int
|
Идентификатор пользователя(FK)
|
gid
|
int
|
Группа, в которой в данный момент пользователь(FK)
|
id
|
int
|
Идентификационный номер соединения(PK)
|
time_on
|
long
|
Время соединения в секундах
|
start_time
|
DateTime
|
Дата и время начала соединения
|
stop_time
|
DateTime
|
Дата и время окончания соединения
|
in_bytes
|
long
|
Принятые байты
|
out_bytes
|
long
|
Переданные байты
|
ip
|
varchar
|
IP пользователя
|
server
|
varchar
|
IP сервера доступа
|
client_ip
|
varchar
|
Реальный IP сервера доступа
|
port
|
int
|
Порт на сервере доступа
|
connect_info
|
varchar
|
Информация о соединении
|
protocol
|
varchar
|
Используемый протокол
|
date
|
DateTime
|
Дата события
|
data
|
varchar
|
Содержание протокола
|
Таблица 12 – Описание таблицы «Архивы событий»
Название
|
Тип
|
Комментарий
|
aid
|
int
|
Идентификатор архивного протокола(PK)
|
uid
|
int
|
Идентификатор пользователя(FK)
|
date
|
DateTime
|
Дата события
|
data
|
varchar
|
Текст архивной версии протокола
|
Таблица 13 – Описание таблицы «События административного интерфейса»
Название
|
Тип
|
Комментарий
|
eid
|
int
|
Идентификатор события(PK)
|
uid
|
int
|
Идентификатор пользователя(FK)
|
date
|
DateTime
|
Дата события
|
event
|
varchar
|
Текст события
|
Таблица 14 – Описание таблицы «Список стран с IP адресами»
Название
|
Тип
|
Комментарий
|
id
|
int
|
Идентификатор страны(PK)
|
sip
|
varchar
|
Начальный IP адрес диапазона
|
eip
|
varchar
|
Конечный IP адрес диапазона
|
source
|
varchar
|
Источник, выдавший данный диапазон
|
assigned
|
DateTime
|
Время принятия IP адреса
|
ctry
|
varchar
|
Сокращенный индекс страны
|
country
|
varchar
|
Полное название страны
|
Таблица 15 – Описание таблицы «Список категорий»
Название
|
Тип
|
Комментарий
|
cid
|
int
|
Идентификатор категории(PK)
|
title
|
varchar
|
Название категории, принятое корпорацией IBM
|
title_ru
|
varchar
|
Русское название категории
|
Таблица 16 – Описание таблицы «Запрещенные для тарифа категории»
Название
|
Тип
|
Комментарий
|
ucdid
|
int
|
Идентификатор запрета(PK)
|
gid
|
int
|
Идентификатор тарифа(FK)
|
cid
|
int
|
Идентификатор запрещенной категории(FK)
|
Таблица 17 – Описание таблицы «Ключевые слова для категории»
Название
|
Тип
|
Комментарий
|
uckid
|
int
|
Идентификатор ключевого слова(PK)
|
keyword
|
varchar
|
Ключевое слово
|
cid
|
int
|
Идентификатор категории(FK)
|
weight
|
int
|
Значимость ключевого слова
|
Таблица 18 – Описание таблицы «Категории сайтов»
Название
|
Тип
|
Комментарий
|
u2cid
|
int
|
Идентификатор(PK)
|
url
|
varchar
|
URL сайта
|
cid
|
int
|
Соответствующая категория(FK)
|
Таблица 19 – Описание таблицы «Неключевые слова»
Название
|
Тип
|
Комментарий
|
ucuwid
|
int
|
Идентификатор(PK)
|
keyword
|
varchar
|
Слово, не несущее смысловой нагрузки
|
Таблица 20 – Описание таблицы «Запрещенные URL»
Название
|
Тип
|
Комментарий
|
duid
|
int
|
Идентификатор правила(PK)
|
gid
|
int
|
Идентификатор тарифа, которому запрещен URL(FK)
|
url
|
varchar
|
URL сайта
|
Таблица 21 – Описание таблицы «Попытки входа на запрещенный сайт»
Название
|
Тип
|
Комментарий
|
udid
|
int
|
Идентификатор правила(PK)
|
unique_id
|
int
|
Идентификатор сессии пользователя(FK)
|
url
|
varchar
|
URL сайта
|
date
|
DateTime
|
Дата и время зафиксированной попытки нарушения
|
Таблица 22 – Описание таблицы «Попытки входа на сайт с запрещенным типом контента»
Название
|
Тип
|
Комментарий
|
ucdlid
|
int
|
Идентификатор правила(PK)
|
cid
|
int
|
Идентификатор тарифа, которому запрещен URL(FK)
|
unique_id
|
int
|
Идентификатор сессии пользователя(FK)
|
url
|
varchar
|
URL сайта
|
date
|
DateTime
|
Дата и время зафиксированной попытки нарушения
|
(PK) – Первичный ключ,
(FK) – Внешний ключ.
Даталогическая модель базы данных системы представлена на рисунке 19.
Рисунок 19 – Даталогическая модель базы данных
Обеспечение безопасности системы
Разработанная для системы четырехуровневая модель безопасности, принцип построения которой был унаследован у механизмов защиты современных операционных систем. Рассмотрим принцип работы этих механизмов, который отражен на рисунке 20.
Для доступа к системе пользователь должен пройти процедуру аутентификации, авторизации и аккаунтинга, которые состоят из сопоставления персоны и существующей учётной записи в системе безопасности (для чего используется логин и пароль пользователя); сопоставления учётной записи и определённых полномочий; и слежение за потреблением ресурсов.
Рисунок 20 – Организация безопасности системы
Пароль пользователя хранится в базе данных в виде MD5 хеш свертки, и чтобы произвести аутентификацию пользователя блок криптографической защиты производит хеш свертку введенного пользователем пароля и проверяет, совпадает ли свертка в базе данных и полученная для данного пользователя и в зависимости от результата разрешает или запрещает пользователю доступ и определяет его права в системе.
Блок поддержания целостности базы данных отвечает за загрузку базы данных, ведение системного журнала, проверку целостности, создание контрольных точек и выполнения по ним откатов в случае аварийной ситуации.
Загрузка и выгрузка базы данных и ведение системного журнала осуществляется средствами самой СУБД. Выполнение контрольных точек осуществляется дополнительной библиотеки расширения, а выполнение откатов производится вручную системным администратором.
Целостность базы данных по сущностям, состоящая в отсутствии нулевых значений у ключей таблиц поддерживается на уровне СУБД.
Ссылочная целостность - необходимое качество реляционной базы данных, заключающееся в отсутствии в любой её таблице внешних ключей, ссылающихся на несуществующие записи в этой или других таблицах.
В качестве средства обеспечения ссылочной целостности используются первичные (primary key) и внешние ключи (foreign key). Отношение первичного ключа к внешнему ключу определяет домен допустимых значений внешнего ключа.
Целостность на уровне пользователя обеспечивалась на стадии проектирования базы данных, путем выбора определенных типов данных и размера этих данных. Кроме того, в денормализованных отношениях целостность обеспечивается программным путём, то есть при записи и считывании данных из базы используется метод преобразования в общепринятый формат.
Блок подготовки системы к работе, выполняющий служебные функции начинает работать при запуске системы и, выполнив свою задачу – отключается.
|
