Схема для поддержки динамических свойств
Я работаю над редактором, который позволяет пользователям создавать определения "объектов" в реальном времени. Определение может содержать ноль или более свойств. Свойство имеет имя и тип. Как только определение создано, пользователь может создать объект этого определения и установить значения свойств этого объекта.
Таким образом, щелчком кнопки мыши пользователь должен, т.е. иметь возможность создать новое определение под названием «Велосипед» и добавить свойство «Размер» типа «Числовой». Затем еще одно свойство с именем «Имя» типа «Текст», а затем еще одно свойство с именем «Цена» типа «Числовой». Как только это будет сделано, пользователь сможет создать пару объектов «Велосипед» и ввести значения свойств «Имя» и «Цена» для каждого велосипеда.
Теперь я видел эту функцию в нескольких программах продукты, поэтому это должно быть хорошо известное понятие. Моя проблема началась, когда я сел и попытался придумать схему БД для поддержки этой структуры данных, потому что я хочу, чтобы значения свойств сохранялись с использованием соответствующих типов столбцов. Т.е. числовое значение свойства хранится, скажем, как INT в базе данных, а значение текстового свойства хранится как VARCHAR.
Во-первых, мне нужна таблица, которая будет содержать все определения моих объектов:
Table obj_defs
id | name |
----------------
1 | "Bicycle" |
2 | "Book" |
Затем мне нужна таблица для хранения того, какие свойства должно иметь каждое определение объекта:
Table prop_defs
id | obj_def_id | name | type |
------------------------------------
1 | 1 | "Size" | ? |
2 | 1 | "Name" | ? |
3 | 1 | "Price" | ? |
4 | 2 | "Title" | ? |
5 | 2 | "Author" | ? |
6 | 2 | "ISBN" | ? |
Мне также понадобится таблица, содержащая каждый объект:
Table objects
id | created | updated |
------------------------------
1 | 2011-05-14 | 2011-06-15 |
2 | 2011-05-14 | 2011-06-15 |
3 | 2011-05-14 | 2011-06-15 |
Наконец, мне нужна таблица, в которой будут храниться фактические значения свойств каждого объекта, и одно решение состоит в том, чтобы эта таблица имела по одному столбцу для каждого возможного типа значения, например:
Table prop_vals
id | prop_def_id | object_id | numeric | textual | boolean |
------------------------------------------------------------
1 | 1 | 1 | 27 | | |
2 | 2 | 1 | | "Trek" | |
3 | 3 | 1 | 1249 | | |
4 | 1 | 2 | 26 | | |
5 | 2 | 2 | | "GT" | |
6 | 3 | 2 | 159 | | |
7 | 4 | 3 | | "It" | |
8 | 5 | 3 | | "King" | |
9 | 6 | 4 | 9 | | |
Если бы я реализовал эту схему, что бы содержал столбец «type» таблицы prop_defs? Целые числа, которые соответствуют имени столбца, varchars, которые просто содержат имя столбца? Какие-нибудь другие возможности? Поможет ли мне хранимая процедура каким-то образом? И как бы выглядел SQL для получения свойства «name» объекта 2?