Как проектировать базу данных: Как спроектировать базу данных, чтобы в будущем не пришлось её переписывать — базовые советы

Содержание

Как спроектировать базу данных, чтобы в будущем не пришлось её переписывать — базовые советы

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

А если вы знакомы с SQL и вас не остановили предыдущие термины, на всякий случай напомним, что:

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

***

Есть минимум два требования, которые должны быть соблюдены при проектировании структуры БД:

  1. Сохранить всю информацию после разделения её на таблицы.
  2. Минимизировать избыточность того, как эта информация хранится.

Примечание Второй пункт важен не только из-за того, что избыточность влияет на размер БД.

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

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

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

Используйте хотя бы третью нормальную форму

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

Нормальных форм существует целых 6 штук, однако обычно соблюдают всего лишь 3 и для начала этого более чем достаточно.

Первая нормальная форма

Для примера будем использовать отношение сотрудники_отделы_проекты. В нём есть информация о номере сотрудника, его фамилии, номере отдела, в котором он работает, номере телефона отдела и так далее.

Это отношение, как и любое другое, автоматически находится в первой нормальной форме:

  • в отношении нет одинаковых кортежей;
  • кортежи не упорядочены;
  • атрибуты не упорядочены и различаются по наименованию;
  • все значения атрибутов атомарны.

Вторая нормальная форма

В нашем случае у таблицы выше имеется сложный (составной) ключ {Н_СОТР, Н_ПРО}. От части ключа

Н_СОТР зависят неключевые атрибуты ФАМ, Н_ОТД, ТЕЛ. От части ключа Н_ПРО зависит неключевой атрибут ПРОЕКТ. А вот атрибут Н_ЗАДАН зависит от всего составного ключа, так как сотрудник может выполнять одно задание в одном проекте.

Поэтому для приведения отношения ко второй нормальной форме из отношения сотрудники_отделы_проекты нужно выделить два отношения сотрудники_отделы и проекты, а исходное отношение оставим отношением задания.

Наконец, третья нормальная форма

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

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

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

Используйте проверочные ограничения

База данных — это не просто набор таблиц. В неё встроено много инструментов, которые помогут с сохранностью и качеством данных.

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

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

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

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

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

Не храните ФИО в одном поле, также как и полный адрес

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

Установите для себя правила именования таблиц и полей

Сложно работать с данными, которые выглядят как-то так: user.firstName, user.last_name, user.birthDate. Конечно, каждый программист в праве сам выбирать для себя стиль наименования, но для SQL рекомендуется выбрать наименование с подчёркиванием. Потому что не все SQL-движки одинаково работают с заглавными буквами, а помещать всё в кавычки бывает утомительно.

Ещё нужно определиться как будут называться таблицы — во множественном числе (users) или в единственном (user). Каждая базовая структура в БД обычно настроена на множественное число, поэтому и именовать таблицы стоит соответственно.

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

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

Перевод статьи «A humble guide to database schema design»

Руководство по проектированию реляционных баз данных (1-3 часть из 15) [перевод]

Перевод цикла из 15 статей о проектировании баз данных.

Информация предназначена для новичков.

Помогло мне. Возможно, что поможет еще кому-то восполнить пробелы.

Другие части: 4-6, 7-9, 10-13, 14-15.

Руководство по проектированию баз данных.
1. Вступление.

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

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

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

INSERT

) информацию в базу данных и мы хотим иметь возможность делать выборку информации из базы данных (

SELECT

).

Язык запросов к базам данных был придуман для этих целей и был назван

Структурированный язык запросов

или SQL. Операции вставки данных (INSERT) и их выборки (SELECT) – части этого самого языка. Ниже приведен пример запроса на выборку данных и его результат.

SQL – большая тема для повествования и его рассмотрение выходит за рамки данного руководства. Данная статья строго сфокусирована на изложении процесса проектирования баз данных. Позднее, в отдельном руководстве, я расскажу об основах SQL.

Реляционная модель.

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

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

Примеры.

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

РСУБД.

РСУБД, которую я использовал для создания таблиц примеров – MySQL. MySQL – наиболее популярная РСУБД и она бесплатна.

Утилита для администрирования БД.

После установки MySQL вы получаете только интерфейс командной строки для взаимодействия с MySQL. Лично я предпочитаю графический интерфейс для управления моими базами данных. Я часто использую SQLyog. Это бесплатная утилита с графическим интерфейсом. Изображения таблиц в данном руководстве взяты оттуда.

Визуальное моделирование.

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

Проектирование независимо от РСУБД.

Важно знать, что хотя в данном руководстве и приведены примеры для MySQL, проектирование баз данных независимо от РСУБД. Это значит, что информация применима к реляционным базам данных в общем, не только к MySQL. Вы можете применить знания из этого руководства к любым реляционным базам данных, подобным Mysql, Postgresql, Microsoft Access, Microsoft Sql or Oracle.

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

2. История.

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

Так выглядели профессионалы в сфере информационных технологий в 70-е. (Слева внизу находится Билл Гейтс).

Текстовые файлы и сегодня все еще используются для хранения малых объемов простой информации. Comma-Separated Values (CSV) — значения, разделённые запятыми, очень популярны и широко поддерживаются сегодня различным программным обеспечением и операционными системами. Microsoft Excel – один из примеров программ, которые могут работать с CSV–файлами. Данные, сохраненные в таком файле могут быть считаны компьютерной программой.

Выше приведен пример того, как такой файл мог бы выглядеть. Программа, производящая чтение данного файла, должна быть уведомлена о том, что данные разделены запятыми. Если программа хочет выбрать и вывести категорию, в которой находится урок ‘Database Design Tutorial’, то она должна строчка за строчкой производить чтение до тех пор, пока не будут найдены слова ‘Database Design Tutorial’ и затем ей нужно будет прочитать следующее за запятой слово для того, чтобы вывести категорию Software.

Таблицы баз данных.

Чтение файла строчка за строчкой не является очень эффективным. В реляционной базе данных данные хранятся в таблицах. Таблица ниже содержит те же самые данные, что и файл. Каждая строка или “запись” содержит один урок. Каждый столбец содержит какое-то свойство урока. В данном случае это заголовок (title) и его категория (category).

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

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

История реляционной модели.

Реляционная модель баз данных была изобретена в 70-х Эдгаром Коддом (Ted Codd), британским ученым. Он хотел преодолеть недостатки сетевой модели баз данных и иерархической модели. И он очень в этом преуспел. Реляционная модель баз данных сегодня всеобще принята и считается мощной моделью для эффективной организации данных.

Сегодня доступен широкий выбор систем управления базами данных: от небольших десктопных приложений до многофункциональных серверных систем с высокооптимизированными методами поиска. Вот некоторые из наиболее известных систем управления реляционными базами данных (РСУБД):

Oracle – используется преимущественно для профессиональных, больших приложений.
Microsoft SQL server – РСУБД компании Microsoft. Доступна только для операционной системы Windows.
Mysql – очень популярная РСУБД с открытым исходным кодом. Широко используется как профессионалами, так и новичками. Что еще нужно?! Она бесплатна.
IBM – имеет ряд РСУБД, наиболее известна DB2.
Microsoft Access – РСУБД, которая используется в офисе и дома. На самом деле – это больше, чем просто база данных. MS Access позволяет создавать базы данных с пользовательским интерфейсом.
В следующей части я расскажу кое-что о характеристиках реляционных баз данных.

3. Характеристики реляционных баз данных.

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

Использование ключей.

Каждая строка данных в таблице идентифицируется уникальным “ключом”, который называется первичным ключом. Зачастую, первичный ключ это автоматически увеличиваемое (автоинкрементное) число (1,2,3,4 и т.д). Данные в различных таблицах могут быть связаны вместе при использовании ключей. Значения первичного ключа одной таблицы могут быть добавлены в строки (записи) другой таблицы, тем самым, связывая эти записи вместе.

Используя структурированный язык запросов (SQL), данные из разных таблиц, которые связаны ключом, могут быть выбраны за один раз. Для примера вы можете создать запрос, который выберет все заказы из таблицы заказов (orders), которые принадлежат пользователю с идентификатором (id) 3 (Mike) из таблицы пользователей (users). О ключах мы поговорим далее, в следующих частях.


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

Отсутствие избыточности данных.

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

Ограничение ввода.

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


Когда вы создаете таблицу базы данных вы предоставляете тип данных для каждого столбца. К примеру, varchar – это тип данных для небольших фрагментов текста с максимальным количеством знаков, равным 255, а int – это числа.

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

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

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

Поддержание целостности данных.

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

Назначение прав.

Большинство РСУБД предлагают настройку прав доступа, которая позволяет назначать определенные права определенным пользователям. Некоторые действия, которые могут быть позволены или запрещены пользователю: SELECT (выборка), INSERT (вставка), DELETE (удаление), ALTER (изменение), CREATE (создание) и т.д. Это операции, которые могут быть выполнены с помощью структурированного языка запросов (SQL).

Структурированный язык запросов (SQL).

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

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

Переносимость.

Реляционная модель данных стандартна. Следуя правилам реляционной модели данных вы можете быть уверены, что ваши данные могут быть перенесены в другую РСУБД относительно просто.

Как говорилось ранее, проектирование базы данных – это вопрос идентификации данных, их связи и помещение результатов решения данного вопроса на бумагу (или в компьютерную программу). Проектирование базы данных независимо от РСУБД, которую вы собираетесь использовать для ее создания.

В следующей части подробнее рассмотрим первичные ключи.

Основы правил проектирования базы данных / Хабр

Введение

Как это часто бывает, архитектору БД нужно разработать базу данных под конкретное решение.

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

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

Для начала, разберем создание базы данных в MS SQL Server для сервиса поиска соискателей на работу.

Этот материал можно перенести и на другую СУБД такую как MySQL или PostgreSQL.

Основы правил проектирования

Для проектирования схемы базы данных, нужно вспомнить 7 формальных правил и саму концепцию нормализации и денормализации. Они и лежат в основе всех правил проектирования.

Опишем более детально 7 формальных правил:

  1. отношение один к одному:

    1.1) с обязательной связью:

    примером может выступать гражданин и его паспорт: у любого гражданина должен быть паспорт; паспорт один для каждого гражданина

    Реализовать данную связь можно двумя способами:

    1.1.1) в одной сущности (таблице):


    Рис.1. Сущность Citizen

    Здесь таблица Citizen представляет собой сущность гражданина, а атрибут (поле) PassportData содержит все паспортные данные гражданина и не может быть пустым (NOT NULL).

    1.1.2) в двух разных сущностях (таблицах):


    Рис.2. Отношение сущностей Citizen и PassportData

    Здесь таблица Citizen представляет собой сущность гражданина, а таблица PassportData — сущность паспортных данных гражданина (самого паспорта). Сущность гражданина содержит атрибут (поле) PassportID, который ссылается на первичный ключ таблицы PassportData. В свою очередь сущность паспортных данных содержит атрибут (поле) CitizenID, которое ссылается на первичный ключ CitizenID таблицы Citizen. Поле PassportID таблицы Citizen не может быть пустым (NOT NULL). Также здесь важно поддерживать целостность поля CitizenID таблицы PassportData, чтобы обеспечить связь один к одному. Иными словами, поле PassportID таблицы Citizen и поле CitizenID таблицы PassportData должны ссылаться на одни и те же записи как если бы это была одна сущность (таблица), представленная в пункте 1.1.1.

    1.2) с необязательной связью:

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

    Реализовать данную связь можно двумя способами:

    1.2.1) в одной сущности (таблице):


    Рис.3. Сущность Person

    Таблица Person представляет собой сущность человека, а атрибут (поле) PassportData содержит все его паспортные данные и может быть пустым (NULL).

    1.2.2) в двух сущностях (таблицах):


    Рис.4. Отношение сущностей Person и PassportData

    Таблица Person представляет собой сущность человека, а таблица PassportData — сущность паспортных данных человека (самого паспорта). Сущность человека содержит атрибут (поле) PassportID, который ссылается на первичный ключ таблицы PassportData. В свою очередь сущность паспортных данных содержит атрибут (поле) PersonID, которое ссылается на первичный ключ PersonID таблицы Person. Поле PassportID таблицы Person может быть пустым (NULL). Здесь также важно поддерживать целостность поля PersonID таблицы PassportData. Это нужно, чтобы обеспечить связь один к одному. Поле PassportID таблицы Person и поле PersonID таблицы PassportData должны ссылаться на одни и те же записи как если бы это была одна сущность (таблица), показанная в пункте 1.2.1. Или же данные поля должны быть неопределенными, то есть, содержать NULL.

  2. отношение один ко многим:

    2.1) с обязательной связью:

    примером могут выступать родитель и его дети. У каждого родителя есть как минимум один ребенок.

    Реализовать данную связь можно двумя способами:

    2.1.1) в одной сущности (таблице):


    Рис.5. Сущность Parent

    Таблица Parent представляет сущность родителя, а атрибут (поле) ChildList содержит информацию о детях. Данное поле не может быть пустым (NOT NULL). Обычно типом поля ChildList выступают неполно структурированные данные (NoSQL) такие как XML, JSON и т д.

    2.1.2) в двух сущностях (таблицах):


    Рис.6. Отношение сущностей Parent и Child

    Таблица Parent представляет сущность родителя, а таблица Child — сущность ребенка. У таблицы Child есть поле ParentID, ссылающееся на первичный ключ ParentID таблицы Parent. Поле ParentID таблицы Child не может быть пустым (NOT NULL).

    2.2) с необязательной связью:

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

    Реализовать данную связь можно двумя способами:

    2.2.1) в одной сущности (таблице):


    Рис.7. Сущность Person

    Таблица Parent представляет сущность родителя, а атрибут (поле) ChildList содержит информацию о детях. Данное поле может быть пустым (NULL). Обычно типом поля ChildList выступают неполно структурированные данные (NoSQL) такие как XML, JSON и т д.

    2.2.2) в двух сущностях (таблицах):


    Рис.8. Отношение сущностей Person и Child

    Таблица Parent представляет сущность родителя, а таблица Child — сущность ребенка. У таблицы Child есть поле ParentID, ссылающееся на первичный ключ ParentID таблицы Parent. Поле ParentID таблицы Child может быть пустым (NULL).

    2.2.3) в одной сущности со ссылкой на саму себя при условии, что у сущностей (таблиц) родителя и ребенка будут одинаковые наборы атрибутов (полей) без учета ссылки на родителя:


    Рис.9. Сущность Person со связью на саму себя

    Сущность (таблица) Person содержит атрибут (поле) ParentID, который ссылается на первичный ключ PersonID этой же таблицы Person и может содержать пустое значение (NULL).

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

  3. отношение многие к одному:

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

  4. отношение многие ко многим:

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

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


    Рис.10. Отношение сущностей Person и RealEstate

    Таблицы Person и RealEstate представляют соответственно сущности человека и недвижимости. Связываются данные сущности (таблицы) через сущность (таблицы) PersonRealEstate. Атрибуты (поля) PersonID и RealEstateID ссылаются на первичные ключи PersonID таблицы Person и RealEstateID таблицы RealEstate соответственно. Обратите внимание, что для таблицы PersonRealEstate пара (PersonID; RealEstateID) всегда является уникальной и потому может выступать первичный ключем для самой связующей сущности PersonRealEstate.

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

Отношения

один ко многим

и

многие к одному

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

А где же семь формальных правил?

Вот они:

  1. п.1 (п.1.1 и п.1.2) — первое и второе формальные правила
  2. п.2 (п.2.1 и п.2.2) — третье и четвертое формальные правила
  3. п.3 (аналогично п.2) — пятое и шестое формальные правила
  4. п.4 — седьмое формальное правило

В тексте выше эти семь формальных правил объединены в четыре блока по функционалу.

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

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

Вот и вся суть правил проектирования баз данных.

А вы уверены, что поняли отношения в семи формальных правилах? Именно поняли, а не узнали? Ведь знать и понимать — две совершенно разных концепции.

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

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

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

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

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

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

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

Вы проследили, какие отношения были между субъектами, и как менялись эти отношения?
Давайте присмотримся внимательнее.

  • Когда семья была полной, с несколькими детьми, отношение между родителями и детьми имело вид многие ко многим.
  • Когда остались мать и дети, отношение между родителем и детьми стало один ко многим с обязательной связью. Однако, в любой семье, где может и не быть детей, это отношение будет таким же, но уже с необязательной связью.
  • А вот со стороны детей отношение к родителю было как многие к одному с обязательной связью пока родителя не лишили родительских прав.
  • Когда дети оказались в детском доме — отношение изменилось на многие к одному с необязательной связью.
  • Когда у детей появились попечители, связь между ними стала многие ко многим: у каждого попечителя могут быть другие подопечные дети, а у каждого ребенка могут быть другие попечители (родители).

Отношение между мужем и женой

один к одному с обязательной связью

при официальной брака или

один к одному с необязательной связью

до регистрации. Жена может быть только одна, как и муж может быть только один. По крайней мере, в России. Но в другой стране возможно многоженство, и тогда связь между мужем и женами будет

один ко многим

, а между женами и мужем —

многие к одному

.

Надеюсь, теперь вы значительно приблизились к пониманию этих семи формальных правил.

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

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

Также вы увидите, что реализовать отношение можно разными способами, а сами отношения могут меняться. Модель (схема) базы данных — это «снимок» отношений между сущностями в определенный момент времени. Именно поэтому важно определить как сами сущности — образы объектов из реального мира или предметной области, так и их отношения между собой с учетом изменений в будущем.

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

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

Проектирование схемы базы данных для поиска соискателей на работу

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

Для начала, определим, что важно для сотрудников из компании, которые ищут кандидатов:

  1. для HR:

    1.1) компании, где работал соискатель
    1.2) позиции, которые ранее занимал соискатель в данных компаниях
    1.3) навыки и умения, которыми соискатель пользовался в работе;
    а также продолжительность работы соискателя в каждой компании на каждой позиции и длительность использования каждого навыка и умения

  2. для технического специалиста:

    2.1) позиции, которые занимал соискатель в данных компаниях
    2.2) навыки и умения, которыми соискатель пользовался в работе
    2.3) проекты, в которых участвовал соискатель;
    а также продолжительность работы соискателя на каждой позиции и в каждом проекте, длительность использования каждого навыка и умения

Для начала выявим нужные сущности:

  1. Сотрудник (Employee)
  2. Компания (Company)
  3. Позиция (должность) (Position)
  4. Проект (Project)
  5. Навык (Skill)

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

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


Рис.11. Схема базы данных для поиска соискателей на работу

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

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

Когда вы понимаете отношения между субъектами и между субъектами и объектами — уже упомянутые семь формальных правил — эту или схожую схему можно реализовать «на коленке»: на листке бумаги, мене чем за час. И это еще с учетом усталости после плодотворного рабочего дня.

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

Подобная модель лежит в основе базы данных проекта Geecko.

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

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

Немного лирики

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

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

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

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

Послесловие

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

Database Diagram Tool for SQL Server

. Однако, подобный функционал есть и в

DBeaver

.

Источники


Как спроектировать схему базы данных

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

Дополнение: Также вас могут заинтересовать заметки Начало работы с PostgreSQL, Потоковая репликация в PostgreSQL и пример фейловера, Некоторые интересные отличия PostgreSQL от MySQL и Обратная совместимость и изменение схемы базы данных.

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

Рисуем диаграмму

Допустим, требуется спроектировать схему базы данных, в которой хранится информация о музыкальных исполнителях, альбомах и песнях. На начальном этапе, когда у нас еще совсем ничего нет, удобно начать с рисования диаграммы будущей схемы. Можно начать с наброска ручкой на листе бумаги, можно сразу взять специализированный редактор. Их сейчас очень много, все они устроены довольно похожим образом. При подготовке этой заметки я воспользовался DbSchema. Это платная программа, но мне кажется, что она стоит своих денег. К тому же, в нормальных компаниях обычно оплачивают стоимость софта, необходимого для работы. Триал у DbSchema, если что, составляет две недели.

Нарисовать следюущую диаграмму заняло у меня порядко десяти минут:

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

Дополнение: Аналогичную диаграмму можно построить при помощи открытого инструмента PlantUML.

Генерируем SQL и скармливаем его СУБД

Нетрудно заметить, что данная диаграмма легко отображается в код для создания схемы базы данных на языке SQL. В DbSchema сгенерировать SQL можно, сказав Schema → Generate Schema and Data Script. Затем полученный скрипт можно скормить используемой вами СУБД:

cat music.sql | psql -hlocalhost test_database test_user

Я использовал PostgreSQL. Информацию о том, как установить эту СУБД, вы найдете в этой заметке.

Итак, чем же я руководствовался при проектировании схемы?

Нормальные формы

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

Грубо говоря, таблица находится в первой нормальной форме (1НФ), если на пересечении любой строки и любого столбца в таблице находится ровно одно значение. В современных РСУБД это условие всегда выполняется. Даже если СУБД поддерживает множества или массивы, на пересечении строки и столбца хранится ровно одно значение типа множество или массив. Но в таблице (user varchar(100), phone integer) не может быть строки alex - 1234, 5678. В 1НФ может быть только две сроки — alex - 1234 и alex - 5678.

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

Таблица находится в третьей нормальной форме, если она находится в 2НФ и ни один неключевой атрибут не находится в транзитивной функциональной зависимости от первичного ключа. Например, рассмотрим таблицу (employee varchar(100) primary key, department varchar(100), department_phone integer). Очевидно, что она находится в 2НФ. Но телефон отдела находится в транзитивной функциональной зависимости от имени сотрудника, так как сотрудник однозначно задает отдел, а отдел однозначно задает телефон отдела. Для приведения таблицы в 3НФ нужно разбить ее на две таблицы — employee - department и departmnet - phone.

Легко видеть, что нормализация уменьшает избыточность базы данных и препятствует внесению случайных ошибок. Например, если оставить таблицу из последнего примера в 2НФ, то можно по ошибке прописать одному и тому же отделу разные телефоны. Или рассмотрим компанию с пятью отделами и 1000 сотрудниками. Если у отдела поменялся номер телефона, то для его обновления в базе данных в случае 2НФ потребуется просканировать 1000 строк, а в случае с 3НФ только пять.

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

Отношение один ко многим

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

Для моделирования такого типа отношения в каждом альбоме указывается id исполнителя, и в каждом исполнителе указывается id страны. Понятное дело, мы не просто пишем туда какую-то циферку, а возлагаем ответственность по контролю ссылочной целостности на нашу СУБД:

ALTER TABLE albums ADD CONSTRAINT fk_albums_artists FOREIGN KEY ( artist_id ) REFERENCES artists( artist_id );

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

Жанры и страны в приведенной схеме иногда еще называют «словарями». Это сравнительно небольшие таблицы, состоящие из двух столбцов — id и названия. Если, например, мы захотим переименовать страну Russia в Russian Federation, нам придется поменять всего лишь одну строчку в таблице countries, а не править кучу строк в таблице artists, что может привести к очень большому количеству дисковых операций. Кроме того, если требуется отобразить в диалоге создания нового исполнителя выпадающий список с выбором страны, нам не придется делать дорогих группировок по таблице artists, достаточно сделать простую выборку из countries.

Отношение многие ко многим

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

Оно моделируется путем введения дополнительной таблицы. В нашем примере эта таблица называется albums_songs. Первичный ключ в этой таблицы состоит из двух внешних ключей — album_id и song_id. Теперь нетрудно с помощью пары join’ов получить все песни, входящие в данный альбом или все альбомы, в которые входит заданная песня. Кроме того, ничто не мешает завести в связующей таблице дополнительные столбцы. Например, столбец, хранящий номер трека, под которым песня входит в заданный альбом.

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

Отношение родитель-потомок (или общее-частное)

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

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

Есть и другие подходы. В PostgreSQL, например, есть наследование таблиц, предназначенное для решения как раз такой вот проблемы. Если вы работаете с PostgreSQL, нет причин не использовать этот механизм. Кто-то предпочитает ввести одну таблицу для всех типов с дополнительным столбцом type. Если некий столбец не имеет смысла для заданного типа, в него пишется null. Но это, как вы можете подозревать, не очень-то удобно, если у вас 10 типов, каждый из которых имеет по дюжине столбцов, характерных только для этого типа, а также парочку собственных подтипов. Кроме того, можно опрометчиво реализовать смену типа, как простое обновление столбца type, и получить массу интереснейших эффектов.

Что еще нужно принять во внимание

Принцип при моделировании других отношений тот же. Например, один человек имеет двух родителей и при этом один человек может иметь сколько угодно детей. Казалось бы, связь 2:N, этого мы не проходили. На самом деле, это просто две связи 1:N. Вводим столбцы mother_id, father_id и вперед. Да, связь в рамках одной таблицы, ну и что?

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

В общем, все, что нужно, это немного здравого смысла.

Также при проектировании схемы базы данных нужно уделять внимание индексам. Тут все сильно зависит от конкретной СУБД, поддерживает ли она составные индексы, частичные индексы, функциональные индексы, bitmap scans и так далее. Кое-что по этой теме я писал здесь, а вообще — курите мануалы по вашей СУБД. Также за кадром остались вьюхи, триггеры и многое другое.

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

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

Заключение

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

Проект для DbSchema, а также сгенерированный из него SQL, вы можете скачать здесь. Как всегда, если у вас есть вопросы или дополнения, не стесняйтесь писать их в комментариях.

Метки: Разработка, СУБД.

Создание базы данных

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

В этой статье

Обзор

При первом запуске Access, а также при закрытии базы данных без завершения работы Access отображается представление Microsoft Office Backstage.

Представление Backstage является отправным пунктом для создания новых и открытия существующих баз данных, просмотра релевантных статей на сайте Office.com и т. д., то есть для выполнения любых операций с файлом базы данных или вне базы данных, но не в ней.

Создание базы данных

Когда вы открываете Access, в представлении Backstage отображается вкладка «Новое». Создать базу данных можно несколькими способами:

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

  • Шаблон, установленный в Access    Если вы хотите начать новый проект и начать его, рассмотрите возможность использования шаблона. В Access по умолчанию установлено несколько шаблонов.

  • Шаблон из Office.com    В дополнение к шаблонам, поставляемым с Access, много других шаблонов доступно на сайте Office.com. Для их использования даже не нужно открывать браузер, потому что эти шаблоны доступны на вкладке Создать.

Добавление объектов в базу данных

При работе с базой данных в нее можно добавлять поля, таблицы и части приложения.

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

Также можно создавать запросы, формы, отчеты, макросы — любые объекты базы данных, необходимые для работы.

Создание базы данных с помощью шаблона

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

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

  1. Если база данных открыта, нажмите на вкладке Файл кнопку Закрыть. В представлении Backstage откроется вкладка Создать.

  2. На вкладке Создать доступно несколько наборов шаблонов. Некоторые из них встроены в Access, а другие шаблоны можно скачать с сайта Office.com. Дополнительные сведения см. в следующем разделе.

  3. Выберите шаблон, который вы хотите использовать.

  4. Access предложит имя файла для базы данных в поле «Имя файла». При этом имя файла можно изменить. Чтобы сохранить базу данных в другой папке, отличной от папки, которая отображается под полем «Имя файла», нажмите кнопку , перейдите к папке, в которой ее нужно сохранить, и нажмите кнопку «ОК». При желании вы можете создать базу данных и связать ее с сайтом SharePoint.

  5. Нажмите кнопку Создать.

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

    На вкладке Главная в группе Записи нажмите кнопку Удалить.

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

Дополнительные сведения о работе с шаблонами см. в статье Создание базы данных Access на компьютере с помощью шаблона.

К началу страницы

Создание базы данных без использования шаблона

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

  • Ввести, вставить или импортировать данные в таблицу, которая создается вместе с базой данных, и повторить эту процедуру для новых таблиц, которые создаются с помощью команды Таблица на вкладке Создание.

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

Создание пустой базы данных

  1. На вкладке Файл щелкните Создать и выберите вариант Пустая база данных.

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

  3. Нажмите кнопку Создать.

    Access создаст базу данных с пустой таблицей «Таблица1» и откроет ее в режиме таблицы. Курсор будет помещен в первую пустую ячейку столбца Щелкните для добавления.

  4. Чтобы добавить данные, начните вводить их или вставьте из другого источника (см. раздел Копирование данных из другого источника в таблицу Access).

Ввод данных в представлении таблицы аналогиен вводу данных на листах Excel. Структура таблицы создается при вводе данных. При добавлении нового столбца в таблицу в таблице определяется новое поле. Access автоматически задает тип данных каждого поля на основе введите данные.

Если на этом этапе вводить данные в таблицу «Таблица1» не нужно, нажмите кнопку Закрыть . Если вы внесли изменения в таблицу, будет предложено сохранить их. Нажмите кнопку Да, чтобы сохранить изменения, кнопку Нет, чтобы не сохранять их, или кнопку Отмена, чтобы оставить таблицу открытой.

Совет:  Access ищет файл с именем Blank.accdb в папке [диск установки]:\Program Files\Microsoft Office\Templates\1049\Access\. Если он существует, blank.accdb является шаблоном для всех новых пустых баз данных. Все новые базы данных наследуют содержимое этого файла. Это отличный способ распространения содержимого по умолчанию, например номеров компонентов или заявлений об отказе от ответственности и политик компании.

Важно: Если хотя бы один раз закрыть таблицу «Таблица1» без сохранения, она будет удалена полностью, даже если в нее введены данные.

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

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

Создание таблицы в режиме таблицы.    В режиме таблицы можно начать ввод данных сразу, структура таблицы при этом будет создаваться автоматически. Полям присваиваются имена с последовательными номерами («Поле1», «Поле2» и т. д.), а тип данных автоматически задается с учетом вводимых данных.

  1. на вкладке Создание в группе Таблицы нажмите кнопку Таблица.

    Access создаст таблицу и выделит первую пустую ячейку в столбце Щелкните для добавления.

  2. На вкладке Поля в группе Добавление и удаление выберите нужный тип поля. Если нужный тип поля не отображается, нажмите кнопку Другие поля .

  3. Откроется список часто используемых типов полей. Выберите необходимый тип поля, и Access добавит в таблицу новое поле в точке вставки.

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

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

  5. Для переименования столбца (поля) дважды щелкните его заголовок и введите новое название.

    Присвойте полям значимые имена, чтобы при просмотре области Список полей было понятно, что содержится в каждом поле.

  6. Чтобы переместить столбец, щелкните его заголовок для выделения столбца и перетащите столбец в нужное место. Можно выделить несколько смежных столбцов и перетащить их одновременно. Чтобы выделить несколько смежных столбцов, щелкните заголовок первого столбца, а затем, удерживая нажатой клавишу SHIFT, щелкните заголовок последнего столбца.

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

  1. На вкладке Создание в группе Таблицы нажмите кнопку Конструктор таблиц.

  2. Для каждого поля в таблице введите имя в столбце Имя поля, а затем в списке Тип данных выберите тип данных.

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

  4. Когда все необходимые поля будут добавлены, сохраните таблицу:

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

Задание свойств полей в режиме конструктора.    Независимо от способа создания таблицы рекомендуется проверить и задать свойства полей. Хотя некоторые свойства доступны в режиме таблицы, другие можно настроить только в режиме конструктора. Чтобы перейти в режим конструктора, в области навигации щелкните таблицу правой кнопкой мыши и выберите пункт Конструктор. Чтобы отобразить свойства поля, щелкните его в сетке конструктора. Свойства отображаются под сеткой конструктора в области Свойства поля.

Щелкните свойство поля, чтобы просмотреть его описание рядом со списком Свойства поля. Более подробные сведения можно получить, нажав кнопку справки.

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

Свойство

Описание

Размер поля

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

Формат поля

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

Маска ввода

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

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

Это свойство позволяет задать стандартное значение, которое будет отображаться в этом поле при добавлении новой записи. Например, для поля «Дата/время», в котором необходимо записывать дату добавления записи, в качестве значения по умолчанию можно ввести «Date()» (без кавычек).

Обязательное поле

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

К началу страницы

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

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

При вставке данных в пустую таблицу приложение Access задает тип данных для каждого поля в зависимости от того, какие данные в нем находятся. Например, если во вставляемом поле содержатся только значения даты, для этого поля используется тип данных «Дата/время». Если же вставляемое поле содержит только слова «Да» и «Нет», для этого поля выбирается тип данных «Логический».

Access называет имена полей в зависимости от того, что находится в первой строке в полученных данных. Если первая строка с данными похожа на последующие, Access определяет, что первая строка является частью данных, и присваивает полям общие имена (F1, F2 и т. д.). Если первая строка с данными не похожа на последующие строки, Access определяет, что первая строка состоит из имен полей. Access применит имена полей соответствующим образом и не включит первую строку в данные.

В Access имена присваиваются полям автоматически, поэтому во избежание путаницы поля следует переименовать. Это можно сделать следующим образом:

  1. Нажмите клавиши CTRL+S, чтобы сохранить таблицу.

  2. В режиме таблицы дважды щелкните заголовок каждого столбца и введите описательное имя поля для каждого столбца.

  3. Еще раз сохраните таблицу.

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

К началу страницы

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

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

  1. В Access на вкладке Внешние данные в группе Импорт и связи выберите команду для типа файла, который необходимо импортировать.

    Например, чтобы импортировать данные с листа Excel, нажмите кнопку Excel. Если вы не видите нужный тип программы, нажмите кнопку Дополнительно.

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

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

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

  4. Если будет запущен мастер, следуйте инструкциям на экране. На последней странице мастера нажмите кнопку Готово.

    При импорте объектов или связывании таблиц из базы данных Access открывается диалоговое окно Импорт объектов или Связь с таблицами. Выберите нужные элементы и нажмите кнопку ОК.

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

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

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

К началу страницы

Добавление части приложения

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

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

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

  2. Откройте вкладку Создание.

  3. В группе Шаблоны нажмите кнопку Части приложения. Появится список доступных частей.

  4. Щелкните часть приложения, которую вы хотите добавить.

К началу страницы

Открытие существующей базы данных Access

  1. На вкладке Файл нажмите кнопку Открыть.

  2. В диалоговом окне Открытие файла базы данных найдите базу данных, которую нужно открыть.

  3. Выполните одно из следующих действий.

    • Чтобы открыть базу данных в режиме по умолчанию, дважды щелкните ее (режим по умолчанию может быть указан в диалоговом окне Параметры Access или установлен административной политикой).

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

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

    • Щелкните стрелку рядом с кнопкой Открыть и выберите вариант Монопольно, чтобы открыть базу данных в монопольном режиме. Если ее затем попытается открыть другой пользователь, он получит сообщение «Файл уже используется».

    • Щелкните стрелку рядом с кнопкой Открыть и выберите вариант Монопольно для чтения, чтобы открыть базу данных только для чтения. Другие пользователи при этом смогут открывать базу данных только для чтения.

Примечание: Вы можете напрямую открывать файлы данных внешних форматов, например dBASE, Microsoft Exchange или Excel. Кроме того, можно открыть напрямую любой источник данных ODBC, например Microsoft SQL Server. Access автоматически создаст базу данных Access в одной папке с файлом данных и добавит ссылки на все таблицы внешней базы данных.

Советы

  • Чтобы открыть одну из недавно использовавшихся баз данных, щелкните Последние на вкладке Файл и выберите имя файла базы данных. Access откроет базу данных, используя параметры, которые применялись при ее открытии в прошлый раз. Если список последних использовавшихся файлов не отображается, щелкните Параметры на вкладке Файл. В диалоговом окне Параметры Access нажмите кнопку Параметры клиента. В разделе Вывод на экран укажите количество документов, которые необходимо отобразить в списке «Последние документы» (не больше 50).

    Кроме того, на панели навигации представления Backstage можно отдемонстрировать последние базы данных (1) вкладка «Файл», часть 2) базу данных, которую вы хотите открыть. В нижней части вкладки «Последние» выберите поле «Число последних баз данных», а затем укажите нужное количество.

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

К началу страницы

Проектирование базы геоданных. Общий обзор—ArcMap

Проектирование базы геоданных:общий обзор

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

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

Это включает в себя определение:

  • Как географические объекты будут представлены в каждой теме данных (например, в виде точек, линий, полигонов или в виде растров), а также их табличные атрибуты
  • Как данные будут организованы в таких наборах данных, как классы пространственных объектов, наборы атрибутов, наборы растровых данных и так далее
  • Какие дополнительные пространственные элементы и элементы базы данных будут нужны для обеспечения правил целостности данных, для реализации расширенных ГИС-моделей поведения (например, топология, сети и каталоги растров), а также для определения пространственных и атрибутивных отношений между наборами данных

Представление

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

  • Классов пространственных объектов (наборов точек, линий и полигонов)
  • Изображений и растров
  • Непрерывных поверхностей, которые могут быть представлены в виде пространственных объектов (например, изолиний), растровых изображений (цифровых моделей рельефа [ЦМР, (DEM)]) или в виде нерегулярных триангуляционных сетей (TIN) с использованием наборов данных terrain.
  • Для данных текстового описания могут быть использованы атрибутивные таблицы

Темы данных

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

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

В ГИС организация тематических слоев является одним из самых важных принципов проектирования базы данных ГИС.

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

Наборы данных ГИС представляют собой наборы представлений для каждой темы данных

Наборы географических данных могут быть представлены в базе данных ГИС в виде классов пространственных объектов, а также в виде наборов растровых данных.

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

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

На определение пространственных представлений в ГИС имеют влияние и существующие источники данных, и те источники данных, которые планируется использовать. При проектировании базы данных ГИС пользователи подразумевают и варианты их применения. Они четко понимают, на какие вопросы должна будет ответить ГИС. Определение этих вопросов поможет определить содержимое каждой темы, а также то, как они будут представлены географически. Например, существует множество вариантов представления поверхности рельефа: в виде изолиний, в виде подписей отметок высот (например, отметки вершин, высших точек), в виде континуальной поверхности рельефа (TIN) или в виде затененного изображения рельефа. Любой из этих способов изображения рельефа может подходить для конкретного проекта базы данных ГИС. Намеченные варианты использования данных помогут определить, какие из этих представлений будут нужны в будущем.

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

Отдельные наборы данных ГИС нередко собираются вместе с другими слоями данных

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

  • Гидрологическая информация о водоразделах и водосборных бассейнах должна собираться в унисон с информацией о дренажной сети. Линии водосборов должны попадать внутрь бассейнов. Все остальные слои должны совпадать с поверхностью рельефа.
  • Различные слои данных в материалах по земельным участкам должны быть собраны в соответствии с информацией из кадастровых слоев и геодезической информацией, чтобы пространственные объекты земельных участков совпадали с опорной сетью геодезических данных. Множество прочих наборов пространственных объектов (зоны отчуждения, межевые границы, коммуникации, классы зон и т.д.) должны соответствовать набору данных участков.
  • Пространственные отношения между рельефом, ландшафтами, типами почв, уклонами, растительностью, геологией и т.д. обычно тоже собираются в унисон, характеризуя природные ресурсы. Научное понимание этих пространственных отношений помогает построить логически согласованную базу данных, где пространственные объекты одного слоя данных соответствуют объектам остальных слоев.
  • Информация топографической базовой карты компилируется вся вместе. Гидрография, транспортные сети, административные границы и прочие слои топографической карты должны компилироваться в унисон. Эти картографические представления на отображении карты должны быть интегрированы, чтобы с такой картой было удобно работать, и можно было обращать внимание на ключевые позиции карты.

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

  • Ландшафт земной поверхности. Топографические карты, рельеф, дренажная сеть, транспортная сеть, объекты карты, перемещение между странами и т.д.
  • Городской ландшафт. Здания и сооружения, жизненно важная инфраструктура и т.д.
  • Ландшафт изображений. Спутниковая и авиасъемка, местное, региональное и национальное покрытия и т.д.
  • Человеческий ландшафт. Демография (характеристики населения), культурные центры, гражданство, административные районы и зоны и т.д.
  • Ландшафт рабочей силы. Мобильное отслеживание перемещений рабочей силы, сервисные центры, дорожные условия, товарные склады и т.д.
  • Сенсорный ландшафт. Местоположения камер и прочих устройств
  • Ландшафт операций и планов. Контролируемые зоны, планы перемещений, реагирования и т.д.

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

PHP: Проектирование базы данных — Manual

Change language: EnglishBrazilian PortugueseChinese (Simplified)FrenchGermanJapaneseRussianSpanishTurkishOther

Проектирование базы данных

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

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

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

krystian at jablonowski dot eu

8 months ago

It's a good practice to create a user account with absolutely minimal permissions. Whenever You need to select those permissions by columns or tables remember that some rules don't apply to security measures on Your server, like "We are all adults here" or "KISS - Keep It Simple Stupid". Personally, I prefer to create a minimal amount of users with only the necessary authorization to manipulate or collect data from DB.
Remember, that leak of data can have tremendous consequences, and rebuilding the trust of Your users is extremely hard to accomplish.

Основы проектирования баз данных

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

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

Важно: Access предоставляет возможности проектирования, которые позволяют создавать приложения баз данных для Интернета. При проектировании для Интернета многие аспекты дизайна отличаются. В этой статье не обсуждается проектирование веб-приложения базы данных.Дополнительные сведения см. в статье Создание базы данных для публикации в Интернете.

В этой статье

Некоторые термины базы данных, которые нужно знать

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

Каждую строку правильнее называть записью , а каждый столбец полем . Запись — это осмысленный и непротиворечивый способ объединения информации о чем-либо. Поле – это отдельный элемент информации – тип элемента, который появляется в каждой записи.Например, в таблице «Продукты» каждая строка или запись будет содержать информацию об одном продукте. Каждый столбец или поле содержит определенную информацию об этом продукте, например его название или цену.

Верх страницы

Что такое хороший дизайн базы данных?

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

Таким образом, хороший проект базы данных должен:

  • Разделяет вашу информацию на тематические таблицы, чтобы уменьшить количество избыточных данных.

  • Предоставляет Access информацию, необходимую для объединения информации в таблицах по мере необходимости.

  • Помогает поддерживать и обеспечивать точность и целостность вашей информации.

  • Удовлетворяет ваши потребности в обработке данных и отчетности.

Верх страницы

Процесс проектирования

Процесс проектирования состоит из следующих шагов:

  • Определите назначение вашей базы данных     

    Это поможет вам подготовиться к оставшимся шагам.

  • Найдите и систематизируйте необходимую информацию     

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

  • Разделить информацию на таблицы     

    Разделите свои информационные элементы на основные объекты или темы, такие как Продукты или Заказы.Затем каждый предмет становится таблицей.

  • Превратить информационные элементы в столбцы     

    Решите, какую информацию вы хотите хранить в каждой таблице. Каждый элемент становится полем и отображается в виде столбца в таблице. Например, таблица «Сотрудники» может включать такие поля, как «Фамилия» и «Дата приема на работу».

  • Укажите первичные ключи     

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

  • Настроить отношения между таблицами     

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

  • Усовершенствуйте свой дизайн     

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

  • Применить правила нормализации     

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

Верх страницы

Определение цели вашей базы данных

Рекомендуется записать назначение базы данных на бумаге — ее цель, как вы собираетесь ее использовать и кто будет ее использовать.Например, для небольшой базы данных для домашнего бизнеса вы можете написать что-нибудь простое, например: «База данных клиентов хранит список информации о клиентах для создания рассылок и отчетов». Если база данных является более сложной или используется многими людьми, как это часто бывает в корпоративной среде, цель может легко состоять из абзаца или более и должна включать, когда и как каждый человек будет использовать базу данных. Идея состоит в том, чтобы иметь хорошо разработанную формулировку миссии, на которую можно ссылаться на протяжении всего процесса проектирования.Наличие такого заявления помогает вам сосредоточиться на своих целях, когда вы принимаете решения.

Верх страницы

Поиск и систематизация необходимой информации

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

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

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

Размышление об отчетах и ​​рассылках, которые вы, возможно, захотите создать, поможет вам определить элементы, которые вам понадобятся в вашей базе данных.Например, предположим, вы даете клиентам возможность подписаться на периодические обновления по электронной почте (или отказаться от них) и хотите распечатать список тех, кто дал согласие. mail» в таблицу клиентов. Для каждого клиента вы можете установить в поле Да или Нет.

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

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

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

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

После сбора этой информации вы готовы к следующему шагу.

Верх страницы

Разделение информации на таблицы

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

.

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

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

.

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

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

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

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

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

Верх страницы

Преобразование информационных элементов в столбцы

Чтобы определить столбцы в таблице, решите, какую информацию о субъекте, записанном в таблице, вам нужно отслеживать.Например, для таблицы «Клиенты» хороший начальный список столбцов составляют «Имя», «Адрес», «Город-штат-индекс», «Отправить электронное письмо», «Приветствие» и «Адрес электронной почты». Каждая запись в таблице содержит одинаковый набор столбцов, поэтому для каждой записи можно сохранить имя, адрес, город-штат-индекс, отправить электронное письмо, приветствие и адрес электронной почты. Например, столбец адреса содержит адреса клиентов. Каждая запись содержит данные об одном клиенте, а поле адреса содержит адрес этого клиента.

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

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

В следующем списке приведены несколько советов по определению столбцов.

  • Не включать расчетные данные     

    В большинстве случаев не следует хранить результат вычислений в таблицах. Вместо этого вы можете попросить Access выполнить расчеты, когда захотите увидеть результат. Например, предположим, что имеется отчет «Продукты в заказе», в котором отображается промежуточная сумма заказанных единиц для каждой категории продуктов в базе данных. Однако ни в одной таблице нет промежуточного столбца «Единицы в заказе».Вместо этого таблица «Продукты» содержит столбец «Единицы в заказе», в котором хранятся единицы в заказе для каждого продукта. Используя эти данные, Access вычисляет промежуточный итог каждый раз, когда вы печатаете отчет. Сам промежуточный итог не должен храниться в таблице.

  • Хранить информацию в ее наименьших логических частях     

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

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

Верх страницы

Указание первичных ключей

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

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

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

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

Часто в качестве первичного ключа используется произвольный уникальный номер. Например, вы можете присвоить каждому заказу уникальный номер заказа.Единственная цель номера заказа — идентифицировать заказ. Однажды назначенный, он никогда не меняется.

Если вы не имеете в виду столбец или набор столбцов, которые могли бы стать хорошим первичным ключом, рассмотрите возможность использования столбца с типом данных AutoNumber. Когда вы используете тип данных AutoNumber, Access автоматически присваивает вам значение. Такой идентификатор не имеет фактов; он не содержит фактической информации, описывающей строку, которую он представляет. Идентификаторы без фактов идеально подходят для использования в качестве первичного ключа, поскольку они не меняются.Первичный ключ, содержащий факты о строке — например, номер телефона или имя клиента — с большей вероятностью изменится, поскольку может измениться сама фактическая информация.

1. Столбец, для которого задан тип данных AutoNumber, часто является хорошим первичным ключом. Не бывает двух одинаковых идентификаторов продуктов.

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

Для базы данных продаж продуктов вы можете создать столбец AutoNumber для каждой из таблиц, который будет служить первичным ключом: ProductID для таблицы Products, OrderID для таблицы Orders, CustomerID для таблицы Customers и SupplierID для таблицы Suppliers.

Верх страницы

Создание связи между таблицами

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

1.Информация в этой форме берется из таблицы «Клиенты»…

2. …таблица Сотрудники…

3. …стол заказов…

4. …Таблица товаров…

5. …и таблицу сведений о заказе.

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

Верх страницы

Создание связи «один ко многим»

Рассмотрим этот пример: таблицы поставщиков и продуктов в базе данных заказов на продукты. Поставщик может поставить любое количество товаров. Из этого следует, что для любого поставщика, представленного в таблице «Поставщики», может быть много продуктов, представленных в таблице «Продукты». Таким образом, отношение между таблицей Suppliers и таблицей Products является отношением «один ко многим».

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

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

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

Верх страницы

Создание связи «многие ко многим»

Рассмотрим взаимосвязь между таблицей «Продукты» и таблицей «Заказы».

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

Субъекты двух таблиц — заказы и продукты — связаны отношением «многие ко многим».Это представляет проблему. Чтобы понять проблему, представьте, что произойдет, если вы попытаетесь создать связь между двумя таблицами, добавив поле Product ID в таблицу Orders. Чтобы иметь более одного продукта в заказе, вам потребуется более одной записи в таблице «Заказы» для каждого заказа. Вы будете повторять информацию о заказе для каждой строки, относящейся к одному заказу, что приведет к неэффективному дизайну, который может привести к неточным данным. Вы столкнетесь с той же проблемой, если поместите поле «Идентификатор заказа» в таблицу «Продукты» — у вас будет более одной записи в таблице «Продукты» для каждого продукта.Как решить эту проблему?

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

Каждая запись в таблице сведений о заказе представляет одну позицию в заказе.Первичный ключ таблицы «Сведения о заказах» состоит из двух полей — внешних ключей из таблиц «Заказы» и «Продукты». Использование только поля «Идентификатор заказа» не работает в качестве первичного ключа для этой таблицы, поскольку в одном заказе может быть много позиций. Идентификатор заказа повторяется для каждой позиции в заказе, поэтому поле не содержит уникальных значений. Использование только поля Product ID также не работает, потому что один продукт может фигурировать во многих разных заказах. Но вместе эти два поля всегда создают уникальное значение для каждой записи.

В базе данных по продажам товаров таблицы «Заказы» и «Товары» не связаны друг с другом напрямую. Вместо этого они связаны косвенно через таблицу сведений о заказе. Связь «многие ко многим» между заказами и продуктами представлена ​​в базе данных двумя отношениями «один ко многим»:

.
  • Таблица «Заказы» и таблица «Сведения о заказе» имеют отношение «один ко многим». В каждом заказе может быть несколько позиций, но каждая позиция связана только с одним заказом.

  • Таблица «Продукты» и таблица «Сведения о заказе» имеют отношение «один ко многим». С каждым продуктом может быть связано много позиций, но каждая позиция относится только к одному продукту.

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

После включения таблицы сведений о заказе список таблиц и полей может выглядеть примерно так:

Верх страницы

Создание отношения «один к одному»

Другим типом отношений являются отношения один-к-одному.Например, предположим, что вам нужно записать какую-то специальную дополнительную информацию о продукте, которая вам понадобится редко или применима только к нескольким продуктам. Поскольку вам нечасто нужна эта информация и поскольку хранение информации в таблице «Продукты» приведет к образованию пустого места для каждого продукта, к которому она не относится, вы помещаете ее в отдельную таблицу. Как и в таблице Products, вы используете ProductID в качестве первичного ключа. Отношение между этой дополнительной таблицей и таблицей Product является отношением «один к одному».Для каждой записи в таблице Product существует одна соответствующая запись в дополнительной таблице. Когда вы определяете такую ​​связь, обе таблицы должны иметь общее поле.

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

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

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

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

Верх страницы

Доработка дизайна

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

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

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

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

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

  • Вы постоянно вводите повторяющуюся информацию в одну из своих таблиц? Если это так, вам, вероятно, нужно разделить таблицу на две таблицы, которые имеют отношения «один ко многим».

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

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

  • Содержит ли каждый столбец факт о предмете таблицы? Если столбец не содержит информации о предмете таблицы, он принадлежит другой таблице.

  • Все ли отношения между таблицами представлены либо общими полями, либо третьей таблицей? Для отношений «один к одному» и «один ко многим» требуются общие столбцы. Для отношений «многие ко многим» требуется третья таблица.

Уточнение таблицы продуктов

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

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

Лучшее решение — сделать категории новой темой для отслеживания базы данных с собственной таблицей и собственным первичным ключом. Затем вы можете добавить первичный ключ из таблицы «Категории» в таблицу «Продукты» в качестве внешнего ключа.

Таблицы «Категории» и «Товары» имеют отношение «один ко многим»: категория может включать более одного продукта, но продукт может принадлежать только одной категории.

При просмотре структур таблиц обращайте внимание на повторяющиеся группы. Например, рассмотрим таблицу, содержащую следующие столбцы:

.
  • Код продукта

  • Имя

  • Код продукта 1

  • Имя1

  • Код продукта2

  • Имя2

  • Код продукта3

  • Имя3

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

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

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

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

Верх страницы

Применение правил нормализации

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

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

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

Первая нормальная форма

Первая нормальная форма утверждает, что на каждом пересечении строки и столбца в таблице существует одно значение, а не список значений.Например, у вас не может быть поля с именем «Цена», в котором вы указываете более одной цены. Если вы думаете о каждом пересечении строк и столбцов как о ячейке, каждая ячейка может содержать только одно значение.

Вторая нормальная форма

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

.
  • Идентификатор заказа (первичный ключ)

  • Идентификатор продукта (первичный ключ)

  • Название продукта

Этот дизайн нарушает вторую нормальную форму, поскольку название продукта зависит от идентификатора продукта, но не от идентификатора заказа, поэтому оно не зависит от всего первичного ключа.Вы должны удалить Product Name из таблицы. Он принадлежит другой таблице (Продукты).

Третья нормальная форма

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

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

.
  • ProductID (первичный ключ)

  • Имя

  • Рекомендованная розничная цена

  • Скидка

Предположим, что Скидка зависит от рекомендуемой розничной цены (SRP).Эта таблица нарушает третью нормальную форму, поскольку неключевой столбец Discount зависит от другого неключевого столбца SRP. Независимость от столбцов означает, что вы должны иметь возможность изменять любой неключевой столбец, не затрагивая другие столбцы. Если вы измените значение в поле SRP, соответственно изменится и скидка, что нарушит это правило. В этом случае скидка должна быть перемещена в другую таблицу с ключом на SRP.

Верх страницы

Учебное пособие по структуре и дизайну базы данных

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

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

Отношения один к одному

говорят, что они имеют отношение один к одному (часто пишут 1:1).Вы можете указать этот вид связи на диаграмме ER линией с дефисом на каждом конце:

Если у вас нет веских причин не делать этого, связь 1:1 обычно означает, что вам лучше объединить две таблицы. ‘ данные в одну таблицу.

Однако при определенных обстоятельствах может потребоваться создать таблицы с соотношением 1:1. Если у вас есть поле с необязательными данными, такими как «описание», которое пусто для многих записей, вы можете переместить все описания в отдельную таблицу, устранив пустое пространство и улучшив производительность базы данных.

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

Отношения «один ко многим»

Эти отношения возникают, когда запись в одной таблице связана с несколькими записями в другой. Например, один клиент мог разместить много заказов, или посетитель может взять несколько книг из библиотеки одновременно. Отношения «один ко многим» (1:M) обозначаются так называемой «нотацией гусиной лапки», как в этом примере:

Чтобы реализовать отношение 1:M при настройке базы данных, просто добавьте первичный ключ из «одна» сторона отношения как атрибут в другой таблице.Когда первичный ключ указан в другой таблице таким образом, он называется внешним ключом. Таблица на стороне «1» отношения считается родительской по отношению к дочерней таблице на другой стороне.

Отношения «многие ко многим»

Когда несколько объектов из таблицы могут быть связаны с несколькими объектами в другой таблице, говорят, что они имеют отношение «многие ко многим» (M:N). Это может произойти в случае студентов и классов, поскольку учащийся может посещать несколько классов, а в классе может быть много студентов.

На диаграмме ER эти отношения изображаются следующими линиями:

К сожалению, напрямую реализовать такого рода отношения в базе данных невозможно. Вместо этого вы должны разбить его на два отношения «один ко многим».

Для этого создайте новый объект между этими двумя таблицами. Если между продажами и продуктами существует связь M:N, вы можете назвать эту новую сущность «sold_products», поскольку она будет отображать содержание каждой продажи. И таблицы продаж, и таблицы продуктов будут иметь связь 1:M с проданными_продуктами.Такой промежуточный объект в различных моделях называется таблицей ссылок, ассоциативным объектом или соединительной таблицей.

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

Обязательно или нет?

Еще один способ анализа отношений — рассмотреть, какая сторона отношений должна существовать, чтобы существовала другая.Необязательная сторона может быть отмечена кружком на линии, где был бы прочерк. Например, страна должна существовать, чтобы иметь представителя в ООН, но обратное неверно:

Два субъекта могут быть взаимозависимы (одно не может существовать без другого).

Рекурсивные отношения

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

Избыточные отношения

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

Как спроектировать базу данных SQL

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

Чтобы спроектировать базу данных на SQL, выполните следующие основные шаги:

  1. Решите, какие объекты вы хотите включить в базу данных.

  2. Определите, какие из этих объектов должны быть таблицами, а какие — столбцами в этих таблицах.

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

    При желании вы можете назначить столбец таблицы или комбинацию столбцов в качестве ключа.

Шаг 1: определение объектов

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

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

Шаг 2. Определение таблиц и столбцов

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

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

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

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

  • Тесты, которые вы выполняете

  • Сотрудники

  • Заказов

  • Результаты

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

Шаг 3: Определите таблицы

Теперь вы хотите определить таблицу для каждой сущности и столбец для каждого атрибута.
Стол Столбцы
КЛИЕНТ Имя клиента
Адрес 1
Адрес 2
Город
Состояние
Почтовый индекс
Телефон
Факс
Контактное лицо
ИСПЫТАНИЯ Имя теста
Стандартная заправка
СЛУЖАЩИЙ Имя сотрудника
Адрес 1
Адрес 2
Город
Состояние
Почтовый индекс
Домашний телефон
Внутренний номер офиса
Дата найма
Классификация должностей
Почасовая оплата/зарплата/комиссия
ЗАКАЗЫ Номер для заказа
Имя клиента
Тестовый заказ
Ответственный продавец
Дата заказа
РЕЗУЛЬТАТЫ Номер результата
Номер для заказа
Результат
Дата сообщения
Предварительный/Окончательный
Вы можете создавать определенные здесь таблицы с помощью инструмента быстрой разработки приложений (RAD) или с помощью языка определения данных SQL (DDL), как показано в следующем коде:
 СОЗДАТЬ ТАБЛИЧНЫЙ КЛИЕНТ (
  Имя клиента CHAR (30) НЕ NULL,
  Адрес1 СИМВ (30),
  Адрес2 СИМВОЛА (30),
  Город ЧАР (25),
  Государственный ЧАР (2),
  Почтовый индекс CHAR (10),
  Телефон ЧАР (13),
  Факс ЧАР (13),
  Контактное лицо CHAR (30) ) ;
СОЗДАТЬ ТАБЛИЧНЫЕ ТЕСТЫ (
  Имя_теста CHAR (30) НЕ НУЛЕВОЕ,
  Стандартный заряд CHAR (30) );
СОЗДАТЬ ТАБЛИЦУ СОТРУДНИКОВ (
  Имя_сотрудника CHAR (30) НЕ NULL,
  Адрес1 СИМВ (30),
  Адрес2 СИМВОЛА (30),
  Город ЧАР (25),
  Государственный ЧАР (2),
  Почтовый индекс CHAR (10),
  Домашний телефон CHAR (13),
  OfficeExtension CHAR (4),
  Дата найма ДАТА,
  Классификация должностей CHAR (10),
  HourSalComm CHAR (1) );
СОЗДАТЬ ТАБЛИЦЫ ЗАКАЗОВ (
  OrderNumber INTEGER NOT NULL,
  Имя клиента CHAR (30),
  TestOrdered CHAR (30),
  Продавец ЧАР (30),
  Дата заказа ДАТА );
СОЗДАТЬ РЕЗУЛЬТАТЫ ТАБЛИЦЫ (
  ResultNumber INTEGER NOT NULL,
  Номер заказа INTEGER,
  Результат СИМВОЛ(50),
  ДатаОтчетная ДАТА,
  Предварительный Финал CHAR (1) ) ; 
Эти таблицы связаны друг с другом атрибутами (столбцами), которые они разделяют, как описано в следующем списке:
  • Таблица CLIENT связана с таблицей ORDERS столбцом ClientName.

  • Таблица TESTS связана с таблицей ORDERS столбцом TestName (TestOrdered).

  • Таблица EMPLOYEE связана с таблицей ORDERS столбцом ИмяСотрудника (Продавец).

  • Таблица RESULTS связана с таблицей ORDERS столбцом OrderNumber.

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

Ссылки иллюстрируют четыре различных отношения один ко многим . Ромб в середине каждого отношения показывает максимальную мощность каждого конца отношения. Число 1 обозначает «одну» сторону отношения, а N обозначает «многие» стороны.

  • Один клиент может делать много заказов, но каждый заказ делается одним и только одним клиентом.

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

  • Каждый заказ принимается одним и только одним сотрудником (или продавцом), но каждый продавец может принимать несколько заказов.

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

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

Как спроектировать реляционную базу данных

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

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

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

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

  • Вы можете избежать избыточных, дублирующихся и недействительных данных. Проблемные данные могут подорвать достоверность вашей базы данных, но вы можете спроектировать свою реляционную базу данных так, чтобы свести к минимуму риски, связанные с некачественными данными.
  • Вы можете избежать ситуаций, когда отсутствуют необходимые данные. Если вы можете заранее определить, какие типы данных наиболее важны для вашего рабочего процесса, вы можете структурировать свою базу данных таким образом, чтобы она обеспечивала правильный ввод данных или предупреждала пользователей, когда в записях отсутствуют важные данные.
  • Структуру базы данных легко модифицировать и поддерживать. Рабочие процессы редко остаются неизменными навсегда, поэтому в будущем вам, вероятно, придется внести некоторые коррективы в структуру вашей базы данных.К счастью, хорошо спроектированная реляционная база данных гарантирует, что любые изменения, которые вы вносите в поля одной таблицы, не повлияют отрицательно на другие таблицы.
  • Сами данные легко изменить. Точно так же хорошо спроектированная реляционная база данных гарантирует, что изменения, внесенные в значения в данном поле в одной таблице, не повлияют отрицательно на другие поля в этой таблице.
  • Легче найти нужную информацию. Благодаря согласованной логической структуре базы данных (избегающей дублирования полей и таблиц) гораздо проще выполнять запросы к базе данных.
  • Вы можете тратить меньше времени на исправление базы данных и больше времени на другие виды работы. Лучшая база данных та, о которой вам не нужно беспокоиться.

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

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

Что такое «хорошо спроектированная» база данных?

Итак, ясно, что хороший дизайн базы данных важен при попытке построить базу данных, которая работает на вас. Но что на самом деле означает для хорошо спроектированной базы данных?

Хорошо спроектированная база данных обеспечивает целостность данных

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

Целостность данных включает три конкретных технических аспекта структуры реляционной базы данных:

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

Хорошо спроектированная база данных обеспечивает соблюдение соответствующих бизнес-правил

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

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

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

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

Шаг 1: определите цель и задачи

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

Вы даже можете пожелать работать вместе с менеджерами, руководством и конечными пользователями, чтобы совместно написать заявление о миссии для вашей базы данных, например: «Цель базы данных Международного музея Мингей — поддерживать данные для нашей коллекции произведений искусства». или «База данных Zenbooth будет хранить все данные для нашего планирования производственных ресурсов.”

Кроме того, вы должны определить цели, которые будут иметь конечные пользователи базы данных: какие конкретные задачи должны будут выполнять конечные пользователи, чтобы выполнить свою работу? Разработка четкого списка целей, таких как «Всегда знать статус и местонахождение каждого произведения искусства в нашей коллекции», «Составлять ежемесячные отчеты о продажах клиентов» или «Ведение полных записей для каждого из наших клиентов» — поможет вам определить подходящую структуру для вашей базы данных, когда вы будете работать над этим процессом проектирования.

Шаг 2: анализ требований к данным

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

  • Как ваша организация в настоящее время собирает данные? Вы используете электронные таблицы? Бумажные шаблоны? Другая база данных? Какой бы из этих методов вы ни использовали, найдите наиболее полные образцы работ, какие сможете, и просмотрите их, чтобы найти как можно больше различных атрибутов.Например, ваш редакционный календарь в настоящее время может находиться в виде электронной таблицы и содержать столбцы «Автор», «Срок выполнения», «Редактор» и т. д.
  • Как ваша организация в настоящее время представляет данные ? Какие отчеты использует ваша организация? PDF-файлы? Скользящие колоды? Интернет страницы? Внимательно изучите любые типы презентаций, которые включают данные из ваших текущих методов сбора данных, и используйте их для выявления потенциальных полей.
  • Как члены вашей команды в настоящее время используют данные ? Лучший способ получить ответы на этот вопрос — поговорить с членами команды — как с руководством, так и с конечными пользователями — чтобы определить их текущие модели использования данных, а также любые пробелы в текущей системе. Вы можете задать такие вопросы, как «Какие типы данных вы используете в настоящее время?» и попросите их просмотреть образцы, которые вы собрали. Важно отметить, что эти интервью также могут пролить свет на планы будущего роста организации, что даст вам некоторое представление о будущих информационных требованиях.

Шаг 3: создайте список сущностей и список атрибутов

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

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

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

  • Имя исполнителя
  • Номер телефона исполнителя
  • Имя агента
  • Номер телефона агента
  • Адрес электронной почты агента
  • Название места
  • 9 Адрес места проведения
  • Даты концертов
  • и т. д.

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

Советы:

  • Если несколько атрибутов имеют разные имена, но фактически представляют одну и ту же концепцию , выполните их дедупликацию, чтобы остался только один. Например, если у вас есть оба «Product No.» и «Номер продукта» в вашем списке, вы должны удалить один из них.
  • Если несколько атрибутов имеют одинаковые имена, но на самом деле представляют различных концепций , переименуйте атрибуты, чтобы они были более конкретными. Например, вы можете переименовать два разных атрибута «Имя» в более конкретные «Имя исполнителя» и «Название места».”

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

Шаг 4: смоделируйте таблицы и поля

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

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

.

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

Поле первичного ключа каждой таблицы должно соответствовать следующим критериям:

  • Оно должно содержать уникальные значения. Это предотвратит создание повторяющихся записей в таблице.
  • Не может содержать пустых значений. Пустое значение — это отсутствие значения, поэтому нулевое значение нельзя использовать для идентификации записи.
  • Это значение не должно часто изменяться. В идеале значения первичного ключа должны оставаться относительно постоянными с течением времени и изменяться только в редких случаях.
  • Это не должно быть конфиденциальной личной информацией. Номера социального страхования, пароли, личная медицинская информация (PHI) и другие конфиденциальные данные могут быть уникальными, но их не следует использовать в качестве первичных ключей, так как это значительно усложняет хранение этих данных в одном месте.
  • В идеале имя таблицы используется как часть собственного имени. Хотя это и не является строго обязательным, наличие имени таблицы в имени поля первичного ключа может упростить идентификацию таблицы, из которой произошло поле первичного ключа.Например, «Имя сотрудника» будет очевидно идентифицировано как взятое из таблицы «Сотрудники».

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

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

Вернемся к нашему примеру с базой данных по управлению талантами. Для таблицы «Исполнители» поле «Имя исполнителя» уже является довольно хорошим кандидатом на первичный ключ, поскольку маловероятно, что ваша звукозаписывающая компания подпишет двух исполнителей с одинаковым именем.Мы также можем выбрать «Название места» в качестве первичного ключа для таблицы «Места». Однако для других таблиц, вероятно, было бы лучше создать новые поля, объединяющие значения из существующих полей. В таблице «Агенты» мы можем создать новое поле — «Полное имя агента», которое объединяет значения полей «Имя агента» и «Фамилия агента». Для таблицы «Концерты» артист может выступать в одном и том же месте несколько раз, поэтому мы должны создать новое поле, которое дает уникальное имя для конкретной комбинации артиста в месте проведения в определенную дату.Потенциально вы можете объединить имя исполнителя, место проведения и дату, чтобы создать такие значения, как «2 Linkz в клубе Gotham City Metro Club, 13 февраля 2019 года», но это может стать долго и громоздко быстро. В качестве альтернативы вы можете попробовать создать новое поле — «Gig code» — с уникальными буквенно-цифровыми кодовыми значениями (например, «E0023»). Какой бы подход вы ни выбрали, зависит от вас и конкретных потребностей вашей команды.

Шаг 5: установите связи между таблицами

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

Отношения между таблицами создаются путем связывания полей первичного ключа и полей внешнего ключа. Внешний ключ — это поле в одной таблице, которое ссылается на первичный ключ другой таблицы. Например, если вы разрабатывали базу данных воронки продаж, ваша таблица «Сделки» может содержать поле внешнего ключа «Представитель по продажам», которое будет ссылаться на вашу таблицу «Представители по продажам»; если вы проектировали базу данных календаря контента, ваша таблица «Pieces» может содержать поле внешнего ключа «Редактор» и поле внешнего ключа «Автор», оба из которых будут связаны с вашей таблицей «Персонал».

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

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

Существует три типа отношений между таблицами:

  • Отношения один к одному , при которых запись в одной таблице связана с одной и только с одной записью в другой таблице.Примером этого может быть база данных отслеживания активов ИТ-команды с таблицей «Сотрудники» и таблицей «Компьютеры»: каждый сотрудник владеет только одним компьютером, принадлежащим компании, и каждый компьютер, принадлежащий компании, принадлежит только одному сотруднику. (Обратите внимание, что отношения «один к одному» относительно редки.)
  • Отношения «один ко многим» , в которых запись в одной таблице может быть связана с одной или несколькими записями в другой таблице. Примером этого может быть база данных трекера проектов, которая содержит таблицу «Проекты» и таблицу «Задачи»: каждый проект имеет несколько связанных задач, но каждая задача связана только с одним проектом.
  • Отношения «многие ко многим» , при которых одна или несколько записей в одной таблице могут быть связаны с одной или несколькими записями в другой таблице. Примером этого может быть список книг, который содержит таблицу «Названия» и таблицу «Авторы»: каждое название будет написано одним или несколькими авторами, и каждый автор может написать одну или несколько книг.

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

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

Это пример диаграммы ER для нашей базы данных кадрового агентства. Первичные ключи отмечены «PK», а внешние ключи — «FK». Различные формы на концах линий обозначают типы отношений между объектами: форма «гусиной лапки» означает «много», а тире — «один». Так, например, строку между таблицами «Исполнители» и «Агенты» можно интерпретировать так: каждый артист связан с одним агентом, но каждый агент может быть связан со многими артистами.

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

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

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

Вы даже можете сделать еще один шаг вперед: если вы хотите увидеть, какой доход каждый агент получил в целом от всех артистов, которыми он управляет, вы можете использовать установленную связь между «Исполнителями» и таблицей «Агенты». и создайте новое вычисляемое поле в таблице «Агенты» — «Общий доход от концертов исполнителей», — которое суммирует общее значение «Общего дохода от концертов» для всех связанных записей в таблице «Исполнители» для каждого агента.Поскольку это новое вычисляемое поле в таблице «Агенты» связано с таблицей «Концерты» через таблицу «Исполнители», оно будет автоматически обновляться при изменении значений в таблице «Концерты», даже если нет прямая связь между таблицами «Гиги» и «Агенты».

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

Этот образец базы данных моделирует отношения, описанные на приведенной выше диаграмме ER.Нажмите, чтобы узнать, как таблицы соотносятся друг с другом, или сделайте копию для себя, нажав кнопку «Копировать базу».

Шаг 6: установите бизнес-правила

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

Вообще говоря, есть два основных вида бизнес-правил. Бизнес-правила для конкретных полей относятся к ограничениям, наложенным на определенные поля. Некоторыми примерами бизнес-правил для конкретных областей могут быть следующие: «Даты в наших счетах-фактурах заказа на продукцию должны отображаться в формате ISO ‘2020-12-22’», «Адреса электронной почты, хранящиеся в каталоге сотрудников, должны быть действительными адресами электронной почты» или « Единственными допустимыми значениями, которые могут быть выбраны для этого поля состояния, являются «Сделать», «Выполняется» и «Готово».’»

Бизнес-правила для конкретных отношений, которые мы вкратце коснулись ранее, относятся к ограничениям, наложенным на отношения между таблицами. Некоторыми примерами бизнес-правил, связанных с отношениями, могут быть: «Каждый проект в нашем трекере видеопроизводства должен быть связан с одним или несколькими инструментами проверки фактов» или «Каждая позиция в заказе клиента должна быть связана с одним и только одним продуктом».

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

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

Шаг 7: проверьте свою работу

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

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

Если все выглядит хорошо для вас, вы должны запустить конечный продукт без конечных пользователей и менеджеров, которые будут взаимодействовать с базой данных.Если они довольны конечным результатом, то радуйтесь! Гордитесь своей новой структурно надежной базой данных.

Зарегистрируйтесь на Airtable бесплатно

Учебное пособие по проектированию баз данных в СУБД: изучение моделирования данных

Что такое проектирование базы данных?

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

Основными задачами проектирования базы данных в СУБД являются создание логических и физических моделей предлагаемой системы баз данных.

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

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

В этом руководстве по проектированию базы данных вы узнаете:


Почему важно проектирование базы данных?

Помогает создавать системы баз данных

  1. Отвечающие требованиям пользователей
  2. Иметь высокую производительность.

Процесс проектирования базы данных в СУБД имеет решающее значение для высокопроизводительной системы баз данных .

Заметьте, гениальность базы данных в ее дизайне. Операции с данными с использованием SQL относительно просты

Жизненный цикл разработки базы данных

Жизненный цикл разработки базы данных состоит из нескольких этапов, которым следуют при разработке систем баз данных.

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

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

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

Анализ требований

  • Планирование . Эти этапы концепции проектирования базы данных связаны с планированием всего жизненного цикла разработки базы данных.Он принимает во внимание стратегию информационных систем организации.
  • Определение системы — На этом этапе определяются объем и границы предлагаемой системы баз данных.

Проектирование базы данных

  • Логическая модель . Этот этап связан с разработкой модели базы данных на основе требований. Весь проект находится на бумаге без каких-либо физических реализаций или конкретных соображений СУБД.
  • Физическая модель — На этом этапе реализуется логическая модель базы данных с учетом факторов СУБД и физической реализации.

Реализация

  • Преобразование и загрузка данных — этот этап проектирования реляционных баз данных связан с импортом и преобразованием данных из старой системы в новую базу данных.
  • Тестирование – этот этап связан с выявлением ошибок во вновь внедряемой системе. Он проверяет базу данных на соответствие спецификациям требований.

Два типа методов баз данных

  1. Нормализация
  2. ER Моделирование

Давайте изучим их один за другим

Основы проектирования базы данных SQL с примерами

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

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

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

В этой части я расскажу об основах проектирования реляционных баз данных и проиллюстрирую проектирование базы данных MS SQL Server для службы подбора персонала.

Теперь, что касается следующих статей, я покажу вам, как заполнять базу данных данными с помощью генератора данных для SQL Server и искать данные и объекты базы данных с помощью бесплатного инструмента поиска dbForge. Я буду использовать dbForge Studio для SQL Server для реализации диаграмм для моих примеров и dbForge Data Pump для импорта и экспорта данных.

Основы проектирования БД

Для проектирования схем баз данных вспомним 7 нормальных форм и сами понятия нормализации и денормализации. Они лежат в основе всех правил проектирования.

Приведу подробное описание 7 нормальных форм:

1. Связь один к одному:

1.1 Обязательная связь:

Примером может быть гражданин с паспортом (каждый гражданин должен иметь паспорт, и паспорт один на каждого гражданина)

Это отношение реализуется двумя способами:

1.1.1 В одном объекте (таблица):

Рис.1. Объект «Гражданин»

Здесь таблица Citizen представляет сущность гражданина, а атрибут (поле) PassportData содержит все паспортные данные гражданина и не может быть пустым (НЕ NULL)

1.1.2. В двух разных сущностях (таблицах):

Рис.2. Связь между объектами Citizen и PassportData

Таблица Citizen представляет сущность гражданина, а таблица PassportData представляет сущность паспортных данных гражданина.Сущность гражданина содержит атрибут (поле) PassportID , который ссылается на первичный ключ таблицы PassportData . Принимая во внимание, что объект паспортных данных имеет атрибут (поле) CitizenID , который ссылается на первичный ключ CitizenID таблицы Citizen .

Также важно гарантировать целостность как поля CitizenID , так и таблицы PassportData , чтобы обеспечить связь один к одному.То есть поле PassportID в таблице Citizen и поле CitizenID в таблице PassportData должны ссылаться на одну и ту же запись, как если бы это был один объект (таблица), как показано в параграфе 1.1.1.

1.2 Необязательное отношение:

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

Эта связь реализуется двумя способами:

1.2.1 В одном объекте (таблица):

Рис.3. Сущность Person

Здесь таблица Person представляет сущность человека, а атрибут (поле) PassportData содержит все паспортные данные человека и может быть пустым (NULL)

1.2.2 В двух сущностях (таблицах):

Рис.4. Связь между Person и PassportData

Здесь таблица Person представляет сущность человека, а таблица PassportData представляет сущность паспортных данных человека (то есть сам паспорт).Сущность человека содержит атрибут (поле) PassportID , который ссылается на первичный ключ таблицы PassportData . Принимая во внимание, что объект паспортных данных имеет атрибут (поле) PersonID в таблице Person . Поле PassportID таблицы Person может быть пустым (NULL).

Также важно гарантировать целостность как поля PersonID , так и таблицы PassportData , чтобы обеспечить связь один к одному.То есть поле PassportID таблицы Person и поле PersonID таблицы PassportData должны ссылаться на одни и те же записи, как если бы это был один объект (таблица), показанный в пункте 1.2.1, или эти поля должны быть неопределенными, то есть содержать NULL.

2. Связь «один ко многим»

2.1 Обязательная связь:

Иллюстрацией этого могут быть родители и их дети. У каждого родителя есть хотя бы один ребенок.

Вы можете реализовать эту связь двумя способами:

2.1.1 В одном объекте (таблица):

Рис.5. Родительский объект

Здесь таблица Parent представляет родительскую сущность, а атрибут (поле) ChildList содержит информацию о дочерних элементах, то есть самих дочерних элементах. Это поле не может быть пустым (НЕ NULL). Тип поля ChildList обычно представляет собой частично структурированные данные (NoSQL), такие как XML, JSON и т. д.

2.1.2 В двух сущностях (таблицах):

Рис.6. Отношения между родительскими и дочерними сущностями

Здесь таблица Parent представляет родительский объект, а таблица Child представляет дочерний объект. Таблица Child имеет поле ParentID , которое ссылается на первичный ключ ParentID таблицы Parent . Поле ParentID таблицы Child не может быть пустым (НЕ NULL).

2.2) опционально отношения:

Примером может быть человек, который может иметь детей или может не иметь детей.

Это отношение реализуется двумя способами:

2.2.1 В одном объекте (таблица):

Рис.7. Сущность Person

Здесь таблица Parent представляет родительскую сущность, а атрибут (поле) ChildList содержит информацию о дочерних элементах, то есть самих дочерних элементах.Это поле может быть пустым (NULL). Обычный тип поля ChildList — это полуструктурированные данные (NoSQL), такие как XML, JSON и другие.

2.2.2 В двух сущностях (таблицах):

Рис.8. Связь между сущностями Person и Child

Здесь таблица Parent представляет родительский объект, а таблица Child представляет дочерний объект. Таблица Child имеет поле ParentID , которое ссылается на первичный ключ ParentID таблицы Parent .Поле ParentID таблицы Child может быть пустым (NULL).

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

Рис.9. Сущность Person со ссылкой на себя

Здесь сущность (таблица) Person содержит атрибут (поле) ParentID , который ссылается на первичный ключ PersonID той же таблицы Person и может иметь пустое значение (NULL).

Это реализация отношения «многие к одному» необязательного характера.

3. Отношение «многие к одному»

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

Отношения «один-ко-многим» и «многие-к-одному» также могут быть реализованы через более 2 сущностей путем добавления необходимых атрибутов, ссылающихся на первичные ключи соответствующих сущностей. Эта реализация аналогична примерам выше в пунктах 1.1.2 и 1.2.2.

4. Связь «многие ко многим»

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

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

Рис.10. Взаимоотношения между Лицом и субъектами Недвижимости

Здесь таблицы Person и RealEstate представляют сущности лица и недвижимости соответственно. Эти сущности (таблицы) связаны посредством сущности (таблицы) PersonRealEstate через атрибуты (поля) PersonID и RealEstateID , которые ссылаются на первичные ключи PersonID таблицы Person и RealEstateID . таблицы RealEstate соответственно.Обратите внимание, что пара ( PersonID ; RealEstateID ) всегда уникальна для таблицы PersonRealEstate , поэтому она может быть первичным ключом для самого связующего объекта (таблицы) PersonRealEstate .

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

Так где же 7 нормальных форм, спросите вы?

Ну вот они:

  1. Пар.1 (пар.1.1 и пар.1.2) является первое и второе формальные правила.
  2. Пар.2 (пар.2.1 и пар.2.2) третье и четвертое формальные правила.
  3. Пар.3 (аналогично пар.2) пятое и шестое формальные правила.
  4. Параграф 4 — седьмое формальное правило.

Просто так эти 7 нормальных форм сгруппированы в 4 функциональных блока в тексте над.

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

Противоположный процесс — денормализация. Это упрощает обработку запросов на доступ к данным за счет добавления избыточных данных (например, как было указано выше в п. 2.1.1 и 2.2.1 с помощью полуструктурированных данных (NoSQL)).

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

Если этот вопрос застал вас врасплох, и вы считаете, что это маловероятно, значит, вы знаете 7 нормальных форм, но не понимаете их.

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

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

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

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

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

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

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

Разработка схемы базы данных для найма

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

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

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

Сначала определим необходимые лиц:

  • Сотрудник
  • Компания
  • Должность
  • Проект
  • Навык

Компания и сотрудник имеют отношения «многие ко многим», поскольку сотрудник может работать в разных компаниях, а в компаниях много сотрудников.

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

Сущность «проект» следует той же логике: проект относится ко всем другим вышеупомянутым сущностям как многие ко многим.

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

Учитывая важность указания стажа работы сотрудника в той или иной компании, на конкретной должности и в конкретном проекте, схема нашей базы данных может иметь следующую ER-диаграмму:

Рис.11. Схема базы данных службы подбора персонала

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

Проект и умение связаны как многие-ко-многим, таким образом, они связаны с помощью ProjectSkill организация.

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

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

Заключение

Как видите, проектирование систем — это просто превращение объектов и субъектов из реальности в сущности базы данных, где отношения между этими сущностями фиксируются в определенный момент времени с учетом будущих изменений.Что именно мы берем из реальности и реализуем как сущность схемы, и какие отношения выстраиваем в модели, зависит от того, чего мы хотим от информационной системы в целом, сейчас и в будущем. То есть, какие данные мы хотим получить на текущий момент и через какое-то время в будущем. Следующая часть будет посвящена заполнению результирующей базы данных данными. Чтобы заполнить нашу базу данных данными, мы будем использовать dbForge для SQL Server.

Евгений — аналитик баз данных MS SQL Server, разработчик и администратор.Он участвует в разработке и тестировании инструментов управления базами данных SQL Server. Евгений также пишет статьи, связанные с SQL Server.

Последние сообщения Евгения Грибкова (посмотреть все)

проектирование базы данных, схема базы данных, sql сервер, студия для sql сервера

Введение в проектирование баз данных | Учебник

Идентификация атрибутов

Элементы данных, которые вы хотите сохранить для каждой сущности, называются «атрибутами».

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

Рисунок 6: Сущности с атрибутами.

Производные данные

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

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

Представление сущностей и отношений: диаграмма отношений сущностей (ERD)

Диаграмма отношений сущностей (ERD) дает графический обзор базы данных. Существует несколько стилей и типов ER-диаграмм.Часто используемая нотация — это нотация «гусиные лапки», где объекты представлены в виде прямоугольников, а отношения между объектами представлены в виде линий между объектами. Знаки в конце строк указывают на тип отношений. Сторона отношений, которая является обязательной для существования другого, будет обозначена через тире на линии. Необязательные сущности обозначены кружком. «Много» обозначается через «гусиные лапки»; линия отношений разделяется на три линии.

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

Обязательная связь 1:1 представлена ​​следующим образом:

Рисунок 7: Обязательные отношения один к одному.

Обязательная связь 1:N:

Рисунок 8: Обязательная связь один ко многим.

Отношения M:N:

Рисунок 9. Обязательная связь «многие ко многим».

Модель нашего примера будет выглядеть так:

Рисунок 10: Модель со связями.

Назначение ключей

Первичные ключи

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

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

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

В ERD атрибуты первичного ключа обозначаются текстом «PK» после имени атрибута.В примере только объект «магазин» не имеет очевидного кандидата на PK, поэтому мы введем для этого объекта новый атрибут: shopnr.

Внешние ключи

Внешний ключ (FK) в объекте — это ссылка на первичный ключ другого объекта. В ERD этот атрибут будет указан с «FK» после его имени. Внешний ключ объекта также может быть частью первичного ключа, в этом случае атрибут будет указан с «PF» после его имени. Обычно это происходит с объектами-ссылками, потому что вы обычно связываете два экземпляра только один раз вместе (при 1 продаже только 1 тип продукта продается 1 раз).

Если мы поместим все связанные сущности, PK и FK в ERD, мы получим модель, как показано ниже. Обратите внимание, что атрибут «Продукты» больше не нужен в «Продажи», потому что «Проданные продукты» теперь включены в таблицу ссылок. В ссылку-таблицу было добавлено еще одно поле «количество», которое указывает, сколько товаров было продано. Поле количества также было добавлено в таблицу запасов, чтобы указать, сколько товаров еще есть в наличии.

Рисунок 11: Первичные и внешние ключи.

Определение типа данных атрибута

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

Стандартные типы данных, которые известны каждой базе данных и которые наиболее часто используются: CHAR, VARCHAR, TEXT, FLOAT, DOUBLE и INT.

Текст:

  • CHAR(длина) — включает текст (символы, цифры, знаки препинания…). Характерной чертой CHAR является то, что он всегда сохраняет фиксированное количество позиций. Если вы определяете CHAR(10), вы можете сохранить максимум десять позиций, но если вы используете только две позиции, база данных все равно сохранит 10 позиций. Остальные восемь позиций будут заполнены пробелами.
  • VARCHAR(длина) — включает текст (символы, цифры, знаки препинания…). VARCHAR — это то же самое, что и CHAR, разница в том, что VARCHAR занимает ровно столько места, сколько необходимо.
  • ТЕКСТ — может содержать большое количество текста. В зависимости от типа базы данных это может составлять до гигабайт.

Номера:

  • INT — содержит положительное или отрицательное целое число. Многие базы данных имеют варианты INT, такие как TINYINT, SMALLINT, MEDIUMINT, BIGINT, INT2, INT4, INT8. Эти вариации отличаются от INT только размером фигуры, которая в них помещается. Обычный INT имеет размер 4 байта (INT4) и подходит для чисел от -2147483647 до +2147483646 или, если вы определяете его как UNSIGNED, от 0 до 4294967296.INT8, или BIGINT, может быть еще больше по размеру, от 0 до 18446744073709551616, но занимает до 8 байт дискового пространства, даже если в нем содержится небольшое число.
  • FLOAT, DOUBLE — та же идея, что и у INT, но также может хранить числа с плавающей запятой. . Обратите внимание, что это не всегда работает идеально. Например, в MySQL вычисления с этими числами с плавающей запятой не идеальны, (1/3) * 3 приведет к тому, что числа с плавающей запятой MySQL будут равны 0,9999999, а не 1.

Другие типы:

  • BLOB — для двоичных данных, таких как файлы.
  • INET — для IP-адресов. Также можно использовать для сетевых масок.

Для нашего примера типы данных следующие:

Рисунок 12: Модель данных, отображающая типы данных.

Нормализация

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

Нормализация, первая форма

Первая форма нормализации гласит, что в объекте не может быть повторяющихся групп столбцов. Мы могли бы создать объект «продажи» с атрибутами для каждого из купленных продуктов. Это будет выглядеть так:

Рис. 13: Не в 1-й нормальной форме.

Что не так, так это то, что теперь можно продать только 3 продукта.Если вам нужно продать 4 продукта, вам придется начать вторую продажу или настроить модель данных, добавив атрибуты «product4». Оба решения нежелательны. В этих случаях вы всегда должны создавать новую сущность, которую вы связываете со старой через отношение «один ко многим».

Рисунок 14: В соответствии с 1-й нормальной формой.

Нормализация, вторая форма

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

Рис. 15: Не во второй нормальной форме.

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

Рис. 16: В соответствии со 2-й нормальной формой.

Теперь каждый атрибут объектов зависит от всего PK объекта. Дата зависит от номера продажи, а количество зависит от номера продажи и проданного продукта.

Нормализация, третья форма

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

Рис. 17: Не в 3-й нормальной форме.

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

Рисунок 18: В соответствии с 3-й нормальной формой.

Нормализация, дополнительные формы

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

Модель нормализованных данных

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

Рисунок 19: Модель данных в соответствии с 1-й, 2-й и 3-й нормальной формой.

Добавить комментарий

Ваш адрес email не будет опубликован.

© 2019 Шоу группа Килиманджаро. Все права защищены