Пример переработки обычной таблицы в БД

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

И это тоже не совсем так, ибо при преобразовании данных использованы дополнительные рассуждения. Обычно же используется подход с искусственно подобранными примитивными данными, просто удобными для разбора. За счет этого выхолащивается прикладной смысл, работа же с реальной информацией мгновенно выявляет множество вопросов, способы решения которых совершенно неочевидны.

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

№ п/пФИОД/рПолПодразде­лениеДолжностьОбразованиеОкладОтпускПриказ о приемеПодчи­нение
1.Иванов Иван Иванович01.01.1960МАдмини­страцияДиректорВысшее, МГУ20000010.06.20- 10.07.20№10 01.02.10
2.Лайкина Анна Александровна14.09.1972МАдмини­страцияЗам.директораВысшее, МАИ10900010.06.20- 10.07.20№16 15.07.19Директор
3.Петров Петр Петрович21.07.1970МАдмини­страцияНач. АХЧВысшее, АНХ8000020.09.20№702 25.10.20Директор
4.Яковенко Софья Александровна28.09.2001ЖАдмини­страцияСекретарьСреднее30000№61 20.07.20Директор
5.Сидоров Сидор Сидорович17.02.1980МГаражНач. гаражаВысшее, МАДИ60000№18 20.01.16Нач. АХЧ
6.Ткач Николай Иванович10.11.1970МГаражВодительСреднее40000№702 25.10.20Нач. гаража
7.Пак Михаил Ильич20.07.1990ГаражМеханикСреднее профессио­нальное45000№702 25.10.20Нач. гаража

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

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

Итак, на роль главной однозначно претендует таблица Люди (множественное число!, можно заменить на Сотрудники). Одна запись будет описывать одного человека (единственное число!).

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

ФИО мы пока обсудим в упрощенном виде. Речь идет о том что данная характеристика будет у каждого человека. И, в большинстве случаев, она будет индивидуальной. Вопрос полных тезок мы оставим за рамками, хотя в реальной ситуации это учитывать нужно! Принципиальными вопросами будут: 1) информация относится к символьному типу; 2) любое имеющееся (надо исследовать) и перспективное (нужно думать и изучать всю связанную тематику) сочетание должно поместится в отводимый размер поля.

В любом случае, при озвученной постановке оно остается в главной таблице.

Подробные (и гораздо более разумные) рассуждения о ФИО приведены по ссылке: Фамилия, имя и отчество.

Дата рождения, естественно также индивидуальна и пытаться вынести ее во вспомогательные таблицы всегда будет ошибкой. Тип — дата.

Пол. Уже из самой таблицы следует мизерность показателя. Напрашивается традиционный вариант: логический тип, где true — мужчина, а false — женщина. Но он не лишен недостатков. Представьте себе ситуацию, когда данные вам неизвестны, а по ФИО их определить затруднительно. Большинство БД позволяет хранить тип данных NULL, что решит нашу проблему, но можно добавить мировую причуду со средним полом или отказом от его указания. Поскольку логический тип данных требует все равно 1 байта, намного разумнее использовать любой тип данных такого же размера (числовой или символьный). Размещение — в главной таблице.

Подразделение. Здесь мы уже видим, что сотрудники относятся к нескольким повторяющимся подразделениям. Поскольку мы встречаемся с этим первый раз, давайте разберемся с вопросом подробно.

Самое длинное из наименований, которое мы видим в таблице (надо исследовать) — Администрация, содержит 13 букв. Отсюда требуется символьное поле длиной 13 символов. Таким образом, данное поле займет 1300 байт для 100 сотрудников, 13000 — для 1000 и т.д.

Наиболее вероятным применением будет выбор всех сотрудников одного из подразделений. Если допустить при вводе сотрудника опечатку любого типа, то он не попадет в выборку. Сведения станут недостоверными.

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

Таблица Подразделений
НаименованиеПоле  ПримечаниеКомментарий
podr_idI(1)Код подразделенияУникальный идентификатор
podr_nameC(13)Наименование подразделения
ruk_idI(4)Идентификатор руководителяsotr_id из таблицы сотрудников

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

Если считать, что перечень подразделений состоит из 50 наименований (характерно для очень большого предприятия), то затрачиваемое место составит: 18 байт на подразделение (1+13+4) и 900 байт на всю таблицу. При этом надо отметить одновременные достаточность и отсутствие избыточности идентификатора (до 256 значений при минимальном размере в 1 байт).

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

Чтобы оценить общие затраты, справочник подразделений надо добавить к объему информации для связи. Так как это будет один байт на человека (поле кода), приводить расчеты не имеет смысла. Зато понятно, что для 100 человек такое не особо выгодно (1300 против 1000). При этом не забываем про ошибки и стоимость их исправления!

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

При каждом перемещении по таблице людей потребуется производить поиск по коду соответствующей строки в таблице подразделений. В результате, например, ФИО будет выведено из главной таблицы, а найденное по коду подразделение — из поля podr_name второй таблицы. По ряду причин, объясняемых отдельно, такой поиск происходит очень быстро, практически мгновенно. Можно сказать, что десятки аналогичных последовательных поисков не будут замечены пользователем.

По идентичным принципам строятся все вспомогательные таблицы.

Должности. На первый взгляд идентично подразделениям, но примитивная запись наименования на самом деле достаточно сложна.

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

Таблица Должностей
НаименованиеПоле  ПримечаниеКомментарий
dalzhn_idI(2)Код должности
dalzhn_nameC(15)Наименование
okladN(10,2)Заработок
edinitsI(2)Единиц в штате

Таблица специально упрощена, так как нам требуется другое.

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

Для реализации подобной возможности потребуется создание дополнительной связывающей таблицы.

Таблица связи сотрудник-должность
НаименованиеПоле  ПримечаниеКомментарий
sotr_idI(4)Код сотрудника
dalzhn_idI(2)Код должности
partДоля ставки
job_typeN(1)Тип работы1 - штат, 2 - внутреннее совместительство 3 - внешнее совместительство
doc_idI(4)Идентификатор документа, позволяющий занимать должностьСм. далее
podr_idI(1)Код подразделения
ruk_idI(4)Идентификатор руководителя для конкретного сотрудникаsotr_id из таблицы сотрудников

Таким образом, для большинства сотрудников эта таблица будет содержать единственную запись. Для совместителей — две или более. Заодно решена проблема описания внешних совместителей.

Чрезвычайно важно, что именно сюда, а не в главную таблицу вынесены идентификаторы подразделения и код руководителя. Ведь, по сути, запись данной таблицы отражает целую или частичную реальную штатную единицу. Она не абстрактна, а относится к конкретному подразделению. Если суммировать здесь всех, например, инженеров (part), то их количество не может превысить штатное расписание (edinits из соответствующей записи таблицы должностей).

Образование. Графа специально заполнена с некоторым намеком.

Во-первых, здесь присутствует уровень образования, который должен быть разделен на:

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

Во-вторых, графа содержит совершенно другую, хотя и связанную информацию, а именно — наименования вузов. Принципиальной же является информация о документе об образовании со всеми его реквизитами.

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

Таким образом, требуется:

Возможный вариант таблицы с учетом некоторых(!) их высказанных положений. Следует еще раз обратить внимание, что для каждого сотрудника обязательна хотя бы одна запись, но их может быть и больше.

Таблица Документов об образовании
НаименованиеПоле  ПримечаниеКомментарий
sotr_idI(4)Код сотрудника
doc_idI(4)Код документа об образовании
doc_linkMСсылка на скан оригинала
edu_level_idN(1)Ссылка на код из справочника уровня образования
doc_serC(5)Серия документаЕсли есть
doc_noC(15)Номер документаСимвольный, так как неизвестен набор знаков. Требует исследования на предмет размера
spec_nameМНаименование специальностиКак в документе
oo_idI(4)Образовательная организацияКод, ссылающийся на соответствующий справочник

Приказ. Внимательный просмотр позволит обнаружить, что несколько человек приняты на работу одним приказом (обычно такого не бывает), что уже можно понять без затруднений: нужна таблица приказов.

В нее можно включить не только номер и дату, но и текстовое содержание и/или ссылку на соответствующий файл.

Для качественной работы потребуется рубрикатор приказов (не обсуждается).

Отпуска. Очевидно, что человек уходит в отпуск ежегодно и эта информация должна быть сохранена.

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

Структура первой таблицы должна быть понятна и не требует обсуждения.

Таблица Приказов
НаименованиеПоле  ПримечаниеКомментарий
prik_idI(4)Код приказа
prik_nameC(100)Название приказа
prik_dateD(8)Дата приказа
prik_noС(10)Номер приказаЗависит от принятой системы делопроизводства
rubr_idI(2)Код ссылки на рубрикатор
prik_linkMСсылка на скан оригинала или текстовый документ

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

С этими и многими задачами поможет справится вспомогательная связывающая таблица.

Таблица связи сотрудник-приказ
НаименованиеПоле  ПримечаниеКомментарий
prik_idI(4)Код приказа
sotr_idI(4)Код сотрудника

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

  1. Выбираем из списков, отображаемых из справочников, подразделение, вид приказа «Отпуск» и год. Результатом станет извлечение из справочника соответствующих кодов (podr_id и rubr_id).
  2. Запросом по podr_id к Таблице связи сотрудник-должность, получаем пока обезличенный перечень сотрудников подразделения (в виде списка sotr_id).
  3. Запросом по rubr_id для отпусков и нужному году, получаем все приказы по предприятию, связанные с отпуском.
  4. Запросом по полученным на 3 этапе кодам сотрудников к таблице связи сотрудник-приказ и списку приказов на отпуск, формируем их соответствие.
  5. Полученный и уже очищенный список приказов дополняем сведениями о сотрудниках (ФИО). В зависимости от цели, можно увидеть тех, у кого приказа нет, отсортировать их по фамилии или дате.

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

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

Подчинение также предполагает возникновение ошибки. В примитивной ситуации оно прекрасно привязывается к должности руководителя из справочника. Но при возрастании числа сотрудников может быть введено несколько должностей, например, заместителей руководителя и такая схема станет неработоспособной. Гораздо более перспективной выглядит схема с указанием конкретного человека. У нее тоже есть недостаток, проявляющийся при необходимости его замены. Но она решается программно.

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

Вместе с тем, проблема уже решена выше (Таблица связи сотрудник-должность).

Главная таблица

В реальной ситуации, формирование именно ее структуры является первичным, что прослеживается по расположенному выше тексту. Однако, это практически невозможно сделать на сайте или в печатном издании.

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

Таблица Сотрудников
НаименованиеПоле  ПримечаниеКомментарий
sotr_idI(4)Уникальный код (идентификатор) сотрудника
sotr_fioC(60)Упрощено
bdD(8)Дата рождения
sexN(1)Пол

Естественно, что множество вопросов остались нерешенными и даже не озвучены. Например, ситуация увольнения.

Схема таблиц

Схема носит условный характер, но позволяет получить общее представление.


Copyright © 1993–2020 Мацкявичюс Д.А. Все права защищены.
Никакая часть сайта не может быть воспроизведена никаким способом без письменного разрешения правообладателя и явной ссылки на данный ресурс.