Как назвать таблицу с данными

Правила именование объектов SQL: таблицы и столбцы

У каждого объекта в базе данных есть уникальное имя. Имена используются в инструкциях SQL и указывают, над каким объектом базы данных должно быть выполнено действие. Фундаментальными именованными объектами в реляцион­ной базе данных являются имена таблиц, столбцов и пользователей; правила их именования были определены еще в стандарте SQL1. В последующих версиях стандарта этот список был значительно расширен и теперь включает схемы (коллекции таблиц), ограничения (ограничительные условия, накладываемые на содержимое таблиц и их отношения), домены (допустимые наборы значений, ко­торые могут быть занесены в столбец) и ряд других типов объектов. Во многих СУБД существуют дополнительные виды именованных объектов, например хра­нимые процедуры, отношения «первичный ключ-внешний ключ», формы для ввода данных и схемы репликации данных.

В соответствии с первоначальным стандартом ANSI/ISO, имена в SQL должны содержать от 1 до 18 символов, начинаться с буквы и не могут содержать пробельные символы или специальные символы пунктуации. В стандарте SQL2 максимальное число символов в имени увеличено до 127 (дословно в стандарте сказано «менее 128»), и это количество остается неизменным до последнего стандарта SQL:2006. На практике поддержка имен в различных СУБД реализована по-разному. Чаще всего приходится сталкиваться с ограничениями на имена, связанные с другим программ­ным обеспечением вне базы данных (например, имена пользователей, которые могут совпадать с именами, используемыми операционной системой). Различные продук­ты отличаются и по отношению к применению в именах специальных символов. С точки зрения переносимости, лучше воздержаться от длинных имен и не приме­нять в них никаких специальных символов, за исключением символа подчеркива­ния, который используется для разделения слов в именах SQL.

Имена таблиц

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

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

Стандарт ANSI/ISO SQL еще больше обобщает понятие квалифицированного имени таблицы. Он разрешает создавать именованное множество таблиц, назы­ваемое схемой. Вы можете обращаться к таблице определенной схемы с использо­ванием квалифицированного имени. Например, обращение к таблице BIRTHDAYS в схеме employee_info имеет следующий вид.

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

Имена столбцов

Если в SQL-инструкции указано имя столбца, обычно SQL сам в состоянии оп­ределить, в какой из указанных в этой же инструкции таблиц содержится данный столбец. Однако если в инструкцию требуется включить два столбца из различных таблиц, но с одинаковыми именами, необходимо указать квалифицированные имена столбцов, которые однозначно определяют их местонахождение. Такое квалифи­цированное имя столбца состоит из имени таблицы, содержащей столбец, и имени столбца, разделенных точкой. Например, полное имя столбца SALES из таблицы SALESREPS имеет такой вид:

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

Источник

Как назвать таблицу с данными

«Всякое приказание должно быть отдано
в должное время, в должном месте,
и в выражениях, исключающих двоякое толкование»
(Из Устава Петровских времен)

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

Выбор имени сущности

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

Итак, почему как можно короче? Потому что на основании имени сущности мы будем «лепить» другие идентификаторы. Они будут встречаться целыми списками в секциях FROM, WHERE. И если названия таблицы и пяти соединенных справочников будут несокращенными словами в 16-20 символов, то это порадует лишь поклонников языка Шекспира, но успешно затуманит смысл простейших SQL-запросов.

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

Наименование таблиц

Если вспомнить, что мы хотим использовать имя сущности также в программах обработки данных, где объектом работы становится именно «единица» информации (например в названии класса для хранения одной улицы TStreet, а затем в названии переменой-объекта objStreet1), то разумнее остановиться на единственном числе как на стандарте. Но ведь в таблице все-таки живет множество сущностей! Как это выразить? Для таблиц примем следующее волюнтаристское решение:

Не будем также забывать, что в качестве имен сущностей мы разрешили себе использовать сокращения и аббревиатуры. А в этом случае добавление «s» или «es» вряд ли добавит ясности и читабельности.

Наименование полей

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

Ну хорошо, не унимаются минималисты. С ключами понятно. А названия, и прочие неключевые поля? Их-то можно назвать просто Name?

Хорошо. Назовем названия города, улицы и типа улицы просто Name, и составим запрос, выводящий «Улица Ленина, Москва».

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

Множественные ссылки

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

Другие объекты базы данных

Источник

Как создавать таблицы в MySQL (Create Table)

Введение

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

Ниже разберем подробнее, как реализовать этот короткий список для MySQL наиболее эффективно.

Синтаксис Create table в MySQL и создание таблиц

Поскольку наш путь в базы данных только начинается, стоит вспомнить основы. Реляционные базы данных хранят данные в таблицах, и каждая таблица содержит набор столбцов. У столбца есть название и тип данных. Команда создания таблицы должна содержать все вышеупомянутое:

table_name — имя таблицы;

column_name — имя столбца;

column_type — тип данных столбца.

Теперь разберем процесс создания таблицы детально.

Названия таблиц и столбцов

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

Имена могут содержать символы подчеркивания для большей наглядности. Классический пример непонятных названий — table1, table2 и т. п. Использование транслита, неясных сокращений и, разумеется, наличие орфографических ошибок тоже не приветствуется. Хороший пример коротких информативных названий: Customers, Users, Orders, так как по названию таблицы должно быть очевидно, какие данные таблица будет содержать. Эта же логика применима и к названию столбцов.

Максимальная длина названия и для таблицы, и для столбцов — 64 символа.

Типы данных столбцов

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

Числовые типы

Символьные

Дата и время

Бинарные

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

Подробный разбор типов данных, включая более специализированные типы, например, ENUM, SET или BIGINT UNSIGNED, будет в отдельной тематической статье.

Практика с примерами

Для лучшего понимания приведем пример, создав простую таблицу для хранения данных сотрудников, где

Синтаксис create table с основными параметрами:

Тут могут появиться вопросы. Откуда MySQL знает, что номер уникален? Если еще нет должности для этого сотрудника, что будет, если оставить поле пустым?
Все это (как и многое другое) придtтся указать с помощью дополнительных параметров — атрибутов.

Часто таблицы создаются и заполняются скриптами. Если мы вызовем команду CREATE TABLE Staff, а таблица Staff уже есть в базе, команда выдаст ошибку. Поэтому перед созданием разумно проверить, содержит ли уже база таблицу Staff. Достаточно добавить IF NOT EXISTS, чтобы выполнить эту проверку в MySQL, то есть вместо

Повторный запуск команды выведет предупреждение:

Если таблица уже создана и нужно создать таблицу с тем же именем с «чистого листа», старую таблицу можно удалить командой:

Возможности SQL в «Облачных баз данных»

Атрибуты (ATTRIBUTES) и ограничения (CONSTRAINTS)

PRIMARY KEY

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

Пользы индексов на примерах: для поиска уникального значения среди 10000 строк придется проверить, в худшем случае, все 10000 без индекса, с индексом — всего 14. Поиск по миллиону записей займет не больше в 20 проверок — это реализация идеи бинарного поиска.

Создадим таблицу Staff с номером сотрудника в качестве первичного ключа. Первичный ключ гарантирует нам, что номер точно будет уникальным, а поиск по нему — быстрым.

NOT NULL

При заполнении таблицы мы утверждаем, что значение этого столбца должно быть установлено. Если нет явного указания NOT NULL, и этот столбец не PRIMARY KEY, то столбец позволяет хранить NULL, то есть хранение NULL — поведение по умолчанию. Для первичного ключа это ограничение можно не указывать, так как первичный ключ всегда гарантирует NOT NULL.

Изменим команду CREATE TABLE, добавив NOT NULL ограничения: таким образом, мы обозначим обязательные для заполнения столбцы (т.е. столбцы, поля в которых не могут оставаться пустыми при наличии записи в таблице):

DEFAULT

Можно указать значение по умолчанию, т.е. текст или число, которые будут сохранены, если не указано другое значение. Применяется не ко всем типам: BLOB, TEXT, GEOMETRY и JSON не поддерживают это ограничение.
Эта величина должна быть константой, функция или выражение не допустимы.

Продолжим изменять команду, установив ограничение DEFAULT для поля BOOLEAN.

Для типа данных BOOLEAN можно использовать встроенные константы FALSE и TRUE. Вместо DEFAULT(FALSE) можно указать DEFAULT(0) — эти записи эквивалентны.

AUTO_INCREMENT

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

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

Интересно, что при CREATE TABLE MySQL не позволяет установить стартовое значение для AUTO_INCREMENT. Можно назначить стартовое значение для счетчика AUTO_INCREMENT уже созданной таблицы.

Первая запись после такой модификации получит >

UNIQUE

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

CHECK

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

Синтаксис позволяет устанавливать CHECK как в описании столбца при CREATE TABLE:

так отдельно от описания столбцов:

В этих случаях название проверки будет определено автоматически. При вставке данных, не прошедших проверку, будет сообщение об ошибке Check constraint ‘staff_chk_1’ is violated. Ситуация усложняется, когда установлено несколько CHECK, поэтому рекомендуется давать понятное имя.

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

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

FOREIGN KEY или внешний ключ

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

Сначала указывается выражение FOREIGN KEY и набор столбцов таблицы, откуда строим FOREIGN KEY. Затем ключевое слово REFERENCES указывает на имя внешней таблицы и набор столбцов этой внешней таблицы. В конце можно добавить операторы ON DELETE и ON UPDATE, с помощью которых настраивается поведение при удалении или обновлении данных в главной таблице. Это делать не обязательно, так как предусмотрено поведение по умолчанию. Поведение по умолчанию запрещает удалять или изменять записи из внешней таблицы, если на эти записи есть ссылки по внешнему ключу.

Возможные опции для ON DELETE и ON UPDATE:

CASCADE: автоматическое удаление/изменение строк зависимой таблицы при удалении/изменении связанных строк главной таблицы.
SET NULL: при удалении/изменении связанных строк главной таблицы будет установлено значение NULL в строках зависимой таблицы. Столбец зависимой таблицы должен поддерживать установку NULL, т.е. параметр NOT NULL в этом случае устанавливать нельзя.
RESTRICT: не даёт удалить/изменить строку главной таблицы при наличии связанных строк в зависимой таблице. Если не указана иная опция, по умолчанию будет использовано NO ACTION, что, по сути, то же самое, что и RESTRICT.

Рассмотрим пример:
Для таблицы Staff было определено текстовое поле position для хранения должности.
Так как список сотрудников в компании обычно больше, чем список занимаемых должностей, есть смысл создать справочник должностей.

Поскольку из Staff мы будем ссылаться на Positions, таблица персонала Staff будет зависимой от Positions. Изменим синтаксис CREATE TABLE для таблицы Staff, чтобы должность была ссылкой на запись в таблице Positions.

При CREATE TABLE, чтобы не усложнять описание столбца, рекомендуется указывать внешний ключ и все его атрибуты после перечисления создаваемых столбцов.
Можно ли добавить внешний ключ, если таблица уже создана и в ней есть данные? Можно! Для внесения изменений в таблицу используем ALTER TABLE.

Или в развернутой форме, определяя имя ключа fk_position_id явным образом:

Главное условие в этом случае — согласованность данных. Это значит, что для всех записей внешнего ключа position_id должно найтись соответствие в целевой таблице Positions по столбцу id.

Создание таблиц на основе уже существующих, временные таблицы

Мы рассмотрели создание таблицы с «чистого листа», но есть два других способа:

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

SELECT

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

Разберем пример создания новой таблицы через SELECT, используя две таблицы в выборке — Staff и Positions. В запросе определим три поля: id, staff, position — это будут столбцы новой таблицы StaffData211015 (срез сотрудников на определённую дату). Без присвоения псевдонимов (name as staff, name as position) в выборке получилось бы два одинаковых поля name, что не позволило бы создать таблицу из-за duplicate column name ошибки.

TEMPORARY

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

Чтобы обозначить таблицу как временную, нужно добавить TEMPORARY в CREATE TABLE:

Работа с уже созданной таблицей

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

Переименование

Ключевая команда — RENAME.

Удаление данных

Изменение структуры таблицы

Команда ALTER TABLE включает в себя множество опций, рассмотрим основные вместе с примерами на таблице Staff.

Добавление столбцов

Добавим три столбца: электронную почту, возраст и наличие автомобиля. Так как в таблице уже есть записи, мы не можем пока что отметить эти поля как NOT NULL, по умолчанию они будут позволять хранить NULL.

Удаление столбцов

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

Значение по умолчанию

Выставим значение по умолчанию для столбца has_auto:

Изменение типа данных столбца

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

Максимальная длина поля была увеличена. Если не указать NOT NULL явно, то поле станет NULL по умолчанию.

Установка CHECK

Добавим ограничение формата для email через регулярное выражение:

Заключение

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

Источник

Название БД и её таблиц (влияние регистра букв)

Могу поспорить о том, что многие программисты PHP ведут свои локальные разработки на компьютерах под управлением операционной системы семейства Windows (я сам такой, но об этом сильно жалею). И наверное никто и не задумывался написать название БД или её таблиц с большой буквы, сколько я встречал БД все они имеют формат слово_слово, по правилам MySQL они должны начинаться с буквы, могут иметь цифры и знак подчеркивания «_» и прочие знаки. Даже если обратится к документации MySQL, то видно, что и там также применяется подобный формат.

И мало ли кто задумывался о создании таблицы или БД с Большой буквы. А надо было.

Если написать в Windows, что что таблица будет с большой буквы

то в файловой системе, где хранятся данные о таблице, сами файлы имеют такое же написание, как и название таблицы, т.е. НАЗВАНИЕ ТАБЛИЦЫ ВЛИЯЕТ НА НАЗВАНИЕ ФАЙЛА и ессно зависят от РЕГИСТРА. Получается, что при обращении к таблице из ОС Windows

ответ будет правильным, нам MySQL возвратит какие-то данные, но не дай Боже написать второй запрос в ОС *nix подобных системах — сразу будет исключение! Догадались чего. Конечно в ОС *nix создание файлов регистрозависимое, и когда мы в запросе пишем название таблицы отличное от созданного, то не находится такой файл и вылетает ОШИБКА. Иногда приходится часы тратить не понимая какого у меня локально все зашибись, а на серваке по *nix все х…во. Да и вообще, если мы юзаем РНР, то и должны писать под ОС на которой будет выполняться код!!

А вот название полей пофиг какие будут буквы, также как и название операторов.

Изначально под виндой запрещено вводить большие буквы в название таблиц))), а вот иногда ну надо. Как же быть??

Воть! Все легко и просто — lower_case_table_names настраиваем и можем делать, что угодно.

Удачного, правильного и чистого подхода.

Статья просмотренна 48339 раз, зашло посетителей 16463

178 4 комментария http%3A%2F%2Fblog.nagaychenko.com%2F2010%2F01%2F13%2F%25d0%25bd%25d0%25b0%25d0%25b7%25d0%25b2%25d0%25b0%25d0%25bd%25d0%25b8%25d0%25b5-%25d0%25b1%25d0%25b4-%25d1%2582%25d0%25b0%25d0%25b1%25d0%25bb%25d0%25b8%25d1%2586-%25d0%25b8-%25d0%25b2%25d0%25bb%25d0%25b8%25d1%258f%25d0%25bd%25d0%25b8%25d0%25b5-%25d1%2580%25d0%25b5%25d0%25b3%25d0%25b8%25d1%2581%25d1%2582%25d1%2580%25d0%25b0%2F %D0%9D%D0%B0%D0%B7%D0%B2%D0%B0%D0%BD%D0%B8%D0%B5+%D0%91%D0%94+%D0%B8+%D0%B5%D1%91+%D1%82%D0%B0%D0%B1%D0%BB%D0%B8%D1%86+%28%D0%B2%D0%BB%D0%B8%D1%8F%D0%BD%D0%B8%D0%B5+%D1%80%D0%B5%D0%B3%D0%B8%D1%81%D1%82%D1%80%D0%B0+%D0%B1%D1%83%D0%BA%D0%B2%29 2010-01-13+21%3A05%3A10 maxnag http%3A%2F%2Fblog.nagaychenko.com%2F%3Fp%3D178 в “Название БД и её таблиц (влияние регистра букв)”

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

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

Ну если посмотреть с точки зрения компьютера буква А и а всё же разные буквы….

Источник

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Примером справочника-связки будет являться таблица платёжных транзакций. Или таблица с данными о футбольном матче.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Заключение

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

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

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

Источник