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

Перевод цикла из 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 в данном руководстве обсуждаться не будет. Я сосредоточусь на проектировании баз данных.

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



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

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

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

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

Comments 27

    +7
    Сначала прочитал этот ужас (сама статья, а не перевод) — всё скомкано и непонятно. А потом прочитал, что это цикл из 15 статей, и мне стало страшно.

    Если действительно хотите подтянуть знания по проектированию баз данных, то читаем — Хомоненко А.Д., Цыганков В.М., Мальцев М.Г. «Базы данных: Учебник для высших учебных заведений». Именно раздел о проектировании, остальная часть уже прилично устарела.
      0
      Спасибо за наводку. Буду смотреть. А по поводу статьи. Каждый воспринимает информацию по-своему. При прочтении различных источников что-то может оказаться неясным и в данных статьях, если отделять зерна от плевел, я нашел для себя недостающие кусочки информационной мозаики, при всей, порой, простоте (может и не в лучшем смысле этого слова) изложения. Возможно кому-то это поможет, все мы разные. А не поможет… c'est la vie
      0
      Читайте классику citforum.ru/database/osbd/contents.shtml
        +1
        К слову про фрагмент:
        Существует отличное бесплатное приложение MySQL Workbench. Оно позволяет спроектировать вашу базу данных графически. Изображения диаграмм в руководстве сделаны в этой программе.

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

            Вот сейчас работаю преимущественно с PostgreSQL и не хватает инструмента, который был бы как и Workbench, своего рода швейцарским ножом для PostgreSQL. Но к сожалению, пока ничего лучше, чем EMS SQL Manager (Lite версия) не нашёл. Хотя и он не плох, очень даже не плох.
              0
              лучше Воркбенча вообще ничего нет

              Частично согласен (в абсолюте судить не берусь), универсальный инструмент. Тоже им пользуюсь, наряду еще с одним попроще.
          0
          Я бы это публиковать на хабре постеснялся.
            0
            Вы все не обижайтесь, но если вам это не интересно, то ваш уровень знаний далеко впереди.
            Серия статей — для новичков.
            Кому-то, надеюсь, это поможет, а это самое главное.
              0
              Как раз для новичков эта статья вредна (кстати, я не заметил в статье ссылки на первоисточник). В принципе, отдельные тезисы верны, но изложение… Статью можно разбирать на цитаты:

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

              Хороший пример. Все верно, не придерешься. Но куда делись UPDATE с DELETE-ом?

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

              Аналогично. Относительно бесплатности MySQL и его открытости (особенно после покупки Oracle-ом) есть разные мнения. По поводу наибольшей популярности, тезис также требует доказательств.

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

              Хммм… А где PostgreSQL? Собственно это в продолжение предыдущей цитаты.

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

              Да ладно, все было не так печально. Например индексы тогда уже были (деталей я за давностью лет уже не помню, но можно посмотреть здесь, мы ведь говорим про CODASYL?). И потом, что значит «бесструктурно», если речь идет о CSV-файлах? Вполне себе структура. Кроме того, замалчивается тот факт, что данные, в то время, хранились на лентах. Соответственно, структуры данных, ориентированные на выборку с произвольным доступом, были не востребованы.

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

              Это быстрее далеко не во всех обстоятельствах. Очень часто полный просмотр выгоднее чем доступ по индексу.

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

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

              И так вся статья. Изложение поверхностное, неряшливое и местами неточное. Я убежден, что эта статья вредна для начинающих разработчиков и совершенно бесполезна для чуть более опытных. Настоятельно рекомендую переключиться на перевод чего либо более полезного.
                0
                Добротно расписали)
                Тоже подумал, «А где же PostgreSQL?». Осмелюсь предположить, что оригинальные статьи очень старые.
                И я согласен с вами в том, что статья вредна для начинающих.
                  0
                  а Sybase где? обидно однако…
                    0
                    За Sybase тоже обидно, но он не бесплатный. А про MySQL утверждается что он чуть ли не самый популярный и бесплатный, что несколько не соответствует действительности.

                    Что еще надо ?

                    Надо PostgreSQL, дабы у новичков не складывалось неправильное представление об арсенале бесплатных РСУБД
                      +1
                      Вообще предпочтительнее начинать, как мне кажется, именно с PostgreSQL, а не с MySQL. Функционал значительно шире и зная PG можно будет быстро разобраться с остальными. Например, работа с теми же самыми хранимыми процедурами, которых нет в MySQL. Ещё вьюхи, не зависимая от типа таблиц возможность использовать внешние ключи и т.п.
                        0
                        Вот вы являетесь наглядным примером того, что зная PG в MySQL не смогли разобраться dev.mysql.com/doc/refman/5.0/en/stored-routines.html

                        Соответственно ценность вашего мнения близка к вашей оценке наличия хранимых процедур в MySQL — нулевая.
                          +1
                          Согласен, лажанул.
                          Сейчас настрочил длинное оправдание, но подумал, кому это надо и стёр его:-)

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

                          После MySQL пришлось столкнуться с Firebird, SQLite и, собственно PG. И последний привёл меня в дикий восторг. PG в некоторых местах заставляет думать по-другому. И у меня возникла мысль, что, если бы я начал не с MySQL, а с PG, то, скорее всего у меня бы остались совершенно другие впечатления от MySQL.

                          В общем можно гадать о том как было бы, но моё мнение (ИМХО), мне надо было начинать с того от чего мои впечатления были лучше.

                          P.S.: каюс, надо было пробежаться по документации MySQL, прежде чем писать.
            +1
            Спасибо за перевод.
              0
              Приятно видеть простое «спасибо»… Вам спасибо за то, что читали.
              0
              еще вот хорошая и толстая книга
              Системы баз данных. Полный курс — Гектор Гарсиа-Молина, Джеффри Ульман, Дженнифер Уидом
              www.ozon.ru/context/detail/id/1351096/
                0
                Спасибо. Оглавление вызывает интерес.
                  0
                  А на курсере еще есть стэнфордский курс по базам данных от Дженнифер Уидом
                  class.coursera.org/db/class/index
                    0
                    Неплохой курс.
                    Не знал про данный ресурс. Спасибо.
                  +1
                  Вы пишете, что SQLyog — бесплатная утилита.
                  Странно, я думал, что она — платная
                  www.webyog.com/product/sqlyog
                    0
                      0
                      Обалдеть!
                      Я всегда думал, что она платная!
                      Интересно, а это одна и та же программа? Я обязательно проверю завтра.

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

                    Only users with full accounts can post comments. Log in, please.