Реляционные базы данных для чайников
Как правило, любое веб приложение можно разделить на 2 основные части: фронт-энд, где отображается вся информация сайта, и бэк-энд, где данная информация формируется и размещается. В этой статье мы поговорим о том, что такое реляционные базы данных, и как их проектировать.
База данных хранит записи в специально организованном виде, чтобы информацию можно было легко найти и извлечь. Любая БД состоит из одной или нескольких таблиц. Электронная таблица состоит из строк и столбцов. Все строки имеют одинаковые столбцы, а каждый столбец содержит данные. В общем, для лучшего понимания, определимся, что таблицы в БД очень похожи на те, что вы видели в Excel-е.
Табличные данные могут быть вставлены, восстановлены, обновлены и удалены. Для пакета этих операций была создана специальная аббревиатура CRUD (Create-Read-Update-Delete).
Реляционные базы данных - это базы, где вся информация хранится в таблицах, связанных друг с другом специальными отношениями. Эти отношения позволяют нам извлекать и объединять данные из одной или нескольких таблиц с помощью одного запроса.
Но всё это всего лишь слова. Для того чтобы действительно понять, что такое реляционные базы данных, вам нужно больше практиковаться. Давайте же начнём и посмотрим, с какими данными нам предстоит работать.
Шаг 1. Подготовка данных
Для того чтобы нам было с чем работать, я набрал в твиттере запрос “#databases” и сформировал таблицу из 10 записей:
Таблица 1
full_name | username | text | created_at | following_username |
---|---|---|---|---|
Boris Hadjur | _DreamLead | What do you think about #emailing #campaigns #traffic in #USA? Is it a good market nowadays? do you have #databases? | Tue, 12 Feb 2013 08:43:09 +0000 | Scootmedia, MetiersInternet |
Gunnar Svalander | GunnarSvalander | Bill Gates Talks Databases, Free Software on Reddit http://t.co/ShX4hZlA #billgates #databases | Tue, 12 Feb 2013 07:31:06 +0000 | klout, zillow |
GE Software | GEsoftware | RT @KirkDBorne: Readings in #Databases: excellent reading list, many categories: http://t.co/S6RBUNxq via @rxin Fascinating. | Tue, 12 Feb 2013 07:30:24 +0000 | DayJobDoc, byosko |
Adrian Burch | adrianburch | RT @tisakovich: @NimbusData at the @Barclays Big Data conference in San Francisco today, talking #virtualization, #databases, and #flash memory. | Tue, 12 Feb 2013 06:58:22 +0000 | CindyCrawford, Arjantim |
Andy Ryder | AndyRyder5 | http://t.co/D3KOJIvF article about Madden 2013 using AI to prodict the super bowl #databases #bus311 | Tue, 12 Feb 2013 05:29:41 +0000 | MichaelDell, Yahoo |
Andy Ryder | AndyRyder5 | http://t.co/rBhBXjma an article about privacy settings and facebook #databases #bus311 | Tue, 12 Feb 2013 05:24:17 +0000 | MichaelDell, Yahoo |
Brett Englebert | Brett_Englebert | #BUS311 University of Minnesota’s NCFPD is creating #databases to prevent “food fraud.” http://t.co/0LsAbKqJ | Tue, 12 Feb 2013 01:49:19 +0000 | RealSkipBayless, stephenasmith |
Brett Englebert | Brett_Englebert | #BUS311 companies might be protecting their production #databases, but what about their backup files? http://t.co/okJjV3Bm | Tue, 12 Feb 2013 01:31:52 +0000 | RealSkipBayless, stephenasmith |
Nimbus Data Systems | NimbusData | @NimbusData CEO @tisakovich @BarclaysOnline Big Data conference in San Francisco today, talking #virtualization, #databases,& #flash memory | Mon, 11 Feb 2013 23:15:05 +0000 | dellock6, rohitkilam |
SSWUG.ORG | SSWUGorg | Don’t forget to sign up for our FREE expo this Friday: #Databases, #BI, and #Sharepoint: What You Need to Know! http://t.co/Ijrqrz29 | Mon, 11 Feb 2013 22:15:37 +0000 | drsql, steam_games |
В первую очередь, давайте разберёмся с колонками:
- 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
from_user | to_user |
---|---|
_DreamLead | Scootmedia |
_DreamLead | MetiersInternet |
GunnarSvalander | klout |
GunnarSvalander | zillow |
GEsoftware | DayJobDoc |
GEsoftware | byosko |
adrianburch | CindyCrawford |
adrianburch | Arjantim |
AndyRyder | MichaelDell |
AndyRyder | Yahoo |
Brett_Englebert | RealSkipBayless |
Brett_Englebert | stephenasmith |
NimbusData | dellock6 |
NimbusData | rohitkilam |
SSWUGorg | drsql |
SSWUGorg | steam_games |
Таблица 3. users
full_name | username | text | created_at |
---|---|---|---|
Boris Hadjur | _DreamLead | What do you think about #emailing #campaigns #traffic in #USA? Is it a good market nowadays? do you have #databases? | Tue, 12 Feb 2013 08:43:09 +0000 |
Gunnar Svalander | GunnarSvalander | Bill Gates Talks Databases, Free Software on Reddit http://t.co/ShX4hZlA #billgates #databases | Tue, 12 Feb 2013 07:31:06 +0000 |
GE Software | GEsoftware | RT @KirkDBorne: Readings in #Databases: excellent reading list, many categories: http://t.co/S6RBUNxq via @rxin Fascinating. | Tue, 12 Feb 2013 07:30:24 +0000 |
Adrian Burch | adrianburch | RT @tisakovich: @NimbusData at the @Barclays Big Data conference in San Francisco today, talking #virtualization, #databases, and #flash memory. | Tue, 12 Feb 2013 06:58:22 +0000 |
Andy Ryder | AndyRyder5 | http://t.co/D3KOJIvF article about Madden 2013 using AI to prodict the super bowl #databases #bus311 | Tue, 12 Feb 2013 05:29:41 +0000 |
Andy Ryder | AndyRyder5 | http://t.co/rBhBXjma an article about privacy settings and facebook #databases #bus311 | Tue, 12 Feb 2013 05:24:17 +0000 |
Brett Englebert | Brett_Englebert | #BUS311 University of Minnesota’s NCFPD is creating #databases to prevent “food fraud.” http://t.co/0LsAbKqJ | Tue, 12 Feb 2013 01:49:19 +0000 |
Brett Englebert | Brett_Englebert | #BUS311 companies might be protecting their production #databases, but what about their backup files? http://t.co/okJjV3Bm | Tue, 12 Feb 2013 01:31:52 +0000 |
Nimbus Data Systems | NimbusData | @NimbusData CEO @tisakovich @BarclaysOnline Big Data conference in San Francisco today, talking #virtualization, #databases,& #flash memory | Mon, 11 Feb 2013 23:15:05 +0000 |
SSWUG.ORG | SSWUGorg | Don’t forget to sign up for our FREE expo this Friday: #Databases, #BI, and #Sharepoint: What You Need to Know! http://t.co/Ijrqrz29 | Mon, 11 Feb 2013 22:15:37 +0000 |
Уже лучше. Теперь в таблице “users” (Таблица 3) у нас хранится только информация о твиттах, а в таблице following (Таблица 2) - зависимость пользователей.
Основатель теории реляционных баз данных, Эдгар Кодд, назвал бы этот процесс (удаления повторений из столбцов таблиц) приведением БД к первой нормальной форме.
Шаг 3. Удаление повторений из строк
Теперь мы займёмся устранением других проблем, а именно, избавимся от дубликатов в строках таблицы “users”. Поскольку пользователи @AndyRyder5 и @Brett_Englebert разместили по несколько твиттов, то их имена в таблице “users” (Таблица 3) дублируются в колонке full_name. Данная проблема также решается разделением таблицы “users”.
Поскольку текст твитта и время его создания являются уникальными данными, то их мы поместим в одну и ту же таблицу. Также нам нужно указать связь между твитами и пользователями. Для этого я создал специальный столбец username.
Таблица 4. tweets
username | text | created_at |
---|---|---|
_DreamLead | What do you think about #emailing #campaigns #traffic in #USA? Is it a good market nowadays? do you have #databases? | Tue, 12 Feb 2013 08:43:09 +0000 |
GunnarSvalander | Bill Gates Talks Databases, Free Software on Reddit http://t.co/ShX4hZlA #billgates #databases | Tue, 12 Feb 2013 07:31:06 +0000 |
GEsoftware | RT @KirkDBorne: Readings in #Databases: excellent reading list, many categories: http://t.co/S6RBUNxq via @rxin Fascinating. | Tue, 12 Feb 2013 07:30:24 +0000 |
adrianburch | RT @tisakovich: @NimbusData at the @Barclays Big Data conference in San Francisco today, talking #virtualization, #databases, and #flash memory. | Tue, 12 Feb 2013 06:58:22 +0000 |
AndyRyder5 | http://t.co/D3KOJIvF article about Madden 2013 using AI to prodict the super bowl #databases #bus311 | Tue, 12 Feb 2013 05:29:41 +0000 |
AndyRyder5 | http://t.co/rBhBXjma an article about privacy settings and facebook #databases #bus311 | Tue, 12 Feb 2013 05:24:17 +0000 |
Brett_Englebert | #BUS311 University of Minnesota’s NCFPD is creating #databases to prevent “food fraud.” http://t.co/0LsAbKqJ | Tue, 12 Feb 2013 01:49:19 +0000 |
Brett_Englebert | #BUS311 companies might be protecting their production #databases, but what about their backup files? http://t.co/okJjV3Bm | Tue, 12 Feb 2013 01:31:52 +0000 |
NimbusData | @NimbusData CEO @tisakovich @BarclaysOnline Big Data conference in San Francisco today, talking #virtualization, #databases,& #flash memory | Mon, 11 Feb 2013 23:15:05 +0000 |
SSWUGorg | Don’t forget to sign up for our FREE expo this Friday: #Databases, #BI, and #Sharepoint: What You Need to Know! http://t.co/Ijrqrz29 | Mon, 11 Feb 2013 22:15:37 +0000 |
Таблица 5. users
full_name | username |
---|---|
Boris Hadjur | _DreamLead |
Gunnar Svalander | GunnarSvalander |
GE Software | GEsoftware |
Adrian Burch | adrianburch |
Andy Ryder | AndyRyder5 |
Brett Englebert | Brett_Englebert |
Nimbus Data Systems | NimbusData |
SSWUG.ORG | SSWUGorg |
После разделения в таблице 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
ID | username | text | created_at |
---|---|---|---|
1 | _DreamLead | What do you think about #emailing #campaigns #traffic in #USA? Is it a good market nowadays? do you have #databases? | Tue, 12 Feb 2013 08:43:09 +0000 |
2 | GunnarSvalander | Bill Gates Talks Databases, Free Software on Reddit http://t.co/ShX4hZlA #billgates #databases | Tue, 12 Feb 2013 07:31:06 +0000 |
3 | GEsoftware | RT @KirkDBorne: Readings in #Databases: excellent reading list, many categories: http://t.co/S6RBUNxq via @rxin Fascinating. | Tue, 12 Feb 2013 07:30:24 +0000 |
4 | adrianburch | RT @tisakovich: @NimbusData at the @Barclays Big Data conference in San Francisco today, talking #virtualization, #databases, and #flash memory. | Tue, 12 Feb 2013 06:58:22 +0000 |
5 | AndyRyder5 | http://t.co/D3KOJIvF article about Madden 2013 using AI to prodict the super bowl #databases #bus311 | Tue, 12 Feb 2013 05:29:41 +0000 |
6 | AndyRyder5 | http://t.co/rBhBXjma an article about privacy settings and facebook #databases #bus311 | Tue, 12 Feb 2013 05:24:17 +0000 |
7 | Brett_Englebert | #BUS311 University of Minnesota’s NCFPD is creating #databases to prevent “food fraud.” http://t.co/0LsAbKqJ | Tue, 12 Feb 2013 01:49:19 +0000 |
8 | Brett_Englebert | #BUS311 companies might be protecting their production #databases, but what about their backup files? http://t.co/okJjV3Bm | Tue, 12 Feb 2013 01:31:52 +0000 |
9 | NimbusData | @NimbusData CEO @tisakovich @BarclaysOnline Big Data conference in San Francisco today, talking #virtualization, #databases,& #flash memory | Mon, 11 Feb 2013 23:15:05 +0000 |
10 | SSWUGorg | Don’t forget to sign up for our FREE expo this Friday: #Databases, #BI, and #Sharepoint: What You Need to Know! http://t.co/Ijrqrz29 | Mon, 11 Feb 2013 22:15:37 +0000 |
С таблицей 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.
Источник: http://feedproxy.google.com/~r/ruseller/CdHX/~3/LrvHdOWWMjg/lessons.php
Дайджест новых статей по интернет-маркетингу на ваш email
Новые статьи и публикации
- 2024-09-30 » Как быстро запустить Laravel на Windows
- 2024-09-25 » Next.js
- 2024-09-05 » OpenAI рассказал, как запретить ChatGPT использовать содержимое сайта для обучения
- 2024-08-28 » Чек-лист: как увеличить конверсию интернет-магазина на примере спортпита
- 2024-08-01 » WebSocket
- 2024-07-26 » Интеграция с Яндекс Еда
- 2024-07-26 » Интеграция с Эквайринг
- 2024-07-26 » Интеграция с СДЕК
- 2024-07-26 » Интеграция с Битрикс-24
- 2024-07-26 » Интеграция с Travelline
- 2024-07-26 » Интеграция с Iiko
- 2024-07-26 » Интеграция с Delivery Club
- 2024-07-26 » Интеграция с CRM
- 2024-07-26 » Интеграция с 1C-Бухгалтерия
- 2024-07-24 » Что такое сторителлинг: техники и примеры
- 2024-07-17 » Ошибка 404: что это такое и как ее использовать для бизнеса
- 2024-07-03 » Размещайте прайс-листы на FarPost.ru и продавайте товары быстро и выгодно
- 2024-07-01 » Профилирование кода в PHP
- 2024-06-28 » Изучаем ABC/XYZ-анализ: что это такое и какие решения с помощью него принимают
- 2024-06-17 » Зачем вам знать потребности клиента
- 2024-06-11 » Что нового в работе Яндекс Метрики: полный обзор обновления
- 2024-06-11 » Поведенческие факторы ранжирования в Яндексе
- 2024-06-11 » Скорость загрузки сайта: почему это важно и как влияет на ранжирование
- 2024-05-27 » Подборка сервисов для расшифровки аудио в текст
- 2024-05-27 » PostgreSQL 16. Изоляция транзакций. Часть 2
- 2024-05-06 » Как настраивать конверсионные стратегии: работа над ошибками
- 2024-04-22 » Комментирование кода и генерация документации в PHP
- 2024-04-22 » SEO в России и на Западе: в чем основные отличия
- 2024-04-22 » SEO для международного масштабирования
- 2024-04-22 » Как использовать XML-карты для продвижения сайта
Секрет быть несчастным: иметь время занудствовать на тему, счастлив ты или нет Шоу Джордж Бернард - (1856-1950) - английский писатель. В своем творчестве ниспровергал догматизм и предвзятость, традиционность представлений |
Мы создаем сайты, которые работают! Профессионально обслуживаем и продвигаем их , а также по всей России и ближнему зарубежью с 2006 года!
Как мы работаем
Заявка
Позвоните или оставьте заявку на сайте.
Консультация
Обсуждаем что именно Вам нужно и помогаем определить как это лучше сделать!
Договор
Заключаем договор на оказание услуг, в котором прописаны условия и обязанности обеих сторон.
Выполнение работ
Непосредственно оказание требующихся услуг и работ по вашему заданию.
Поддержка
Сдача выполненых работ, последующие корректировки и поддержка при необходимости.