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

1) Есть таблица customers: [CustomerID, Name, Address, ...]. И теперь нам нужно сделать опцию, чтобы клиентам можно было делать рассылку изменений в их заказах. Т.е. для каждого клиента можно будет выбрать — делать ему ежедневную рассылку или нет. К тому же надо сохранять timestamp последней рассылки (чтобы знать какие изменения в заказах включать в следующую рассылку). Основных варианта решения два:
а) добавить в таблицу customers поле SendUpdates (boolean) и поле LastUpdatesSentTS (timestamp).
б) создать отдельную таблицу customer_updates_notifications: [CustomerID, LastUpdatesSentTS]. Необходимости в поле SendUpdates в этом случае не будет, т.к. отправлять ли уведомления клиенту будет определяться наличием соответствующей строки в таблице customer_updates_notifications.

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

Какой вариант выбрали бы вы и почему? Зависит ли ваше решение от того сколько у нас клиентов — 100 или 10000 или 100000? Зависит ли ваше решение от того у какого процента клиентов будет выбрана опция рассылки (<10%, 50%, 90%)? Дополнительно, условимся что только очень малой части запросов на выборку будет нужна данная информация).

Кстати, стали бы вы включать свойство SendUpdates & LastUpdatesSentTS в класс Customer в модели предметной области в коде? Или работали бы с этим атрибутом просто напрямую использую DAL-методы (т.е. не сохраняя их в свойствах сущности Customer)?

2) Есть таблица order_lines [OrderID, ManufacturerID, PartNumber, Quantity, ...]. И теперь нам нужно сделать опцию, чтобы менеджеры могли проставлять комментарий к позиции заказа. Допустим указать коментарий "запчасть не найдена в пришедщем от поставщика грузе" или "клиент попросил не превышать цену" и т.д. Но, допустим, мы подозреваем что через месяц мы решим сохранять timestamp установки комментария, а еще через полгода — возможно понадобится сохранять ID менеджера, который поставил комментарий.
В общем-то те же самые 2 варианты основные:
а) добавить в таблице order_lines колонки Comment, а в будущем еще и CommentTS и CommentManagerID.
б) создать отдельную таблицу order_line_comments: [OrderLineID, Comment]. В будущем в нее добавятся колонки CommentTS и CommentManagerID.

Какой вариант выбрали бы вы и почему? Зависит ли ваше решение от того у какого процента позиций будут указаны комментарии (1%, 10%, 50%)? Так же, дополнительно условимся, что только малой части запросов на выборку будет нужна информация о комментариях (это действительно так).

---
По первому примеру — лично у меня 50 на 50, не знаю как будет лучше. По второму примеру склоняюсь к тому что лучше вынести эти атрибуты в отдельную таблицу (т.е. вертикальное партицирование со всеми вытекающими плюсами и минусами).
А собственно какие плюсы и минусы в каждом варианте? Давайте подумаем:
В случае добавления колонок в ту же таблицу мы получаем относительную простоту (Т.к. не нужно заморачиваться с отдельной таблицей. И опустим случай когда у нас в таблице 100 миллионов строк и добавление колонки в такую таблицу может занять часы.). Не будет кучи маленьких таблиц с доп. атрибутами и не будет кучи джоинов. Но кстати не нарушает ли это (я про первый вариант с одной таблицей говорю) нормализацию? Вроде как нет, но может я ошибаюсь? К тому же в случае больших таблиц может быть неэффективная трата дискового пространства (например будет 90% строк со значением NULL в такой колонке).
Во втором варианте мы как бы отделяем "менее основные" атрибуты в отдельную таблицу, иногда отделяем группу атрибутов. Но этот вариант чуть более заморочный, в будущем потребует джоинов, да и еще меня смущает — не появится ли через пару лет куча мелких таблиц для таких доп. атрибутов? С соответствующей кучей джоинов.. Плюсы от вертикального партицирования известные: меньше места на диске, быстрее операции чтения и сохранения данных (в одной физической странице (блоке) будет умещаться больше строк, т.е. за раз сможем прочитать или сохранить больше строк), некоторые запросы могут начать выполняться быстрее (да да, несмотря на джоины, подробнее например здесь ), ну и можно разнести индексы по разным таблицам — это тоже может быть плюсом.
Вот такие вот соображения.

Я хоть по базам данных много не читал (полкнижки по дизайну БД, полкниги по SQL и полкниги по транзакциям и concurrency control ), но все равно нигде никогда не видел, где бы объяснялось как правильно размышлять и поступать в таких ситуациях. Если кто знает хороший материал по конкретной данной проблеме — буду очень рад ссылке.

В общем жду ваших мнений, особенно интересует мнение опытных специалистов по БД. Заранее спасибо.

softwarerЕсли не считать этого, то я бы сказал, надо просто решить, добавляем ли мы атрибут-два или добавляем новую сущность. Критерии сущности кроме интуитивных - вероятное нарастание списка атрибутов, вероятное использование для других похожих задач, запросы именно к этим данным без интереса к другим. Например, "рассылка" - очень похожа на отдельную сущность. Потому как завтра у клиента появятся другие рассылки, а послезавтра появится рассылка для сотрудников.
По поводу рассылки для сотрудников - она (если вдруг появится) же будет отдельную таблицу использовать ведь.. Это я к тому что как это влияет на принятие решения?

softwarerЕсли таки атрибут - остаётся решить, весомее ли преимущества или недостатки вертикального партиционирования. По умолчанию имхо второе.
А какие недостатки вертикального партицирования (кроме 1) необходимости доп. джоинов в некоторых запросах 2) размножения маленьких табличек) вы видите?


А в целом очень понравился ваш ответ. Четкий, логичный, привели примеры критериев определения отдельной сущности. Спасибо.

softwarerНе факт. Это уже как сдизайнить.
Придется отказаться от FK. Имхо это не есть красиво. Или вы как бы такое сдизайнили?

softwarerГлавный имхо - неинтуитивность структуры БД. Это вещь, про которую нужно помнить, которая не поддерживается визуальными инструментами итп. Пользоваться БД в целом становится сложнее.
Мне кажется что при соответствующем именовании таблиц и наличии FK все достаточно неплохо.

mcureenabТолько не путайте сущности и таблицы.
Я не путаю.
Вообще я, по-моему, привел достаточно конкретные примеры и контекст, а вы мне зачем-то про EAV..

kdv,

Спасибо за ваш ответ. Я кстати пока его читал вспомнил, что обновление например LastStatusSentTS приведет к автоматическому обновлению колонки TS (TIMESTAMP ... ON UPDATE CURRENT_TIMESTAMP), что не всегда может быть желательным. Это тоже надо учитывать. И еще я тут вспомнил, что надо для рассылки сделать возможность указания отдельного email'а у клиента, а так же указания типа рассылки (полная или сокращенная) - так что уже образовывается группа связанных атрибутов и это дополнительный аргумент в пользу выноса в отдельную таблицу.