Классификация таблиц в реляционных базах данных по признакам целостности и избыточности данных

Содержание статьи


Обоснование статьи и некоторые ключевые понятия;
1. Справочники и связки;
1.1. Виды таблиц;
1.2. Виды справочников;
1.3. Виды связок;
2. Обобщение классификации;
2.1. Классификация в табличном виде;
2.2. Классификация в схематичном виде;
3. Некоторые комментарии по применению классификации;
3.1. Применение классификации при нормализации таблиц;
Заключение.

Обоснование статьи и некоторые ключевые понятия


Очень часто присутствовал на обучении дисциплине «Базы данных». Обучался когда-то сам… Как-то даже пришлось проводить целый курс для друзей и знакомых. Во время обучения мною было замечено, что трудности возникают уже на этапе понимания таблиц и того, как ими пользоваться. Многие просто не могли и не могут разработать простейшие базы данных. После более детального рассмотрения такого понятия как таблицы и маленькой классификации, трудности восприятия таблиц в реляционных базах данных почти всегда исчезают. Итак!

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

Для понимания дадим краткие определения целостности и избыточности данных:

Целостность данных – это свойство способности по одним данным восстанавливать другие, при этом не теряя семантическое единство этих данных и отношения между ними (между данными).

Избыточность данных – это состояние базы данных, при котором в таблицах присутствуют лишние данные.

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

1. Справочники и связки


1.1. Виды таблиц


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

Справочники и связки
Рисунок 1. Справочники и связки

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

В справочниках содержатся сведения об объектах и субъектах, связях. В связках содержатся сведения о действиях, процессах, событиях и так далее.

В связках хранятся данные, взятые из таблиц справочников. Поскольку невыгодно повторять одни и те же данные при описании объектов (субъектов) и при описании их взаимодействия, данные об объектах (субъектах) заносятся в справочники, а в таблицах-связках не хранятся данные объектов (субъектов) в чистом виде, а лишь ссылки на них (внешний ключ). Таким образом, в связках хранятся данные по взаимодействию объектов (субъектов) и ссылки на самих объектов (субъектов) (внешний ключ). Эти «ссылки» являются первичными ключами в таблицах справочниках. Но об этом потом…

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

1.2. Виды справочников


Справочники могут подразделяться на несколько видов. Это статичные, статично-динамичные и динамичные справочники. Разумеется, вряд ли можно назвать абсолютно статичный справочник, так как в этом мире может измениться всё. Или почти всё.

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

Примером таких справочников могут служить список месяцев с названиями и номерами, список дней недели, список времён года, список океанов и так далее…

Номер Наименование
1 Январь
2 Февраль
3 Март
4 Апрель
5 Май
6 Июнь
7 Июль
8 Август
9 Сентябрь
10 Октябрь
11 Ноябрь
12 Декабрь

Таблица 1. Пример статичных справочников

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

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

Код должности Оклад Дата обновления
1001 12 000 05.02.2015
1002 17 000 01.02.2015
1003 11 500 01.02.2015
1004 25 450 01.02.2015
1005 10 000 01.02.2015
1006 6 000 04.02.2015

Таблица 2. Пример статично-динамичных справочников

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

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

Код проекта Проект Нормативный срок выполнения Дата добавления Пользователь
PT102 Покраска окон 15 03.01.2014 1547
PT103 Установка дверей 10 04.01.2014 9874
PT587 Проверка пожарных кранов 2 04.01.2014 1456
PT588 Замена люков 3 02.01.2014 0147
PT133 Очистка каналов 11 09.02.2015 1547

Таблица 3. Пример динамичных справочников

Виды справочников
Рисунок 2. Виды справочников

1.3. Виды связок


Таблицы-связки можно разделить на два вида.

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

Примером справочника-связки будет являться таблица платёжных транзакций. Или таблица с данными о футбольном матче.
Код транзакции Плательщик Получатель Сумма Дата Комментарий
EEVS-doodi4 100045 57457 -10 000 25.07.2014 На сапоги
UDFD-ioeed9 455780 10024 -900 24.06.2014 NULL
PEDD-jdksl4 144770 56698 -6980 01.01.2015 NULL
FDFE-keiiii0 447757 1 120 08.07.2014 NULL

Таблица 4. Пример справочника-связки

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

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

Код Код клиента Показания счётчика Месяц
2334 35643 50 01.01.2015
2335 235673 49 01.01.2015
2335 436345 56  01.01.2015
2335 574733 24  01.01.2015

Таблица 5. Пример связки

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

Виды связок
Рисунок 3. Виды связок

2. Обобщение классификации


2.1. Классификация в табличном виде


Вид таблицы  Описание  Примеры  Плюсы (+) Минусы(-) 
 Статичный справочник  Таблица. Данные из неё берутся для других таблиц. Из справочника в других таблицах можно использовать только первичный ключ. В статичном справочнике должна содержаться информация, которая либо вообще не изменяется, либо изменяется так редко, что этим можно принебречь. На статичный справочник ссылаются (внешний ключ), когда нужно получить названия, обозначения, нормы, количественные или качественные показатели. Иное.  Справочник (наименований и номеров) месяцев.
Справочник складов и цехов предприятия.
Справочник правил игры.  
 Иногда заменяет системные функции СУБД, позволяет более гибко работать с некоторыми данными. В случае, если меняется редко изменяемая информация, предостерегает от серьёзных последствий.  Использование таблицы с любой структурой может замедлять работу, в случае, если таблица заменяет системное хранилише.
Приходится писать дополнительные функции и обработки для данной таблицы, которые не всегда правильно оптимизированны. В некоторых случаях невозможно оптимизировать.
 Статично-динамичный справочник  Таблица. Данные из неё берутся для других таблиц. Из справочника в других таблицах нельзя использовать внешний ключ этого справочника, однако можно использовать первичный ключ.  Справочник окладов по должностям. Справочник (размеров обуви, веса, роста, размера головы) физиологических параметров. Справочник (менеджеров, компаний) содержащий компании и менеджеров, которые эти компании обслуживают и учитывают.  Позволяет проводить гибкую нормализацию по схеме «Справочник-связка» = «Связка»+«Статично-динамичный справочник». Справочник, выделенный из справочника-связки, никуда не девается и не имеет никакой реляционной связи, которая позволила бы ему превратиться в статичный или динамичный справочник. А значит, всегда избыточен.
Динамичный справочник Таблица. Данные из неё берутся часто для других таблиц. Из справочника в других таблицах можно использовать только первичный ключ. В динамичном справочнике должна содержаться информация, которая часто изменяется. Справочник клиентов. Справочник поставщиков. Справочник контрагентов. Справочник менеджеров компании. Справочник работников. Справочник студентов.  Позволяет хранить динамичные данные, при этом давая возможность однозначно ссылаться на них. Чаще всего накопительного типа и не делим, что создаёт определённую избыточность.
Справочник-связка Таблица. Данные из неё не могут содержаться в других таблицах, но на основе них могут быть созданы данные в других таблицах. Платёжные транзакции. Продажи. Межзаводские перемещения. График перевозок. Позволяет проводить гибкую нормализацию по схеме «Справочник-связка» = «Связка»+«Статично-динамичный справочник». Справочник-связка после нормализации превращается в связку и сводит избыточность данных к минимуму, не затрагивая целостность, однако не делим и при архивировании в текущей таблице не подлежит оптимизации.
Связка Таблица. Данные из неё не могут содержаться в других таблицах, но на основе них могут быть созданы данные в других таблицах.  Таблица не может содержать кортежей, значения атрибутов в которых являются неделимыми и не уникальными.  Автоматический лог ошибок в программе. Лог запроса сервера. Результаты трассировок. Отчёты о выгрузке и загрузке компонентов. Автоматические отчёты системы безопасности. Связка сводит избыточность данных к минимуму, не затрагивая целостность. Накапливаясь, является неделимой таблицей. Сложно оптимизировать.

Таблица 6. Классификация

2.2. Классификация в схематичном виде


Общая схема
Рисунок 4. Схема классификации таблиц в реляционных базах данных по признакам целостности и избыточности данных

3. Некоторые комментарии по применению классификации


3.1. Применение классификации при нормализации таблиц


Процесс нормализации, если не учитывать некоторые этапы (Но учитывать результаты этих этапов!) — это обычное «дробление» таблиц на более мелкие таблицы с созданием реляционной связи между ними непосредственно или через промежуточные таблицы (связь «Многие ко многим»). Под реляционной связью может не всегда пониматься реляционное отношение!

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

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

Заключение


Данная классификация была создана мной на основе наблюдений при проектировании баз данных, а также исходя из прочитанной теории по проектированию в реляционных СУБД. Моим друзьям и знакомым, изучающим дисциплину «базы данных» и занимающимся проектированием баз данных, и мне эта классификация достаточно серьёзно упростила «жизнь» и позволила во многих ситуациях заранее выбрать наиболее подходящий и, как оказывалось потом, правильный вид таблицы для хранения в ней тех или иных данных.

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

Надеюсь, кому ни будь ещё поможет эта классификация при освоении дисциплины «Базы данных» и при проектировании баз данных в реляционных СУБД.

Похожие публикации

AdBlock похитил этот баннер, но баннеры не зубы — отрастут

Подробнее
Реклама

Комментарии 21

    +1
    Целостность данных – это свойство способности по одним данным восстанавливать другие, при этом не теряя семантическое и реляционное единство этих данных.

    Вообще-то, целостность данных — это всего лишь отсутствие ошибок в них.

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

    В справочниках содержатся сведения об объектах и субъектах, связях. В связках содержатся сведения о действиях, процессах, событиях и так далее.

    Откуда вы взяли это деление?

    Статично-динамичный справочник – справочник, в котором хранятся данные о связях.

    Связях чего с чем?

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

    Что в этих полях «справочного»? Что вы вообще понимаете под «справочником»?

    А теперь простой встречный вопрос: чем вам не угодило стандартное разделение на сущности и связи?
      0
      Откуда вы взяли это деление?

      Опытно-экспериментальным путём, маленьким теоретическим исследованием. Понимаю, что есть много теорий, которые могут объяснить лучше и эффективнее. Думаю, что данное деление — это вариант, который просто имеет права существовать.

      Связях чего с чем?

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

      Что в этих полях «справочного»? Что вы вообще понимаете под «справочником»?

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

      Касаемо полей, которые являются справочными, но в таблицу-справочник не выделены. Иногда из таких полей получаются полноценные справочники. Например, есть таблица, в которой присутствует поле «жалоба». Бывают ситуации, когда в такое поле начинают вписывать одно и то же (смысл одинаковый — конструкции разные), но, естественно, разными синтаксическими конструкциями. Если замечена тенденция по уменьшению частоты появления новых данных в таком поле, то данные, которые в нём существуют, систематизируют и выделяют в отдельный справочник. В дальнейшем для хранения данных по жалобам используются уже внешние ключи. Такое бывает…

      А теперь простой встречный вопрос: чем вам не угодило стандартное разделение на сущности и связи?

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

        Зачем нужно это определение? Почему просто не говорить «сущность»?

        На основе этого разделения эта классификация и описана.

        Нет. Вы оперируете «справочниками». А в сущностях такого понятия нет.
          0
          Действительно, такого понятия в сущностях нет. Зато есть само понятие «сущность».
          А справочники здесь описываются, как списки, в которых эти сущности перечисляются.
            0
            А справочники здесь описываются, как списки, в которых эти сущности перечисляются.

            Зачем вводить это понятие?

            И да, то, что у вас называется «связками» — внезапно, тоже сущности в парадигме сущностей-связей.
              0
              Согласен с Вами.
              А понятия вводятся для разделения сущностей. Ведь, если брать в общем, то каждая таблица описывает сущность.
              Но в одних таблицах описываются действия, а в других сущности, которые в этих действиях принимают участие. И так далее…
                0
                Это разделение надумано и только запутывает. В терминах модели не существует «действий, в которых принимает участие сущность», там есть только сущности, описывающие предметную область (в том числе и то, что вы считаете действиями). Как следствие, парадигма сущность-связь прекрасно на это ложится.

                А в вашей парадигме немедленно возникает вопрос: почему транзакция — это «связка» (а вес — справочник)?

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

          Сущностей с друг другом через какое либо взаимодействие.

          У вас в качестве примера приводится справочник окладов. Это связь каких сущностей друг с другом?
            0
            Виноват, не уточнил у Вас, что Вы имели в виду, когда задавали вопрос «Связях чего с чем?».
            Если речь о справочниках, то они связи не хранят. Связи (согласно классификации, о которой сейчас говорим) описываются в таблицах-связках.
              0
              Вообще-то, это была цитата из вашего текста.

              Статично-динамичный справочник – справочник, в котором хранятся данные о связях.

              Так справочники хранят связи или нет?
                0
                Здесь следует уточнить, чтобы не было такой путаницы.
                Вполне вероятно, что мне не следовало выбирать в качестве понятия слово «связка» или уточнять, то есть, очень аккуратно описывать.

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

                И да, действительно, по данной классификации, это способен хранить только статично-динамичный справочник.

                Вообще, любая таблица может связи хранить. Достаточным подтверждением этому является то, что в справочнике могут храниться внешние ключи (при чём в любом). Примером такого может быть внешний ключ — идентификатор пользователя, который модифицировал данные. Но это, опять же, немного другое…
                  0
                  А как вы отличаете «справочный» характер от «несправочного»?

                  Понимаете, ваше деление надумано, и никакой пользы не несет (приведите контраргумент, если я не прав); зато в нем существенно больше одной неоднозначности, которая только усугубляет путаницу.
                    0
                    Как раз сейчас дописал различие в статью:

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


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

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

                    Почему структура не является надуманной? Потому что она взята на основе существующей. Понял, что смущает использование терминов «связи», «связка». И вообще пересечение терминов, которые отличаются друг от друга значениями…

                    Можно привести хороший пример. Сущность может описывать связь между сущностями (менеджер, клиент, продажа). Продажа является сущностью, описывающей связь менеджера и клиента.

                    Если брать данную классификацию, то был бы справочник менеджеров, справочник клиентов и связка «продажи».
                    Менеджеры и клиенты были бы в динамичном справочнике. Продажи в справочнике-связке (напомню, справочник-связка справочником в данной классификации не является!).

                    Кто-то поймёт про сущность, которая описывает связь между сущностями.
                    А кому-то будет легче сообразить, что менеджер — это субъект, клиент — это субъект. А продажи — это действия, в которых участвуют менеджер и клиент, они же образуют их связку.

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

                    Классификация, если хотите, маленько уточняет, какие виды сущностей бывают (частично).

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

                      Приведите конкретный пример.

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

                      Эта трактовка ошибочна. Продажа — это сущность, описывающая событие. В числе атрибутов этого события есть менеджер и клиент.

                      Если брать данную классификацию, то был бы справочник менеджеров, справочник клиентов

                      Это только в том случае, если клиенты — это сущности, что далеко не всегда так.

                      как это сущность может описывать связи между сущностями.

                      Никак. Есть сущности, есть связи. Сущность не описывает связи.
        +1
        какую структуру таблиц можно делать, чтобы [...] добиваться целостности в реляционных базах данных.

        И ни слова про встроенные механизмы СУБД по поддержанию целостности?
          0
          По вашей статье же справочник-связка/связка одно и тоже. В первом случае плательщик + получатель — внешние ключи и в то же время первичный ключ, во второй код + код клиента — внешние ключи и в то же время первичный ключ. Если же у вас в первом случае первичным ключом является код транзакции, а плательщик + получатель лишь как внешние ключи, то не понимаю, зачем так делать.
            0
            Во время обучения мною было замечено, что трудности возникают уже на этапе понимания таблиц и того, как ими пользоваться.

            Совсем неудевительно что у людей возникают трудности в понимании :) Это-ж надо умудриться так сложно написать про простые вещи.
              +1
              Как часто должен меняться мой вес, чтобы начать его записывать в статично-динамичные справочники вместо статичных справочников? Серьезно, я последние лет 10 занимаюсь (в том числе) проектированием БД, но ни хрена в вашей статье не понял, кроме первого абзаца. К людям и правда приходит просветление после определений вроде «Избыточность данных – это состояние базы данных, при котором в таблицах присутствуют лишние данные.»?
              Ладно бы еще «Избыточность данных – это состояние базы данных, при котором в таблицах присутствуют избыточные данные.», по прежнему определение не содержит новой информации, но по крайней мере не ошибочное (если в текущей структуре БД дублируются данные это не делает их лишними, говорить так ошибка). Может на практике вы как-то ловко все это комментируете, но сам по себе текст по моему ничего кроме каши в голове не создаст.
                0
                Один-к-одному, один-ко-многим, многие-ко-многим.
                Уникальные ключи, первичные ключи, внешние ключи.
                1NF, 2NF, 3NF, BCNF, 4NF и др.

                Этого достаточно.
                «Просто, понятно, вольготно» (с) Высоцкий

                Вы больше запутываете, чем классифицируете.
                  0
                  Преподаю базы данных в университете лет 15. У Вас какая-то странная классификация, мягко выражаясь. Существует методология ER(Entity-Relation), где есть весьма понятная классификация, как таблиц, так и связей. Студенты ее с первого раза легко усваивают.

                  И определения у Вас просто атас! Что такое «реляционное единство»?
                    0
                    Согласен с Вами, что определение было дано «размытое». Поправил.
                    Фразу «реляционное единство» заменил на «отношения».
                    Под семантическим единством здесь понимается смысловая связь (как раз связь сущностей).
                    А под отношениями понимаются уже выраженные в логических (математических) закономерностях связи между данными (в реляционных СУБД — отношения между таблицами).

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

                  Самое читаемое