Разработка базы данных социальных веб-приложений: как я могу улучшить эту схему?

Фон

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

База данных - это MySQL, а приложение написано на PHP. Я еще не уверен, будем ли мы использовать библиотеку ORM или писать SQL-запросы с нуля в приложении. Помимо веб-приложения, сервер поиска Solr и, возможно, клиент обмена сообщениями будут взаимодействовать с базой данных.

Текущие потребности

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

Создание и изменение параметров профиля и настроек учетной записи
Опубликовать, пометить и классифицировать свою запись
Читать, комментировать и "любимые" сообщения других пользователей.
"Следуйте" другим пользователям, чтобы получать уведомления о своей деятельности.
Поиск и просмотр содержимого и получение рекомендуемых сообщений/пользователей (хотя мы будем использовать поисковый сервер Solr для индексирования данных БД и запуска этих запросов)

Схема

Вот что я придумал в MySQL Workbench для исходного сайта. Я все еще немного расплывчатый в некоторых реляционных вещах базы данных, так что делайте это легко.

Вопросы

В общем, есть ли что-то, что я делаю неправильно или могу улучшить?
Есть ли причина, по которой я не должен комбинировать таблицу ExternalAccounts в таблице UserProfiles?
Есть ли причина, почему я не должен комбинировать таблицу PostStats в таблице Posts?
Должен ли я расширить дизайн, включив функции, которые мы делаем во второй версии, только для того, чтобы гарантировать, что исходная схема может его поддерживать?
Есть ли что-нибудь, что я могу сделать, чтобы оптимизировать дизайн БД для индексации/производительности Solr/независимо?
Должен ли я использовать более естественные первичные ключи, такие как Username вместо UserID, или код zip/area вместо суррогатного LocationID в таблице Locations?

Спасибо за помощь!

Ответ 1

В общем, есть ли что-то, что я делаю неправильно или могу улучшить?

В целом, я не вижу больших недостатков в вашей текущей настройке или схеме.

Что я задумал, так это ваш раздел на 3 пользовательских * таблиц. Я получаю то, что вы хотите, чтобы ваше намерение было (разные вещи, связанные с пользователем), но я не знаю, пойду ли я с тем же самым делом. Если вы планируете отображать только данные из таблицы User на сайте, это прекрасно, так как другая информация не требуется несколько раз на одной странице, но если пользователям нужно использовать свое настоящее имя и отображать их настоящее имя (например, Джон Доу вместо doe55), чем это замедлит ситуацию, когда данные станут больше, так как вам могут потребоваться объединения. Наличие разделения Preferences похоже на личный выбор. У меня нет аргументов в пользу и против него.

В ваших таблицах "многие-ко-многим" не потребуется дополнительный PK (например, PostFavoriteID). Комбинированного первичного значения как PostID, так и UserID будет достаточно, так как PostFavoriteID никогда больше не используется. Это относится ко всем таблицам соединений

Есть ли какая-либо причина, по которой я не должен комбинировать ExternalAccounts таблицу в таблицу UserProfiles?

Как и в предыдущем. ответ, я не вижу недостатка или недостатка. Я могу разместить оба в одной таблице, так как значения NULL (или, может быть, лучше -1) меня не беспокоят.

Есть ли причина, по которой я не должен комбинировать таблицу PostStats в таблицу Posts?

Я бы поместил их в ту же таблицу, используя триггер для обработки приращения таблицы ViewCount

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

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

Я могу что-то сделать, чтобы оптимизировать дизайн БД для Solr индексация/производительность/что?

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

Должен ли я использовать более естественные первичные ключи, такие как Имя пользователя вместо UserID или код zip/area вместо суррогатное местоположениеID в местах таблица?

Здесь есть много тем, обсуждающих это. Лично мне больше нравится суррогатный ключ (или другой уникальный номер ключа, если он доступен), так как он делает запросы более легкими и быстрыми, поскольку поиск в int упрощается. Если вы разрешаете изменение имени пользователя/электронной почты/всего-своего-ПК, то есть требуются массовые обновления. С помощью суррогатного ключа вам не нужно беспокоиться.

Я также хотел бы добавить такие вещи, как created_at, last_accessed в (лучше всего сделать с помощью триггеров или процедур IMO), чтобы иметь уже имеющуюся статистику. Это может реально дать вам ценную статистику.

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

Ответ 2

Я не понимаю, что происходит с вашими таблицами User * - они настроены так, как будто они 1:1, но диаграмма отображает символ "один ко многим" (символ ворона).

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

Например:

ExternalAccounts
    UserId int,
    AccountType varchar(45),  
    AccountIdentifier varchar(45)

позволит вам хранить учетные записи LinkedIn, Google и т.д. в той же структуре. Аналогичным образом, дополнительные типы уведомлений могут быть легко добавлены с использованием такой структуры, как:

UserSettings
    UserId int,  
    NotificationType varchar(45),  
    NotificationFlag ENUM('on','off')

HTH