Как создать базу данных для пользовательских полей?

Ответ 1

Если производительность является основной задачей, я бы пошел С# 6... таблицей на UDF (действительно, это вариант №2). Этот ответ специально адаптирован к этой ситуации и описанию описанного описания распределения данных и доступа.

Плюсы:

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

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

• Вы можете использовать имена таблиц и столбцов, которые отражают фактические данные.

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

• Если вам когда-либо понадобится использовать иностранные ключи, встроенные декларативные справочный   целостность редко выполняется   триггерный или прикладной уровень   принудительное принуждение.

Минусы:

• Это может создать множество таблиц. Обеспечение разделения схем и/или соглашение об именовании облегчит это.

• Существует больше кода приложения необходимо использовать определение UDF и управления. Я ожидаю, что это требуется меньше кода, чем для оригинальные варианты 1, 3 и 4.

Другие соображения:

• Если есть что-нибудь о характер данных, которые смысл для группировки UDF, это следует поощрять. Сюда, эти элементы данных могут быть объединены в одну таблицу. Например, скажем, у вас есть UDF для цвета, размера и стоимости. Тенденция в что большинство случаев этого данные выглядят как

   'red', 'large', 45.03 
  

  а не

   NULL, 'medium', NULL
  

  В таком случае вы не понесете заметное ограничение скорости объединение трех столбцов в 1 стол потому что несколько значений будут NULL и вы избегаете делать еще 2 таблицы, что на 2 меньше требуется, если вам нужно получить доступ ко всем 3 столбцам.

• Если вы нажмете на стену с UDF, который сильно населен и часто используется, то это должно быть рассматривается для включения в мастер-таблица.

• Логическая конструкция стола может привести вас к определенный момент, но когда запись подсчеты становятся действительно массовыми, вы также следует начать смотреть на таблицу параметры разбивки предоставляются вашей РСУБД по выбору.

Ответ 2

У меня написано об этой проблеме много. Наиболее распространенным решением является антипаттерн Entity-Attribute-Value, который похож на то, что вы описываете в своем варианте № 3. Избегайте этого дизайна, как чума.

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

Вы можете прочитать интересную статью от 2009 года об этом решении: http://backchannel.org/blog/friendfeed-schemaless-mysql

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

Ответ 3

Я, скорее всего, создам таблицу следующей структуры:

• имя varchar
• varchar Тип
• decimal NumberValue
• varchar StringValue
• date DateValue

Точные типы курсов зависят от ваших потребностей (и, конечно, от используемых вами dbms). Вы также можете использовать поле NumberValue (десятичное) для int и booleans. Возможно, вам понадобятся и другие типы.

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

Возможно, вы захотите получить некоторую информацию о метаданных. Таким образом, вы получаете следующее:

Таблица UdfMetaData​​p >

• int id
• имя varchar
• varchar Тип

Таблица MasterUdfValues ​​

• int Master_FK
• int MetaData_FK
• decimal NumberValue
• varchar StringValue
• date DateValue

Что бы вы ни делали, я бы не изменил динамическую структуру таблицы. Это кошмар для обслуживания. Я бы также не использовал структуры XML, они слишком медленны.

Ответ 4

Это похоже на проблему, которая может быть лучше решена нереляционным решением, таким как MongoDB или CouchDB.

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

Я согласен с Биллом Карвином, модель EAV не подходит для вас. Использование пар имя-значение в реляционной системе не является внутренне плохим, но работает только хорошо, когда пара имя-значение делает полный набор информации. При его использовании вы динамически восстанавливаете таблицу во время выполнения, всевозможные вещи начинают усложняться. Querying становится упражнением в обслуживании с опорой или заставляет вас подталкивать реконструкцию кортежа в слой объекта.

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

Вы теряете способность эффективно управлять своей схемой. Является ли 100-символьный varchar правильным типом для поля "значение"? 200-символов? Должно ли это быть nvarchar? Это может быть сложный компромисс, и он заканчивается тем, что вам приходится устанавливать искусственные ограничения на динамическую природу вашего набора. Что-то вроде "вы можете иметь только x пользовательских полей, и каждый может содержать только y символов.

С помощью ориентированного на документ решения, такого как MongoDB или CouchDB, вы сохраняете все атрибуты, связанные с пользователем, в одном кортеже. Поскольку объединения не являются проблемой, жизнь счастлива, так как ни одна из этих двух не справляется с объединениями, несмотря на шумиху. Ваши пользователи могут определить столько атрибутов, сколько им нужно (или вы разрешите) по длине, которым трудно справиться, пока вы не достигнете около 4 МБ.

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

Ответ 5

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

Вариант 1

IMO этот подход дает вам схему без каких-либо знаний о том, что означает схема, которая является рецептом катастрофы и кошмаром для разработчиков отчетов. I.e., вы должны иметь метаданные, чтобы знать, какой столбец хранит данные. Если эти метаданные перепутались, у них есть потенциал для шланга ваших данных. Кроме того, это позволяет легко помещать неверные данные в неправильный столбец. ( "Что? String1 содержит имя монастырей? Я думал, что это любимые лекарства Chalie Sheen".)

Вариант 3,4,5

IMO, требования 2, 3 и 4 устраняют любые изменения EAV. Если вам нужно запрашивать, сортировать или выполнять вычисления по этим данным, то EAV - это мечта Cthulhu, ваша команда разработчиков и кошмар DBA. EAV создаст узкое место с точки зрения производительности и не даст вам целостности данных, необходимой для быстрого доступа к необходимой вам информации. Запросы быстро превратятся в кросс-таблицы Gordian.

Вариант 2,6

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

Если клиент хочет получить лучшую производительность для данных, которые они хотят сохранить, им необходимо пройти процесс работы с разработчиком, чтобы понять их потребности, чтобы он был сохранен как можно эффективнее. Он все равно может храниться в таблице отдельно от остальных таблиц с кодом, который динамически создает форму на основе схемы таблицы. Если у вас есть база данных, которая позволяет расширенные свойства в столбцах, вы можете даже использовать их, чтобы помочь строителю форм использовать красивые метки, подсказки и т.д., Так что все, что было необходимо, - это добавить схему. В любом случае, чтобы эффективно создавать и запускать отчеты, данные должны храниться должным образом. Если данные, о которых идет речь, будут иметь множество нулей, некоторые базы данных могут хранить этот тип информации. Например, SQL Server 2008 имеет функцию Sparse Columns специально для данных с множеством нулей.

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

Разреженные столбцы

Ответ 6

4. Создайте несколько таблиц UDF, по одному на тип данных. Таким образом, у нас были бы таблицы для UDFStrings, UDFDates и т.д. Вероятно, он будет делать то же самое, что и # 2, и автоматически генерировать View в любое время, когда добавляется новое поле.

Согласно моему исследованию, несколько таблиц основаны на типе данных, которые не помогут вам в производительности. Особенно, если у вас есть объемные данные, например, 20K или 25K записей с 50+ UDF. Производительность была наихудшей.

Вы должны пойти с отдельной таблицей с несколькими столбцами, например:

varchar Name
varchar Type
decimal NumberValue
varchar StringValue
date DateValue

Ответ 7

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

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

ИЗМЕНИТЬ

другой вариант, доступный сейчас, но не существовавший (или, по крайней мере, не зрелый), когда вопрос был первоначально задан, заключается в использовании json-полей в БД.

многие реляционные БД теперь поддерживают json-поля (которые могут включать динамический список подполей) и позволяют запрашивать их

postgress

mysql

Ответ 8

У меня был опыт или 1, 3 и 4, и все они заканчиваются либо беспорядочными, что неясно, какие данные или действительно сложны с какой-то мягкой категоризацией, чтобы разбить данные на динамические типы записей.

У меня возникнет соблазн попробовать XML, вы должны будете обеспечить применение схем к содержимому xml для проверки ввода данных и т.д., что поможет хранить разностные наборы данных UDF. В новых версиях SQL-сервера вы можете индексировать поля XML, что должно помочь в производительности. (см. http://blogs.technet.com/b/josebda/archive/2009/03/23/sql-server-2008-xml-indexing.aspx), например

Ответ 9

Если вы используете SQL Server, не забывайте тип sqlvariant. Это довольно быстро и должно выполнять вашу работу. Другие базы данных могут иметь что-то подобное.

Типы данных XML не так хороши по соображениям производительности. Если вы делаете расчеты на сервере, вам постоянно приходится их десериализовать.

Вариант 1 звучит плохо и выглядит грубым, но с точки зрения производительности может быть вашим лучшим выбором. Я создал таблицы с столбцами Field00-Field99 раньше, потому что вы просто не можете победить в производительности. Возможно, вам придется также учитывать вашу производительность INSERT, и в этом случае это тоже та, для которой нужно идти. Вы всегда можете создавать представления в этой таблице, если хотите, чтобы она выглядела аккуратно!

Ответ 10

SharePoint использует параметр 1 и имеет разумную производительность.

Ответ 11

Я успешно справился с этим в прошлом, используя ни один из этих параметров (опция 6?:)).

Я создаю модель для пользователей, с которыми можно играть (хранить как xml и выставлять с помощью специального инструмента моделирования), так и из созданных моделей таблиц и представлений, чтобы присоединиться к базовым таблицам с пользовательскими таблицами данных. Таким образом, каждый тип будет иметь базовую таблицу с основными данными и пользовательскую таблицу с пользовательскими полями.

Возьмите документ в качестве примера: типичными полями будут имя, тип, дата, автор и т.д. Это будет отображаться в основной таблице. Затем пользователи будут определять свои собственные типы документов со своими собственными полями, такими как contract_end_date, renewal_clause, blah blah blah. Для этого определяемого пользователем документа будет таблица основных документов, таблица xcontract, соединенная с общим первичным ключом (поэтому первичный ключ xcontracts также является чужой для первичного ключа основной таблицы). Затем я создам представление, чтобы обернуть эти две таблицы. Производительность при запросе была быстрой. дополнительные бизнес-правила также могут быть встроены в представления. Это работало очень хорошо для меня.

Ответ 12

Наша база данных поддерживает приложение SaaS (программное обеспечение службы поддержки), в котором пользователи имеют более 7 тыс. "Пользовательских полей". Мы используем комбинированный подход:

1. (EntityID, FieldID, Value) таблица для поиска данных
2. поле JSON в таблице entities, которое содержит все значения сущностей, используемые для отображения данных. (таким образом, вам не нужен миллион JOIN, чтобы получить значения значений).

Далее можно разделить # 1, чтобы получить "таблицу на тип данных", как предлагает этот ответ, таким образом вы даже можете индексировать свои пользовательские функции.

Постскриптум Пару слов в защиту подхода "Entity-Attribute-Value", который все продолжают ругать. Мы использовали # 1 без # 2 в течение десятилетий, и это работало просто отлично. Иногда это деловое решение. У вас есть время, чтобы переписать ваше приложение и перепроектировать БД, или вы можете бросить пару долларов на облачные серверы, которые действительно дешевы в наши дни? Между прочим, когда мы использовали подход № 1, наша БД содержала миллионы объектов, к которым обращались 100 тысяч пользователей, а двухъядерный 16-гигабайтный сервер БД работал нормально.

Ответ 13

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

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

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

Теперь я бы сделал вариант 4 (EDIT) с добавлением ссылки на пользователей:

general_data_table
id
...


udfs_linked_table
id
general_data_id
udf_id


udfs_table
id
name
type
owner_id --> Use this to filter for the current user and limit their UDFs
string_link_id --> link table for string fields
int_link_id
type_link_id

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

Ответ 14

Я бы порекомендовал # 4, поскольку этот тип системы использовался в Magento, которая является высоко аккредитованной платформой CMS для электронной коммерции. Используйте одну таблицу для определения пользовательских полей, используя fieldId & обозначить столбцы. Затем создайте отдельные таблицы для каждого типа данных, и в каждой из этих таблиц есть индекс, который индексирует по fieldId, а столбцы по типу данных value. Затем в своих запросах используйте что-то вроде:

SELECT *
FROM FieldValues_Text
WHERE fieldId IN (
    SELECT fieldId FROM Fields WHERE [email protected]
)
AND value LIKE '%' + @search + '%'

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

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

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