Реляционные базы данных для чайников

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

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

Табличные данные могут быть вставлены, восстановлены, обновлены и удалены. Для пакета этих операций была создана специальная аббревиатура CRUD (Create-Read-Update-Delete).

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

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

Шаг 1. Подготовка данных

Для того чтобы нам было с чем работать, я набрал в твиттере запрос “#databases” и сформировал таблицу из 10 записей:

Таблица 1

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

• full_name: имя пользователя
• username: логин в Twitter-е
• text: текст твита
• created_at: время создания твита
• following_username: список пользователей, разделённых запятыми, которые подписались на этот твитт. Для краткости я сократил этот список до 2 имён.

Это реальные данные. Если хотите, вы можете их найти и обновить.

Хорошо. Теперь все наши данные находятся в одном месте. Даёт ли это нам возможность легко осуществить поиск по ним? Не совсем. Данная таблица далека от идеала. Во-первых, в некоторых столбцах у нас есть повторяющиеся записи: к примеру, в х “username” и “following_username”. Также колонка “following_username” нарушает правила реляционных моделей, т.к. её в ячейках присутствует более 1 значения (записи разделены запятыми).

К тому же у нас попадаются дубликаты и в строках.

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

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

Шаг 2. Избавляемся от дубликатов в столбцах

Как было оговорено выше, столбцы “username” и “following_username” содержат дубликаты данных. Они возникли в результате того, что я хотел отобразить отношения между твиттами и пользователями. Давайте улучшим нашу структуру БД, разделив существующую таблицу на две: в одной будем хранить информацию, а в другой - отношения между записями.

Поскольку @Brett_Englebert подписан на @RealSkipBayless, то в таблице “following” отобразим это следующим образом: имя @Brett_Englebert поместим в колонку “from_user”, а @RealSkipBayless в “to_user.” Давайте посмотрим, как будет выглядеть таблица “following” после разделения Таблицы 1:

Таблица 2. following

Таблица 3. users

Уже лучше. Теперь в таблице “users” (Таблица 3) у нас хранится только информация о твиттах, а в таблице following (Таблица 2) - зависимость пользователей.

Основатель теории реляционных баз данных, Эдгар Кодд, назвал бы этот процесс (удаления повторений из столбцов таблиц) приведением БД к первой нормальной форме.

Шаг 3. Удаление повторений из строк

Теперь мы займёмся устранением других проблем, а именно, избавимся от дубликатов в строках таблицы “users”. Поскольку пользователи @AndyRyder5 и @Brett_Englebert разместили по несколько твиттов, то их имена в таблице “users” (Таблица 3) дублируются в колонке full_name. Данная проблема также решается разделением таблицы “users”.

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

Таблица 4. tweets

Таблица 5. users

После разделения в таблице users (Таблица 5) у нас присутствуют уникальные (не повторяющиеся) строки.

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

Шаг 4. Объединяем таблицы на основе ключей

Итак, в результате наших действий, Таблица 1 была разбита на 3 части: following (Таблица 2), tweets (Таблица 4), users (Таблица 5). Все дубликаты устранены. Для того чтобы в дальнейшем мы могли с лёгкостью извлекать данные из этой структуры, независимые друг от друга таблицы мы должны связать специальными отношениями, которые будут давать нам информацию о том, какому пользователю принадлежит какой твит, и кто на кого подписан.

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

Вообще говоря, в Таблице 4 и 5 мы уже это сделали. В таблице “users” первичным ключом является колонка “username”, потому что логин пользователя должен быть уникальным значением и не может повторяться. В таблице “tweets” мы используем данный ключ для обозначения связи между пользователем и твитом. Колонка “username” в таблице “tweets” называется внешним ключом.

Если вы когда-то работали с базами данных, то у вас может возникнуть вопрос: можем ли мы использовать колонку “username” в качестве первичного ключа?

С одной стороны, это может упростить процесс поиска, ведь мы не используем никаких числовых ID. С другой стороны, что если пользователь захочет поменять свой логин? Это может привести к огромному количеству проблем. Для того чтобы не попасть в подобную ситуацию, лучше воспользоваться числовыми ID. Всё зависит от вашей системы. Если вы предоставляете вашим пользователям возможность менять логины, то лучше в качестве первичного ключа использовать автоинкрементированное числовое поле ID. В противном случае, колонка “username” вполне подойдёт для этой роли. Я оставлю всё как есть.

Давайте посмотрим на таблицу tweets (Таблица 4). Первичный ключ должен быть уникальным для каждой строки. Какую колонку в данной таблице мы можем выбрать для этой роли? Колонка “created_at” не подойдёт, т.к. в принципе 2 разных пользователя могут в одно и то же время опубликовать запись. С колонкой “text” та же история: два разных пользователя могут создать твит с текстом “Hello World”. Колонка “username” в данной таблице является внешним ключом для обозначения связи между пользователем и твитом. Итак, поскольку все возможные варианты нам не подходят, то лучшим решением будет добавление колонки id, которая будет первичным ключом для данной таблицы.

Таблица 6. tweets с колонкой id

С таблицей following можем сделать то же самое, т.к. ни одна существующая колонка не подойдёт на роль первичного ключа. Колонки “from_user” и “to_user” являются внешними ключами и обозначают связь между подписками пользователей.

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

Ниже вы можете увидеть схему наших таблиц и связей между ними:

Системы Управления Базами Данных

Теперь, когда у нас есть реляционная БД, каким образом мы можем её имплементировать? Для этого мы можем воспользоваться системами управления базами данных (СУБД). Существует целый набор подобных программ, как платных, так и бесплатных. Среди платных можно выделить Oracle Database, IBM DB2 и Microsoft SQL Server. Бесплатные: MySQL, SQLite и PostgreSQL.

Чаще всего различные компании используют MySQL. Twitter в этом смысле - не исключение.

SQLite чаще используется при разработке приложений для iOS и Android, где хранится различного рода конфиденциальная информация. Браузер Google Chrome использует SQLite для хранения истории просмотров, кукисов, изображений...

PostgreSQL используется реже. Для неё существует полезное расширение PostGIS, которое делает данную СУБД удобной для хранения геолокационных данных. К примеру сервис OpenStreetMap исользует PostgreSQL.

Язык структурированных запросов (SQL)

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

Создание БД development:

CREATE DATABASE development;

Создание таблицы Users:

CREATE TABLE users (
  full_name VARCHAR(100),
  username VARCHAR(100)
);

При создании полей нам необходимо указать тип хранимой информации и её размер. Колонки “full_name” и “username” будут типа VARCHAR, который предназначен для хранения строк символов. Размер 100 символов. Список всех типов вы можете найти тут.

Добавление записи:

INSERT INTO users (full_name, username)
VALUES ("Boris Hadjur", "_DreamLead");

Извлечение всех записей пользователя _DreamLead:

SELECT text, created_at FROM tweets WHERE username="_DreamLead";

Обновление записи:

UPDATE users
SET full_name="Boris H"
WHERE username="_DreamLead";

Удаление записи:

DELETE FROM users
WHERE username="_DreamLead";

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

Итог

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

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