Хранение параметров с разными типами данных в одной таблице

#sql #база_данных #sql_server #eav #модель_данных

Есть набор параметров с разными типами данных. У каждого параметра есть имя, набор
ещё каких-то свойств и само значение параметра. Эта значение параметра может быть разных
типов в зависимости самого параметра.

Вопрос: Как организовать хранение значений этих параметров в одной таблице?

Варианты которые сразу пришли в голову:


Хранить значение параметра в типа varchar и сделать ещё один столбец с описанием
типа. И в случае необходимости этого параметра в приложении конвертировать его в нужный
тип. Таблица с параметрами выглядела бы примерно так:

id | name | value | value_type
Сделать кучу полей на каждый существующий тип параметра. То есть таблица с параметрами
выглядела бы примерно так:

id | name | value_varchar | value_int | value_bit | и так далее


Какие best practice есть для решения задачи такого рода?

Уточнение задачи:


Разумеется это будет две таблицы. Одна таблица-справочник с идентификатором и именем
параметра, а другая таблица-связь параметра с другой сущностью.
Параметров этих будет не много. Не много в рамках БД конечно. Не 2 миллиона.
Использоваться это будет следующим образом: Будет хранимка которая получает набор
параметров по идентификатору той самой другой сущности. И в приложении мне нужно их
распарсить в плане типа и использовать. Если это какой-то размер, то конвертируем в
int или double и применять. Если это какое-то название, то конвертируем в string и
используем, и так далее.


Принятое решение: Использовать тип данных sql_variant.
    

Ответы

Ответ 1

В MS SQL Server, начиная с версии 2008, есть такой тип данных, как sql_variant. Это
именно то, что нужно вам в вашей задаче.


Ответ 2

Если использовать первый вариант, то я бы навесил ограничение на столбец value. Ограничение,
использующее функцию, которая на вход принимает значение value_type и проверяет соответствие
значения типу с помощью встроенных функций типа isdate(), isnumeric().


Ответ 3

Ознакомьтесь с описанием объектной модели хранения данных в РСУБД.

Возможно будет полезно.

БД - хранилище объектов

Кратко о чем там применительно к вопросу.

Для каждого типа свойств создаётся пара таблиц:
Строковые:

Strings(TypeId, ObjectId, Value); StringDesc(TypeId, Code, ItemName)

временные, числовые и.т.п по аналогии

Strings - собственно значения свойств
StringDesc - описание типов свойств

значения столбцов:
ObjectId - ид объекта
TypeId - ид типа свойства
Code - код свойства
ItemName - человеческое описание свойства
Value - значение свойства.


Пусть объекты хранятся в таблице Objects(Id, TypeId, ItemName)
тогда можно писать запросы наподобие:

SELECT O.ItemName, S.Value
   FROM Objects O
    INNER JOIN ObjType OT ON O.TypeId = OT.Id
    INNER JOIN Strings S ON O.Id = S.ObjectId
    INNER JOIN StrDesc SD ON S.TypeId = SD.Id
  WHERE OT.Code = ‘EMPLOYEE’
    AND SD.TypeId = OT.Id
    AND SD.Code = ‘EMAIL’


Плюсы этого подхода в том, что изменяя данные в БД - мы меняем структуру объектов,
не меняя структуру таблиц.
Минусы - вертикальное хранение атрибутов и всё, что с этим связано.


Ответ 4

Я бы использовал 1-й вариант.

Я не знаю, какая тематика базы данных, но осмелюсь предположить, что большинство
параметров будут varchar, char, nvarchar. Так что если сделать поле value в типа varchar
с большим значением, то можно будет хранить абсолютно любые данные.  


Для выборки данных другого типа вполне можно использоваться функции CAST() и CONVERT().
И то они понадобятся только если надо проводить (например)
математические операции над цифрами. А если не надо, то value = '1'
тоже работать будет. Или даты сравнивать... 
Если value заиндексировать, то и LIKE() будет достаточно быстро работать


Еще мне кажется, что в таком случае value_type не нужно. Все равно ведь при построении
запросов Вы будете знать, какие данные будут обрабатываться, и будете применять нужный
CAST().



2-й вариант, конечно тоже приемлим

Однако, по-моему, мешает то, что типов данных в MsSQL много (https://msdn.microsoft.com/en-us/library/ms187752.aspx).
Да, их можно сгруппировать и хранить, скажем int, smallint и numeric в одной колонке.
Но даже в этом случае понадобятся от 5 до 8 полей, чтобы покрыть максимум типов данных.   

Мне лично не нравится иметь таблицу, в которой (я точно знаю) для каждой строчки
будут заполнены лишь 3 колонки из 7-10.

А если по каким-то причинам в середине проекта придется сменить тип данных? Все запросы
переписывать? (хотя, согласен, это маловероятно :) )



Ну и главное, последнее...
Все очень сильно зависит от объемов данных и личных предпочтений разработчика :):):):):)