 |
Что такое MySQL? Принципы проектирования БД
|
|
|
|
MySQL – свободно распространяемая реляционная СУБД, предназначенная для хранения сравнительно небольшого объема данных. Наиболее часто данная СУБД используется при создании веб-приложений. Средства для работы с MySQL входят в стандартный пакет языка PHP.
Реляционная база данных состоит из таблиц, как правило, связанных между собой. Каждая таблица имеет название, и содержит логически сгруппированные данные, то есть данные, относящиеся к одному объекту или событию. Например, таблица, в которой хранятся учетные данные пользователей сайта, будет иметь одинаковый набор данных для каждого пользователя. Таблицы в реляционной модели также называют сущностями.
Таблицы состоят из столбцов (называемых также полями) и строк (записей). Существуют определенные правила проектирования таблиц базы данных:
· В таблице не может быть двух одинаковых строк. У каждой записи должен быть так называемый первичный ключ – поле, или совокупность полей, по которому одну запись можно однозначно отличить от всех остальных записей в пределах таблицы. Для удобства принято такое поле создавать искусственно и хранить в нем уникальный номер записи.
· Столбцы располагаются в том порядке, который задается при создании таблицы. Можно создать таблицу без единой записи, но без хотя бы одного столбца нельзя.
· Название столбца в пределах таблицы должно быть уникальным, и все значения в одном столбце должны относиться к одному типу данных.
· На пересечении столбца и строки может находиться только неделимое значение (одно значение, не состоящее из группы значений). Таблицы, удовлетворяющие этому условию, называют нормализованными.
На рисунке 155 изображена таблица для хранения данных пользователей, спроектированная в соответствии с этими правилами.
|
|
|
| Таблица user | |||
| id | login | password | |
| Vaniya | F_6zgd@ | [email protected] | |
| John | Jhjg%g99 | john@ pochta.com | |
| … | |||
| Pinky_Pie85 | Fh4hk9$$ | mlpForever@ pochta.com |
Рисунок 155 - Пример сущности в реляционной БД
Здесь:
· user – название таблицы;
· id – поле «Первичный ключ»;
· login - поле для хранения логина;
· password - поле для хранения пароля;
· e-mail – поле для хранения адреса электронной почты.
Но БД веб-приложения не может содержать только одну таблицу.
Скажем, у нас есть таблица user, в которой хранятся данные пользователей. Мы хотим добавить на сайт возможность оставлять комментарии. Для хранения комментариев, создадим таблицу comments. В ней должны быть следующие поля:
· id – первичный ключ,
· date – дата создания комментария,
· header – заголовок комментария,
· text – содержимое комментария
Также в таблице должна храниться информация о пользователе, который оставил комментарий. Но какая это должна быть информация? Имя? Адрес электронной почты? Но ведь эти данные уже хранятся в таблице user. Кроме того, нам могут понадобиться дополнительные данные о пользователе. Если все эти данные поместить в таблицу comments, то в ней появится большое количество полей, не связанных напрямую с назначением таблицы, и полностью дублирующих таблицу user, как это видно на рисунке 156.
Рисунок 156 - Пример структуры реляционной БД – без связей
Данные в БД необходимо распределять между таблицами таким образом, чтобы избегать дублирования информации. В нашем примере данные о пользователе уже содержатся в таблице user. И вместо того, чтобы дублировать их в таблице comments, можно просто связать эти таблицы между собой, как это показано на рисунке 157.
Рисунок 157 - Пример структуры реляционной БД – со связями
Связывание таблиц происходит через соответствие «Первичный ключ - Внешний ключ». То есть в подчиняемой таблице создается дополнительное поле, в котором будут храниться значения, соответствующие значениям поля Первичного ключа из главной таблицы.
|
|
|
Зная значение внешнего ключа, мы легко можем получить данные из таблицы, сравнивая его со значениями Первичного ключа. В нашем примере поле user_id – внешний ключ, в нем хранятся значения поля id из таблицы «user».
В реляционной модели связи между таблицами могут быть трех видов:
· Связь один-ко-многим – то есть одной записи в таблице соответствуют многие записи в связанной таблице,
· Связь один-к-одному -одной записи в таблице соответствует одна запись в связанной таблице,
· Связь многие-ко-многим -многим записям в таблице соответствуют многие записи в связанной таблице. Тип многие ко многим не реализован напрямую. Для его создания необходимо создавать дополнительную таблицу и разбивать связь многие-ко-многим на две связи один-ко-многим.
Наиболее распространённой является связь один-ко-многим. Пример такой связи - это связь между таблицами user и comments. Один пользователь может написать множество комментариев, но один комментарий не может быть написан несколькими пользователями.
Рассмотрим примеры других типов связей.
Связь многие-ко-многим встречается реже и, как уже упоминалось, напрямую эту связь реализовать не получится.
Добавим для пользователей возможность отмечать понравившиеся комментарии. Для этого в таблице comments создадим поле like, в котором будем хранить число отметок «Мне нравится». Но как следует сохранять информацию о том, какие пользователи отметили комментарий как понравившийся? Если мы попробуем установить связь между таблицами по внешнему ключу, то потерпим неудачу, так как получим недопустимую связь, как это видно на рисунке 158:
Рисунок 158 - Неправильная реализация связи многие-ко-многим
В данном случае получится, что пользователь может отметить сколько угодно комментариев, а вот один комментарий может быть отмечен только одним пользователем, так как поле like_user_id предназначено только для одного комментария.
Для того, чтобы получилась связь многие-ко-многим, то есть чтобы многие пользователи могли отмечать многие комментарии, необходимо добавить в БД еще одну таблицу. Назовем таблицу user_likes, определим в ней два поля: user_id – поле для хранения первичных ключей из таблицы user и comments_id - поле, для хранения первичных ключей из таблицы comments, как это видно на рисунке 159:
|
|
|
Рисунок 159 - Правильная реализация связи многие-ко-многим
Разбив связь многие-ко-многим на две связи один-ко многим, мы добились нужного нам эффекта. В данном случае даже поле like из таблицы comments стало избыточным, так как мы можем узнать количество отметок «мне нравится» для одного комментария, подсчитав, сколько в таблице user_likes записей, в которых поле comments_id содержит значение, соответствующее значению первичного ключа данного комментария.
Связь один-ко-одному встречается крайне редко, так как в большинстве случаев, если данные однозначно соответствуют друг другу, их проще хранить в одной таблице. Но иногда бывают ситуации, когда логически все же следует разделить данные на две таблицы.
В нашей примере мы уже добавили возможность отмечать понравившиеся комментарии, предусмотрим теперь функцию запрета оставлять комментарии тем пользователям, которые нарушают правила нашего сайта, например, используют нецензурную лексику. Такой запрет называется «Бан». Здесь, очевидно, должна быть связь один-к-одному, поскольку один пользователь одномоментно может иметь только один «бан». Создадим таблицу ban, а в ней два поля:
· user_id - поле, для хранения первичных ключей из таблицы user;
· duration – поле, где будет храниться продолжительность блокировки.
В данном случае нет необходимости в искусственно созданном первичном ключе для таблицы ban, так как записи в ней можно однозначно отличить по полю user_id. Дублирования строк не возникнет, потому что по истечении срока блокировки строки из таблицы ban будут удаляться.
Рисунок 160 - Пример связи один-к-одному
Работа с базами данных осуществляется с помощью визуального интерфейса (в нашем случае – это phpMyAdmin), либо с помощью языка запросов SQL.
SQL (Structured Query Language – «структурированный язык запросов») – стандартный язык для работы с реляционными базами данных.
|
|
|
Возможные операции с данными при помощи СУБД MySQL:
· создание баз данных;
· удаление баз данных;
· создание таблиц;
· удаление таблиц;
· добавление записей в таблицы;
· удаление записей из таблиц;
· обновление содержимого некоторых полей в одной либо нескольких записях в таблицах БД;
· поиск одной или нескольких записей, удовлетворяющих заданному условию.
|
|
|
