Работа с "гипернормализованными" данными
Мой работодатель, небольшая канцелярская компания, меняет поставщиков, и я просматриваю их электронный контент, чтобы найти надежную схему базы данных; наша предыдущая схема была в значительной степени просто скомпонована без какой-либо мысли, и это в значительной степени привело к невыносимой модели данных с поврежденной, противоречивой информацией.
Данные нового поставщика намного лучше, чем данные старого, но их данные я бы назвал гипернормализованными. Например, их структура категории продуктов имеет 5 уровней: главный отдел, отдел, класс, подкласс, блок продукта. Кроме того, содержимое блока продукта содержит подробное описание, поисковые термины и названия изображений для продуктов (идея состоит в том, что блок продукта содержит продукт и все варианты - например, конкретная ручка может быть выполнена черными, синими или красными чернилами; все эти Предметы, по сути, одно и то же, поэтому они относятся к одному блоку товара). В данных, которые мне дали, это выражается в виде таблицы продуктов (я говорю "таблица", но это простой файл с данными), содержащей ссылку на уникальный идентификатор блока продукта.
Я пытаюсь придумать надежную схему для размещения данных, которые мне предоставлены, поскольку мне нужно будет загрузить их относительно скоро, и данные, которые они мне дали, не соответствуют типу данных, которые они обеспечить демонстрацию на их сайте образца ( http://www.iteminfo.com/). В любом случае, я не собираюсь повторно использовать их структуру представления, так что это спорный вопрос, но я просматривал сайт, чтобы получить некоторые идеи о том, как структурировать вещи.
В чем я не уверен, так это в том, хранить ли данные в этом формате или нет, или, например, объединить Master/Department/Class/Subclass в одну таблицу "Категории", используя отношение со ссылками на себя, и связать их с блок продукта (блок продукта должен храниться отдельно, поскольку это не "категория" как таковая, а группа связанных продуктов для данной категории). В настоящее время таблица блоков продукта ссылается на таблицу подклассов, поэтому она будет изменена на "category_id", если я объединю их вместе.
Я, вероятно, собираюсь создать витрину электронной коммерции, используя эти данные с помощью Ruby on Rails (или это мой план, во всяком случае), поэтому я стараюсь не зацикливаться позже или иметь раздутые приложения - может быть, я я слишком много об этом думаю, но я скорее буду в безопасности, чем извиняюсь; наши предыдущие данные были настоящей неразберихой и стоили компании десятки тысяч долларов в потерянных продажах из-за непоследовательных и неточных данных. Также я собираюсь немного покончить с соглашениями Rails, убедившись, что моя база данных является надежной и применяет ограничения (я планирую делать это и на уровне приложений), так что это то, что мне нужно учитывать.
Как бы вы справились с такой ситуацией? Имейте в виду, что у меня есть данные для загрузки уже в виде плоских файлов, которые имитируют структуру таблицы (у меня есть документация, в которой говорится, какие столбцы какие и какие ссылки установлены); Я пытаюсь решить, следует ли мне сохранить их в норме, как они есть в настоящее время, или я должен искать консолидацию; Мне нужно знать, как каждый метод повлияет на то, как я программирую сайт с использованием Rails, поскольку, если я произвожу консолидацию, в одной таблице будет по существу 4 "уровня" категорий, но это определенно кажется более управляемым, чем отдельные таблицы для каждый уровень, так как кроме подкласса (который напрямую связан с блоками продуктов) они ничего не делают, кроме как показывают следующий уровень категории под ними. Я всегда теряюсь из-за "лучшего" способа обработки данных, подобных этому, - я знаю поговорку "нормализуй, пока не повредит, затем денормализуй, пока не заработает", но мне до сих пор не приходилось реализовывать это.
10 ответов
Я бы предпочел "гипернормализованный" подход, а не модель ненормальных данных. Упомянутая вами таблица собственных ссылок может уменьшить количество таблиц и в некоторых отношениях упростить жизнь, но в целом с этим типом отношений может быть сложно справиться. Иерархические запросы становятся проблемой, как и сопоставление объектной модели с этим (если вы решите пойти по этому пути).
Пара лишних объединений не повредит и сделает приложение более обслуживаемым. Если производительность не ухудшится из-за чрезмерного количества объединений, я бы предпочел оставить все как есть. В качестве дополнительного бонуса, если для какого-либо из этих уровней таблиц требуется дополнительная функциональность, вы не столкнетесь с проблемами, поскольку объединили их все в таблицу со ссылками на себя.
Я полностью не согласен с критикой самоссылочных табличных структур для иерархий родитель-потомок. Структура связанного списка делает программирование пользовательского интерфейса и бизнес-уровня более простым и более легким в обслуживании в большинстве случаев, поскольку связанные списки и деревья являются естественным способом представления этих данных на языках, на которых обычно реализуются пользовательский интерфейс и бизнес-уровни.
Критика в отношении сложности поддержания ограничений целостности данных в этих структурах совершенно обоснована, хотя простое решение состоит в том, чтобы использовать таблицу замыкания, в которой размещены более жесткие ограничения проверки. Закрытие таблицы легко поддерживается с помощью триггеров.
Компромисс - небольшая дополнительная сложность в БД (таблица замыкания и триггеры) для намного меньшей сложности в пользовательском интерфейсе и коде бизнес-уровня.
Таблицы с самоссылкой почти всегда оказываются намного хуже в запросе и работают хуже, чем нормализованные таблицы. Не делай этого. Может показаться, что вы более элегантны, но это не очень и очень плохая методика проектирования баз данных. Лично структура, которую вы описали, звучит просто отлично, для меня не гипернормализовано. Правильно нормализованная база данных (с ограничениями внешнего ключа, а также со значениями по умолчанию, триггерами (если необходимо для сложных правил) и ограничениями проверки данных) также с большей вероятностью будет иметь согласованные и точные данные. Я согласен с тем, чтобы база данных обеспечивала соблюдение правил, вероятно, это одна из причин того, что в последнем приложении были неверные данные, поскольку правила не были применены в нужном месте, и люди могли легко их обойти. Не то, чтобы приложение также не проверяло (нет смысла даже отправлять недопустимую дату, например, чтобы база данных потерпела неудачу при вставке). После того, как вы изменили дизайн, я бы потратил больше времени и усилий на разработку необходимых ограничений и выбор правильных типов данных (например, не храните даты в виде строковых данных), чем на то, чтобы сделать совершенно обычную нормализованную структуру более элегантной.
Normalization подразумевает целостность данных, то есть каждая нормальная форма уменьшает количество ситуаций, когда ваши данные противоречивы.
Как правило, denormalization имеет цель быстрее querying, но приводит к увеличению пространства, увеличению DML время, и, наконец, что не менее важно, увеличение усилий по обеспечению согласованности данных.
Каждый обычно пишет код быстрее (пишет быстрее, а не код быстрее), и код менее подвержен ошибкам, если данные normalized,
Если я правильно понимаю, вы хотите взять их отдельные таблицы и превратить их в иерархию, которая хранится в одной таблице с FK, ссылающейся на себя.
Как правило, это более гибкий подход (например, если вы хотите добавить пятый уровень), НО SQL и реляционные модели данных не склонны хорошо работать со связанными списками, подобными этому, даже с новым синтаксисом, таким как CTE MS SQL Server. Правда, CTE делают это намного лучше, хотя.
Внедрение таких вещей может быть трудным и дорогостоящим, например, продукт всегда должен быть на четвертом уровне иерархии и т. Д.
Если вы решите сделать это таким образом, то определенно проверьте SQL Джо Селко для Smarties, который, как мне кажется, имеет один или два раздела о моделировании и работе с иерархиями в SQL или, что еще лучше, но получите его книгу, посвященную этой теме ( Джо Деревья и иерархии Celko в SQL для умников).
Я хотел бы привести его как можно ближе к их модели (и, если это вообще возможно, я бы получил файлы, которые соответствуют их схеме - не сплющенную версию). Если вы перенесете данные непосредственно в свою модель, что произойдет, если отправленные ими данные начнут нарушать допущения при преобразовании в модель вашего внутреннего приложения?
Лучше ввести свои данные, выполнить проверку работоспособности и убедиться, что предположения не нарушены. Затем, если у вас есть модель для конкретного приложения, преобразуйте ее в модель для оптимального использования вашим приложением.
Не денормализуй. Попытка получить хороший дизайн схемы с помощью денормализации - это все равно, что попытаться добраться до Сан-Франциско, уезжая из Нью-Йорка. Это не говорит вам, куда идти.
В вашей ситуации вы хотите выяснить, что бы хотела нормализованная схема. Вы можете основывать это в основном на исходной схеме, но вам необходимо узнать, каковы функциональные зависимости (FD) в данных. Ни исходная схема, ни распрямленные файлы не гарантированно покажут вам все FD.
Once you know what a normalized schema would look like, you now need to figure out how to design a schema that meets your needs. It that schema is somewhat less than fully normalized, so be it. But be prepared for difficulties in programming the transformation between the data in the flattened files and the data in your desgined schema.
You said that previous schemas at your company cost millions due to inconsistency and inaccuracy. The more normalized your schema is, the more protected you are from internal inconsistency. This leaves you free to be more vigilant about inaccuracy. Consistent data that's consistently wrong can be as misleading as inconsistent data.
Каждый, кто рекомендует вам не вводить иерархию в базе данных, рассматривает только вариант наличия таблицы с собственной ссылкой. Это не единственный способ моделирования иерархии в базе данных. Вы можете использовать другой подход, который обеспечивает более простой и быстрый запрос без использования рекурсивных запросов. Допустим, у вас есть большой набор узлов (категорий) в вашей иерархии:
Set1 = (Node1 Node2 Node3...)
Любой узел в этом наборе также может быть другим набором, который содержит другие узлы или вложенные наборы:
Узел1 = (Узел2 Узел3=(Узел4 Узел5=(Узел6) Узел7))
Теперь, как мы можем смоделировать это? Давайте иметь каждый узел, чтобы иметь два атрибута, которые устанавливают границы узлов, которые он содержит:
Node = {Id: int, Min: int, Max: int}
Чтобы смоделировать нашу иерархию, мы просто назначаем эти минимальные / максимальные значения соответственно:
Node1 = {Id = 1, Min = 1, Max = 10}
Node2 = {Id = 2, Min = 2, Max = 2}
Node3 = {Id = 3, Min = 3, Max = 9}
Node4 = {Id = 4, Min = 4, Max = 4}
Node5 = {Id = 5, Min = 5, Max = 7}
Node6 = {Id = 6, Min = 6, Max = 6}
Node7 = {Id = 7, Min = 8, Max = 8}
Теперь, чтобы запросить все узлы в Set/Node5:
выберите n.* из узлов как n, узлов как s
где s.Id = 5 и s.Min
Единственной ресурсоемкой операцией будет, если вы захотите вставить новый узел или переместить какой-либо узел в иерархии, так как это повлияет на многие записи, но это нормально, так как сама иерархия меняется не очень часто.
Для меня реальный вопрос: что лучше подходит для модели?
Это как сравнивать кортеж и список.
- Кортежи имеют фиксированный размер и неоднородны - они "гипернормализованы".
- Списки имеют произвольный размер и являются однородными.
Я использую кортеж, когда мне нужен кортеж и список, когда мне нужен список; они принципиально сервер разных целей.
В этом случае, поскольку структура продукта уже хорошо определена (и я предполагаю, что она вряд ли изменится), я бы придерживался "подхода Tuple". Реальная сила / использование списка (или рекурсивного шаблона таблицы) - это когда вам нужно расширить его на произвольную глубину, например, для спецификации или дерева генеалогии.
Я использую оба подхода в некоторых из моей базы данных в зависимости от необходимости. Однако существует также "скрытая стоимость" рекурсивного шаблона, заключающаяся в том, что не все ORM (не уверенны в AR) хорошо его поддерживают. Многие современные БД поддерживают "сквозные соединения" (Oracle), идентификаторы иерархии (SQL Server) или другие рекурсивные шаблоны. Другой подход заключается в использовании иерархии на основе множеств (которая обычно опирается на триггеры / обслуживание). В любом случае, если используемый ORM плохо поддерживает рекурсивные запросы, то могут возникнуть дополнительные "затраты" на непосредственное использование функций для БД - либо с точки зрения генерации запросов / представлений вручную, либо с точки зрения управления, таких как триггеры. Если вы не используете классный ORM или просто используете логический разделитель, такой как iBatis, эта проблема может даже не применяться.
Что касается производительности, то на новой СУБД Oracle или SQL Server (и, вероятно, на других) она должна быть очень сопоставимой, так что это будет наименьшим из моих беспокойств: но посмотрите на решения, доступные для вашей RDBMS и проблем переносимости.
Ваша витрина (или что бы вы ни строили, не совсем понятно) всегда будет использовать данные этого поставщика? Вы могли бы когда-нибудь поменять поставщиков или добавить других разных поставщиков?
если это так, разработайте общую схему, соответствующую вашим потребностям, и сопоставьте с ней данные поставщика. Лично я предпочел бы перенести (невероятно незначительную) "боль" самореферентной таблицы категорий (иерархической), чем поддерживать четыре (очевидно, наполовину бесполезных) уровня вариантов категорий, а затем в следующем году узнаю, что они добавили 5-й, или представил линейку продуктов только с тремя...