支持动态属性的模式[英] Schema to support dynamic properties

本文是小编为大家收集整理的关于支持动态属性的模式的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

我正在研究一个编辑器,该编辑器使其用户能够实时创建"对象"定义.定义可以包含零或更多属性.属性具有名称类型.创建定义后,用户可以创建该定义的对象并设置该对象的属性值.

因此,通过单击鼠标按钮,用户应该IE.能够创建一个名为"自行车"的新定义,并添加类型"数字"的属性"大小".然后是另一个称为"文本"的"名称"的属性,然后另一个称为"数字"的属性称为"价格".完成此操作后,用户应该能够创建几个"自行车"对象并填写每辆自行车的"名称"和"价格"属性值.

现在,我已经在几种软件产品中看到了此功能,因此必须是一个众所周知的概念.当我坐下来试图提出DB模式以支持此数据结构时,我的问题始于我的问题,因为我希望使用适当的列类型存储属性值. IE.数字属性值被存储为数据库中的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 |         |         |

如果我实施了此模式,则Prop_defs表的"类型"列会包含什么?每个映射到列名的整数,简单地保存列名的Varchars?还有其他可能性吗?存储程序会以某种方式帮助我吗?获取对象2的"名称"属性的SQL将是什么样的?

推荐答案

您正在实施一种称为Entity-Attribute-Value模型 http://http://en.wikipedia.org/wiki/entity-attribute-value_model .

很多人会说这是一个坏主意(通常是我是其中之一),因为您最后一个问题的答案是:" sql的sql将是什么……"更糟."

一旦您允许用户开始将对象嵌套在其他对象内,如果您不允许这样做,情况将保持易于管理.

对于您的第一个问题," prop_defs表的"类型"列会保存什么,如果您有一个包含{" numeric","任何数字"}的类型和描述表,一切都会更简单. " textual"," string"}等.第一个值是主要键.然后,在prop_defs中,您的列" type"是该表的外键,并保存值"数字"," textual"等.有些人会告诉您不正确地告诉您始终使用整数键,因为它们加入了更快,但是如果您使用值,则可以使用"值".数字"," textual"等.您不必加入,最快的加入是您不做的.

获取单个值的查询将有一个案例语句:

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"

其他推荐答案

您必须接受关系数据库不擅长提供此类功能.他们可以提供,但不擅长. (我希望我错了).关系数据库更好地适合定义的接口,而不是更改接口.

- eav桌子提供动态场,但效果很糟糕.很烂索引.查询很复杂.在许多情况下,它可以完成工作,但是可以在大桌子上崩溃,其中很多用户都可以击中系统.

- 带有几个位置持有人列的常规表,可以进行性能,但是您可以获得非描述性列名称,并且可以在可以"添加"的列数中受到限制.另外,它不支持子类型分离.

- 通常,您在开发时创建/修改表,而不是运行时间.我们应该真的区分运行时修改数据库吗?也许,也许不是.在运行时创建新的表,外键和列可以实现真正的动态对象,同时给出"常规"表的性能优势.但是您必须查询数据库的架构,然后动态生成所有查询.那会很烂.它将完全打破表作为接口的概念.

本文地址:https://www.itbaoku.cn/post/597473.html

问题描述

I'm working on an editor that enables its users to create "object" definitions in real-time. A definition can contain zero or more properties. A property has a name a type. Once a definition is created, a user can create an object of that definition and set the property values of that object.

So by the click of a mouse-button, the user should ie. be able to create a new definition called "Bicycle", and add the property "Size" of type "Numeric". Then another property called "Name" of type "Text", and then another property called "Price" of type "Numeric". Once that is done, the user should be able to create a couple of "Bicycle" objects and fill in the "Name" and "Price" property values of each bike.

Now, I've seen this feature in several software products, so it must be a well-known concept. My problem started when I sat down and tried to come up with a DB schema to support this data structure, because I want the property values to be stored using the appropriate column types. Ie. a numeric property value is stored as, say, an INT in the database, and a textual property value is stored as VARCHAR.

First, I need a table that will hold all my object definitions:

Table obj_defs

id | name      |
----------------
 1 | "Bicycle" |
 2 | "Book"    |

Then I need a table for holding what sort of properties each object definition should have:

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"    |    ? |

I would also need a table that holds each object:

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 |

Finally, I need a table that will hold the actual property values of each object, and one solution is for this table to have one column for each possible value type, such as this:

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 |         |         |

If I implemented this schema, what would the "type" column of the prop_defs table hold? Integers that each map to a column name, varchars that simply hold the column name? Any other possibilities? Would a stored procedure help me out here in some way? And what would the SQL for fetching the "name" property of object 2 look like?

推荐答案

You are implementing something called Entity-Attribute-Value model http://en.wikipedia.org/wiki/Entity-attribute-value_model.

Lots of folks will say it's a bad idea (usually I am one of those) because the answer to your last question, "What would the SQL for fetching..." tends to be "thick hairy and nasty, and gettting worse."

These criticisms tend to hold once you allow users to start nesting objects inside of other objects, if you do not allow that, the situation will remain manageable.

For your first question, "what would the "type" column of the prop_defs table hold", everything will be simpler if you have a table of types and descriptions that holds {"numeric","Any Number"}, {"textual","String"}, etc. The first value is the primary key. Then in prop_defs your column "type" is a foreign key to that table and holds values "numeric", "textual", etc. Some will tell you incorrectly to always use integer keys because they JOIN faster, but if you use the values "numeric", "textual" etc. you don't have to JOIN and the fastest JOIN is the one you don't do.

The query to grab a single value will have a CASE statement:

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"

其他推荐答案

You must accept that relational databases are not good at providing this kind of functionality. They CAN provide it, but are not good at it. (I hope I'm wrong). Relational databases lend themselves better to defined interfaces, not changing interfaces.

--EAV tables give dynamic fields but suck on performance. Sucks on indexing. And it is complex to query. It gets the job done in many situations, but can fall apart on big tables with lots of users hitting the system.

--"Regular" tables with several place holder columns are OK for performance, but you get non-descriptive column names and are limited in the number of columns you can "add". Also it does not support sub-type separation.

--Typically you create/modify tables at development time, not run time. Should we really discriminate against modifying the database at run time? maybe, maybe not. Creating new tables, foreign keys, and columns at run-time can achieve true dynamic objects, while giving the performance benefits of "regular" tables. But you would have to query the schema of the database, then dynamically generate all of your queries. That would suck. It would totally break the concept of tables as an interface.