模式支持动态属性 [英] Schema to support dynamic properties
问题描述
我正在编辑一个编辑器，使用户能够实时创建对象定义。定义可以包含零个或多个属性。属性具有名称类型。一旦定义被创建，用户可以创建该定义的对象并设置该对象的属性值。
因此，通过点击鼠标按钮，用户应该ie。能够创建一个名为Bicycle的新定义，并添加Numeric类型的属性Size。然后另一个属性名为名称类型文本，然后另一个属性价格类型数字。一旦完成，用户应该能够创建几个自行车对象，并填写每个自行车的名称和价格属性值。
现在，我已经在几个软件产品中看到了这个功能，所以它必须是一个着名的概念。我的问题开始时，我坐下，试图想出一个数据库模式来支持这种数据结构，因为我想要使用适当的列类型存储属性值。也就是说。一个数字属性值被存储为数据库中的一个INT，一个文本属性值被存储为VARCHAR。
首先，我需要一个表所有我的对象定义：
表obj_defs
id |名称|
----------------
1 | 自行车|
2 | 书|
然后我需要一个表来保存每个对象定义应该有哪些属性：
表prop_defs
id | obj_def_id |名称|类型|
------------------------------------
1 | 1 | Size| ？ |
2 | 1 | Name| ？ |
3 | 1 | 价格| ？ |
4 | 2 | Title| ？ |
5 | 2 | 作者| ？ |
6 | 2 | ISBN| ？ |
我还需要一个包含每个对象的表：
表对象
id |创建|更新|
------------------------------
1 | 2011-05-14 | 2011-06-15 |
2 | 2011-05-14 | 2011-06-15 |
3 | 2011-05-14 | 2011-06-15 |
最后，我需要一个表来保存每个对象的实际属性值，对于此表，每个可能的值类型都有一列，例如：
表prop_vals
id | prop_def_id | object_id |数字|文本|布尔|
---------------------------------------------- --------------
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表的type列是什么？每个映射到列名称的整数，只保存列名称的varchars？任何其他可能性？一个存储过程在某种程度上帮助我在这里？
你正在实现一个名为Entity-属性值模型 http://en.wikipedia.org/wiki/Entity-attribute-value_model 。
很多人会说这是一个坏主意（通常我是其中的一个），因为你最后一个问题的答案，SQL ...。
这些批评一旦你允许用户开始嵌套对象
对于你的第一个问题，prop_defs表的类型列将保持什么 ，如果你有一个包含{numeric，any number}，{textual，String}等类型和描述的表，第一个值就是主键。然后在prop_defs你的列类型是该表的外键，并保持值数字，文字等等。有些人会告诉你不正确地总是使用整数键，因为他们JOIN更快，但如果你使用值数字，文字等等。
$ 获取单个值的查询将具有CASE语句： 当pd.type =numeric数字
当pd.type =textual然后pv.textual
当pd.type =boolean然后pv.boolean
从prov_vals pv
JOIN prop_defs pd ON pv。 prop_def_id = pv.id
WHERE pv.object_id = 2
AND pd.name =Name
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"
这篇关于模式支持动态属性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
