Как правильно спроектировать БД MySQL?

Question

[Graid]

Впервые занялся проектированием БД.
1. Просьба указать на явные ошибки и огрехи.



2. Так же интересует как лучше организовать комментарии. Сделать отдельно таблицы комментариев для каждого из типов публикации(news, post, special, special_page), или к каждой публикации добавлять уникальный идентификатор комментариев (по примеру сервисов типа disqus, только свое).

По ссылке zip-архив с изображением в большем разрешении, sql файл и файл проекта для MySQL workbench.

Answer 1

[Wott]

не понятно зачем иметь 4 таблицы для похожих сущностей — post,news,special, special_page. Если ихъ обьеденить, то сразу вопрос с комментами отвалится.

имхо очень плохая практика иметь безликие id, name — лучше иметь blog_id, post_name и сразу все понятно без разглядывания схем или ключей

Comments

[Graid]

Спасибо, насчет объединения подумаю.
А про информативность не совсем понял, зачем дублировать имя таблицы в названиях их колонок?

[EndUser]

Потому, что программисту понятнее будет ;-)
«where post_user_ref = user_id»

[Graid]

Ну не знаю, я чаще встречаю записи такого вида.

$user->login;

и

user.login

[Wott]

фишка не в том что бы иметь повторения, а в том что в user.user_id и в post.user_id — это самодокументирование
одинаковые сущности и называются везде одинаково, а разные — по разному

[balloon]

Имхо, и так понятно что user.id — это ид пользователя, а post.user_id это внешний ключ на user.id. Вы же в объектах не дублируете имя класса в свойствах и методах:

class User {
    public $id; // а не $userId
    public $name; // а не $userName
    public getEmail(); // а не getUserDetails();
}

В базе должны быть теже правила. Если хотите уточнить к чему относится поле, то просто достаточно указать полное имя таблицы blog.name, user.email. Если имя таблицы не указано, значит оно должно быть очевидно в контексте запроса (так же как использование $this в методах класса)

[Wott]

Когда запросы сложные то активно используются алиасы для таблиц и зачастую они короткие типа u1, pm2, хотя с любыми таблицами линковка a1.some_id=b2.some_id понятнее и менее ошибочна чем some1.id=b2.some_id

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

А в обьектах у меня тоже самое userId ( или user_id если не верблюд :) ), отчасти потому что обьекты формируются по записи в базе, но что важнее, имя поля должно однозначно говорить что там есть. Так, к стати, проще в случае усложнения сущностей — я могу просто скинуть все поля user в post и знаю что все потом в базу ляжет как надо по именам.

Безликие id, i и n я допускаю только там где они действительно не важны. В базе это только неиспользуемое поле автоинкремента id или timestamp, используемый исключительно в триггерах.

Answer 2

[rakot]

Обратите внимание на ваши INT(6),INT(3),INT(1), думаю что это не совсем то, что вы хотели(попробуйте в INT(1) записать число 1000000).

Вы везде используете VARCHAR, хотя для password и salt можно(и нужно) использовать CHAR.

Comments

[Graid]

Спасибо! Про CHAR совсем вылетело из головы, а для INT всегда думал что указывается длина значения.

[WebSpider]

Для пароля (там же хранится хэш, верно?) логичнее использовать BINARY, да и для соли возможно тоже

Answer 3

[dbmaster]

Я в таблицах связках ( например post/tag ) делаю ключ одним дополнительным полем c autoincrement.

Comments

[Graid]

А для чего?

[dbmaster]

Например, чтобы отсдедить «последние изменения».
К каждому случаю применять правило не нужно.

[Graid]

Спасибо, подумаю над этим!

[dbmaster]

Тут важно заметить, что в MySQL ключ всегда кластерный — что важно при поиске, вставке — поэтому порядок колонок в ключе при большом объёме тоже важет.

[Graid]

Можно пруф на то о чем говорите, а то не совсем понятно?

[dbmaster]

dev.mysql.com/doc/refman/5.6/en/innodb-table-and-index.html#innodb-index-types

blogs.oracle.com/carriergrademysql/entry/mysql_cluster_basics_why_using

[Graid]

Еще раз спасибо!

Answer 4

[balloon]

Внесу и свою лепту. Я бы:
1. Отказался от сокращенного найменования полей и назвал бы некоторые поля по другому bid -> blog_id, uid -> user_id, uid_add -> created_by, uid_upd -> updated_by, time_add -> created_at, url_name -> slug
2. Объединил таблицы post, special, special_page, news (и соотв. вместо 2 таблиц news_tag & post_tag осталась бы одна) + как уже упомянули, решился бы вопрос с комментариями
3. Для статуса использовал бы ENUM вместо INT(1), а если ENUM нельзя — то хотя бы TINYINT :)
4. Если комментариев много — то использовал бы nested set либо добавил поле с путем для быстрого вывода дерева (в чем поле level не помогает вроде)

Comments

[Graid]

Спасибо за советы!
1. С именованием тут видимо все сугубо лично. Мне к примеру «slug» еще с шопскрипта не нравится.
2. Уже объединил.
3. Уже поставил TINYINT, после коммента rakot, с ENUM боюсь будет не очень удобно при добавлении статуса, думаю списки статусов тоже сделать редактируемыми в будущем.
4. Про это пока гугл покурю, не слышал о таком до этого.

[Graid]

p.s.
bid'ы и др. сделал более понятным.

Answer 5

[Николай Турнавиотов]

Как минимум мы тут видим кучу внетренних, непонятно за что отвечающих переменных.
так что пока не будет нормального тз с описанием что надо и как вы видите решение этой проблемы — Ваша диаграмма 90% пользователям ничего не скажет

Comments

[Graid]

Пытался дать таблицам и колонкам понятные имена. ТЗ сейчас нет, можно сказать как раз начал написание ТЗ с проектирования БД.

[Николай Турнавиотов]

А это как раз и плохо. сначала ТЗ. чем детальнее будет ТЗ (вопрос), тем лучге будет найти ответ (спроектировать БД, п/о)
Да, осмысленные переменные — самое правильное решение.
Но лично мне сейчас эти квадратики и буквочки ни о чем не говорят.

Answer 6

[egorinsk]

Слишком много таблиц

Answer 7

[Сергей Еремин]

Без ТЗ сложно сказать. Мне кажется что функционал вполне стандартный и можно обойтись таблицами ПОЛЬЗОВАТЕЛЬ и ПУБЛИКАЦИЯ. Если в публикации есть ссылка на родительскую публикацию то это комментарий и дальше можно их этого строить хоть ленты хоть деревья. Еще, конечно, нужно таблицу ТЭГОВ иметь, но пока не ясно что из нее надо будет строить )может связанные массивы релевантности) то про ее структуру сложно сказать сто-то определенное. Для чего нужна таблица СПЕШАЛ (и связанные таблицы) вообще не понятно.

P.S. Кстати, а с помощью чего нарисована схема?

Comments

[Graid]

«Спешиал» и сопутствующие нужны для описание некоторых тегов + дочерние страницы, правда в данный момент решил объединить станицы добавив поле тип публикации. Нарисовано в MySQL Workbench.

Answer 8

[afiskon]

Relevant: eax.me/database-design