Рекомендации по включению/отключению/удалению строк базы данных и ее ссылок?

Каковы наилучшие методы обработки удаления или включения/удаления строк и ссылочных таблиц?

Например, предположим, что у меня очень простое приложение для форума.

У меня есть таблица users, которая содержит мои учетные записи webapp и threads, которые содержат потоки пользователей, и таблица comments, которая содержит комментарии пользователей к потокам.

Теперь скажите, что в момент регистрации я хочу проверить электронную почту пользователя, прежде чем активировать свою учетную запись. Что это лучший способ сделать это? Каковы лучшие практики? Может быть, через представление, которое возвращает только строки с полем is_active=true? Использование 2 разделенных таблиц, таких как pre_users (содержит пользователей, которые еще предстоит проверить) и users (проверенные)?

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

Кроме того, удалите поток. Предположим, что при удалении потока я не хочу, чтобы поток был фактически удален, потому что я не хочу, чтобы пользователи, которые оставляли комментарии, внезапно не видели своих комментариев. Как с этим справиться?

Каковы наилучшие методы для таких проблем?

Ответ 1

Для таблиц, в которых активное состояние не влияет на уникальность (например, пользователи идентифицируются по имени пользователя или адресу электронной почты, нет возможности иметь активные и неактивные версии одной и той же вещи). Я использую поле nullable datetime для представляют собой статус. Например, для вашей таблицы users у меня будет столбец verified_at, который первоначально имеет значение null и текущую дату/время, когда пользователь проверяет свою учетную запись. То же самое можно сделать для приостановки учетной записи пользователя. Если пользователь реактивирует свою учетную запись, мы просто установим для поля susp_at значение null.

Если вам нужно больше значений статуса, чем просто да/нет, я бы использовал отдельные поля для значения статуса и даты/времени.

Если вы хотите отслеживать историю обновлений (например, активации/приостановки), лучше сделать это в отдельной таблице. В этом случае вы можете ссылаться на текущую запись активации из таблицы users, которая будет работать более эффективно с индексами таблицы, чем запрос в столбцах состояния.

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

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

Наконец, статусы данных являются шагом в направлении моделирования временных данных. Я рекомендую прочитать эту тему.

Ответ 2

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

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

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

По моему опыту, самый большой вопрос, на который вам нужно ответить, - "заботиться о требованиях к бизнесу со временем"?

Если ответ "нет", вы можете использовать любые флаги состояния. Например, в таблице пользователя у вас может быть столбец состояния с "зарегистрированным/подтвержденным/деактивированным/удаленным". Это относительно просто для кода, но вы не можете легко ответить на такие вопросы, как "на какой день был отключен этот активированный пользователь?" Или "какой был статус пользователя, который отправил этот комментарий, когда он разместил его".

Если требования позаботятся о времени, мне нравится модель - добавить "valid_from" и "valid_until" в строки, которые должны понимать время. "Текущая" строка имеет столбец null valid_unit. Это позволяет вам понять состояние ваших данных в любой момент времени, но это делает ваши запросы более сложными, особенно если вы присоединяетесь к нескольким таблицам.

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

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

Добавив "valid_from" и "valid_until" в таблицу "posts", вы можете включить управление версиями - возможно, вы хотите показать, что комментарий был опубликован в старой версии сообщения?

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

Ответ 3

Шаблон состояния в моделировании данных
В этом типе условий мы можем использовать шаблон, который я назвал Шаблон состояния в моделировании данных.

Предположим, что у нас есть объект со многими Статус. Например, объект user, который имеет pre-registered, normal user, deleted user, suspended user и т.д.

В другом примере пользователь Post (например, сообщения Stackoverflow) имеет много Status, таких как normal, deleted by user, deleted by moderator, duplicated, closed и т.д.

Предположим, что мы хотим моделировать статусы для объекта user. В этом случае мы можем использовать объект для хранения всех типов состояний (например, pre-registered, normal user, deleted user, suspended user). Мы можем назвать его User_Status_Types и поместить в него все типы статуса пользователя.

Таким образом, другой объект должен содержать любые пользовательские статусы. Позвольте мне назвать его User_Statuses. Он имеет F.Ks user и User_Status_Types.

С помощью этого шаблона мы можем сохранить все статусы пользователей.

Чтобы улучшить шаблон, мы можем иметь F.K of User_Statuses в user, который показывает конечный статус пользователя. (обратите внимание, что это не ловушка зависания на велосипеде)

Вопрос 1: Если объект имеет два разных типа статусов?
В этом случае следует использовать для времен этого шаблона.

Вопрос 2. Если статус дочернего объекта (любой объект, который имеет F.K от этого объекта), зависит от статуса объекта?
Например, если мы хотим НЕ отображать комментарии удаленных пользователей. В этом случае у нас есть два варианта:

Вариант 1. Мы можем написать триггеры при изменении статуса пользователя. Если статус пользователя был изменен на удаленный, то наш триггер также изменит статус всех комментариев пользователей.

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

Вопрос 3. Если последовательность изменений состояния важна, и мы хотим их моделировать в нашей модели данных.
В этом случае мы можем добавить новый объект с именем User_Status_Types_Sequence, который имеет 2 F.Ks из User_Status_Types в качестве источника и цели. Значение этого статуса источника может быть изменено на целевой статус. Таким образом, мы можем выбрать последовательность действий из базы данных, а затем выполнить ее в нашей базе данных.