Сейчас мы с вами поговорим о правильной организации баз данных.

Пусть у нас дана вот такая таблица users:

id
айди
name
имя
city
город
1 Коля Минск
2 Вася Минск
3 Петя Москва
4 Иван Москва
5 Богдан Киев

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

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

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

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

Что же делать? Нужно разбить нашу таблицу на две: в одной будут хранится города, а во второй - юзеры. При этом в таблице с юзерами будет колонка city_id, которая будет ссылаться на город юзера.

Итак, давайте сделаем две таблицы - cities (города) и users (юзеры).

Таблица cities:

id
айди
name
название
1 Минск
2 Москва
3 Киев

Таблица users:

id
айди
name
имя
city_id
айди города
1 Коля 1
2 Вася 1
3 Петя 2
4 Иван 2
5 Богдан 3

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

Давайте сделаем запрос, который достанет всех юзеров вместе с их городами. Для этого нам понадобится команда LEFT JOIN:

SELECT *, cities.name as city_name
FROM users LEFT JOIN cities ON cities.id=users.city_id

В результате SQL запрос выберет следующие строки:

id
айди
name
имя
city_id
айди города
city_name
название города
1 Коля 1 Минск
2 Вася 1 Минск
3 Петя 2 Москва
4 Иван 2 Москва
5 Богдан 3 Киев

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

А с точки зрения города это связь один ко многим - одному городу принадлежит много юзеров.

Пусть теперь юзер был в разных городах.

В этом случае таблица users могла бы иметь следующий вид:

id
айди
name
имя
cities
города
1 Коля Минск, Москва
2 Вася Минск, Киев
3 Петя Москва, Киев

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

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

Таблица rel будет иметь колонку user_id и колонку city_id. Каждая запись в этой таблицы будет символизировать одну связь. То есть связь Коля-Минск - это одна запись, а связь Коля-Москва - это вторая запись.

Итак, вот наша таблица rel:

id
айди
user_id
айди юзера
city_id
айди города
1 1 1
1 1 2
2 2 1
3 2 3
4 3 2
5 3 3

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

id
айди
user_id
айди юзера
city_id
айди города
1 1 Коля 1 Минск
1 1 Коля 2 Москва
2 2 Вася 1 Минск
3 2 Вася 3 Киев
4 3 Петя 2 Москва
5 3 Петя 3 Киев

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

id
айди
name
название
1 Минск
2 Москва
3 Киев

А таблица с юзерами users будет выглядеть так (обратите внимание - никаких связей нет, они все живут в таблице rel):

id
айди
name
имя
1 Коля
2 Вася
3 Петя

Давайте сделаем запрос, с помощью которого вытащим юзеров вместе с их городами. Для этого нам понадобится два LEFT JOIN:

SELECT *, cities.name as city_name
FROM users
LEFT JOIN rel ON rel.user_id=users.id
LEFT JOIN cities ON rel.city_id=cities.id

В результате SQL запрос выберет следующие строки (один юзер будет в нескольких строках таблицы - столько раз, сколько городов с ним связано):

id
айди
name
имя
city_name
название города
1 Коля Минск
1 Коля Москва
2 Вася Минск
2 Вася Киев
3 Петя Киев

Родственные связи

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

Первая идея, которая может прийти вам в голову - сделать 2 таблицы: первая - это parents (отцы), а вторая - sons - сыновья и связать их каким-нибудь полем.

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

Более хороший вариант - связать таблицу саму с собой: сделаем таблицу users, в ней будем хранить все юзеров и каждому сделаем поле son_id, в котором будет храниться id сына из этой же таблицы:

id
айди
name
имя
son_id
айди сына
1 Коля 2
2 Вася 3
3 Петя null

Интересный подход и не зная его - тяжело догадаться так сделать, поэтому запоминайте:)

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

SELECT *, sons.name as son_name
FROM users
LEFT JOIN users as sons ON sons.id=users.son_id

Отец имеет несколько сыновей

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

id
айди
name
имя
father_id
айди отца
1 Коля null
2 Вася 1
3 Петя 1

Двойная связь

Решим такую задачу: есть города (таблица cities) и маршруты между городами (таблица routes). Каждый маршрут имеет город начала и город конца:

id
айди
from_city_id
айди города отправления
to_city_id
айди города прибытия
1 1 2
2 3 4

Напомню таблицу cities с городами:

id
айди
name
название
1 Минск
2 Москва
3 Киев

Некоторую сложность здесь представляет SQL запрос на вытягивания маршрутов - в этом случае нам необходимо сделать два LEFT JOIN к одной и той же таблице cities - в этом случае эту таблицу необходимо два раза переименовать через as:

SELECT *, from_cities.name as from_city_name, to_cities.name as to_city_name
FROM routes
LEFT JOIN cities as from_cities ON from_cities.id=users.from_city_id
LEFT JOIN cities as to_cities ON to_cities.id=users.to_city_id

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

юзер - профиль

Денормализация

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

http://i-novice.net/6-normalnyx-form-bd/