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

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

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

Предположим, что они хранятся в таблице с именем exams с столбцами temperature, pressure и т.д. (а также id, patient_id и timestamp). Большинство измерений хранятся как поплавки, но некоторые из них относятся к другим типам (строки, целые числа...)

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

Он не уверен, как сохранить эти настраиваемые поля.

Он наклоняется к отдельной таблице (скажем, таблица custom_exam_data с полями, такими как exam_id, custom_field_id, float_value, string_value,...)

Я беспокоюсь, что это сделает поиск более трудным для достижения и менее эффективным.

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

Он боится динамически модифицировать базу данных и иметь разные схемы для каждой медицинской единицы.

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

Примечания:

он использует Ruby on Rails, но я думаю, что этот вопрос в значительной степени является агностиком рамки, за исключением того факта, что он ищет решения только в SQL-базах данных.
Я немного упростил проблему, так как пользовательские поля должны быть доступны для нескольких таблиц, но я считаю, что это действительно не влияет на направление.
(добавлено) Очень общий модуль отчетности должен будет искать, сортировать, генерировать статистику и т.д. этих данных, поэтому требуется, чтобы эти данные сохранялись в столбцах соответствующего типа
(добавлено) Входы пользователя будут отфильтрованы как для стандартных полей, так и для настраиваемых полей. Например, числа будут проверяться в пределах заданного диапазона (не может иметь температуру от -12 или +444) и т.д. Таким образом, преобразование в соответствующий тип SQL не является проблемой.

Ответ 1

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

Создание строк столбцов UserAttribute или Key/Value звучит привлекательно сначала, но это приводит к внутренне-платформенному эффекту, где вы в конечном итоге должны повторно использовать внешние ключи, типы данных, ограничения, транзакции, проверку, сортировку, группировку, вычисления и т.д. внутри вашей РСУБД. Вы можете просто использовать плоские файлы, а не SQL вообще.

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

Ответ 2

Странно, что так много людей придумывают специальные решения для этого, когда есть хорошо документированный шаблон для него:

Модель атрибута-атрибута (EAV)

Две альтернативы - это XML и вложенные наборы. XML проще в управлении, но в целом медленный. Вложенным наборам обычно требуется некоторый тип проприетарного расширения базы данных, чтобы не делать беспорядок, например CLR-типы в SQL Server 2005+. Они нарушают первую нормальную форму, но тем не менее являются самым быстродействующим решением.

Ответ 3

Microsoft Dynamics CRM достигает этого, изменяя дизайн базы данных каждый раз, когда происходит изменение. Надеюсь, я думаю.

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

Ответ 4

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

Ответ 5

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

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

Ответ 6

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

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

Ответ 7

Я не могу сказать вам наилучший способ, но я могу рассказать вам, как Drupal достигает своего рода схематической структуры, все еще используя стандартные СУБД, доступные сегодня.

Общая идея заключается в том, что существует таблица схем со списком полей. В каждой строке действительно только два столбца: столбец "String" и столбец "column": String. Для каждого из этих столбцов он фактически определяет целую таблицу, содержащую только идентификатор и фактические данные для этого столбца.

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

MySQL очень быстро возвращает данные строки для коротких строк с несколькими столбцами. Таким образом, эта схема заканчивается довольно быстро, позволяя вам много гибкости.

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

Ответ 8

Определите две новые таблицы: custom_exam_schema и custom_exam_data.

custom_exam_data имеет столбец exam_id плюс дополнительный столбец для каждого настраиваемого атрибута.

custom_exam_schema будет содержать строку для описания того, как интерпретировать каждый из столбцов таблицы custom_exam_data. Он имел бы столбцы типа name, type, minValue, maxValue и т.д.

Так, например, чтобы создать настраиваемое поле для отслеживания количества пальцев у человека, вы должны добавить ('fingerCount', 'number', 0, 10) в custom_exam_schema, а затем добавить столбец с именем fingerCount в таблицу exam.

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

Ответ 9

Я бы сохранил эти настраиваемые поля в таблице, где каждая запись (dataType, dataValue, dataUnit) использовалась бы в одной строке. Таким образом, было бы отношение oneToMany от одного образца к данным. Вы также можете создать таблицу для записи всех типов видов вырезок, которые вы использовали бы. Например:

create table DataType
(
id int primary key,
name varchar(100) not null unique
description text,
uri varchar(255) //<-- can be used for an ONTOLOGY
)


create table DataRecord
(
id int primary key,
sample_id int not null,//<-- reference to the sample
dataType_id int not null, //<-- references DataType
value varchar(100),//<-- the value as string
unit varchar(50)//<-- g, mg/ml, etc... but it could also be a link to a table describing the units just like DataType
)

Ответ 10

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

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

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

Учитывая, что база данных:
- нужно быть гибким
- что данные из нескольких таблиц могут быть добавлены и настроены

- чтобы вы могли сохранить целостность основной структуры вашей базы данных для целей распределения и единообразия
- эти данные ДОЛЖНЫ иметь предел, а также предупреждения и предупреждения
- что данные должны иметь единицы (10 кг или 10 фунтов)?
- данные могут иметь выбор вариантов
- эти данные могут иметь разные права (от простого пользователя до администратора)
- что эти данные могут потребоваться для создания отчетов без изменения кода (автоматизация)
- что эти данные могут потребоваться для проведения анализа перекрестных ссылок в системе без изменения кода

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