Схема поддержки динамических свойств

17

Я работаю над редактором, который позволяет своим пользователям создавать определения «объектов» в режиме реального времени. Определение может содержать ноль или больше свойств. Свойство имеет имя типа. После создания определения пользователь может создать объект этого определения и установить значения свойств этого объекта.

Таким образом, нажатием кнопки мыши пользователь должен, то есть. сможете создать новое определение под названием «Велосипед» и добавить свойство «Размер» типа «Числовой». Затем еще одно свойство под названием «Имя» типа «Текст», а затем другое свойство «Цена» типа «Числовое». Как только это будет сделано, пользователь должен будет создать пару объектов «Велосипед» и заполнить значения свойств «Имя» и «Цена» для каждого байка.

Теперь я видел эту функцию в нескольких программных продуктах, поэтому она должна быть хорошо известной концепцией. Моя проблема началась, когда я сел и попытался придумать схему БД для поддержки этой структуры данных, потому что я хочу, чтобы значения свойств сохранялись с использованием соответствующих типов столбцов. То есть. значение числового свойства сохраняется как, скажем, 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 выглядел?

    
задан Johan Fredrik Varen 16.01.2011 в 15:25
источник

2 ответа

28

Вы реализуете нечто, называемое модель Entity-Attribute-Value Ссылка .

Многие люди скажут, что это плохая идея (как правило, я одна из них), потому что ответ на ваш последний вопрос: «Что бы SQL для извлечения ...» имеет тенденцию быть «густым волосатым и противным», а gettting хуже ".

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

Для вашего первого вопроса: «Что будет иметь столбец« type »таблицы prop_defs?», все будет проще, если у вас есть таблица типов и описаний, которая содержит {«numeric», «Any Number»}, { «textual», «String»} и т. д. Первое значение - это первичный ключ. Затем в prop_defs ваш столбец «type» является внешним ключом к этой таблице и содержит значения «числовые», «текстовые» и т. Д. Некоторые скажут вам неверно всегда использовать целые ключи, потому что они JOIN быстрее, но если вы используете значения « числовое "," текстовое "и т. д. вам не нужно ПРИСОЕДИНЯЙТЕ , и самый быстрый JOIN - это тот, который вы не делаете.

Запрос для захвата одного значения будет иметь оператор CASE:

SELECT case when pd.type = "numeric" then pv.numeric
            when pd.type = "textual" then pv.textual
            when pd.type = "boolean" then pv.boolean
  from prov_vals pv 
  JOIN prop_defs pd ON pv.prop_def_id = pv.id
 WHERE pv.object_id = 2
   AND pd.name = "Name"
    
ответ дан Ken Downs 16.01.2011 в 16:51
  • Отличный ответ! Большое спасибо :) –  Johan Fredrik Varen 18.01.2011 в 19:23
  • Что тогда было бы лучшим решением в случае, если EAV-то, чего следует избегать, когда возникает необходимость в размещении предметов? –  ChrisR 18.01.2012 в 16:10
  • Теперь с решениями NoSQL, такими как MongoDB, EAV может, наконец, умереть. –  Bernard Igiri 18.05.2015 в 14:13
4

Вы должны признать, что реляционные базы данных не обеспечивают такую ​​функциональность. Они МОГУТ это обеспечить, но не очень хороши в этом. (Надеюсь, я ошибаюсь). Реляционные базы данных лучше подходят для определенных интерфейсов, а не для изменения интерфейсов.

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

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

- Как правило, вы создаете / изменяете таблицы во время разработки, а не время запуска. Должны ли мы действительно дискриминировать изменение базы данных во время выполнения? может быть, может и нет. Создание новых таблиц, внешних ключей и столбцов во время выполнения позволяет достичь истинных динамических объектов, при этом обеспечивая преимущества производительности «обычных» таблиц. Но вам придется запрашивать схему базы данных, а затем динамически генерировать все ваши запросы. Это сосать. Это полностью нарушит концепцию таблиц как интерфейса.

    
ответ дан Lord Tydus 17.01.2011 в 00:38