设计数据库以保存不同的元数据信息 [英] designing database to hold different metadata information

问题描述

所以我想设计一个数据库，让我连接一个产品与多个类别。这部分我想到了。但我不能解决的是持有不同类型的产品细节的问题。

例如，产品可能是一本书（在这种情况下，我需要引用该书的元数据，如isbn，作者等），或者它可以是商家列表（具有不同的元数据）。

我应该如何处理？

解决方案

这称为观察模式。

alt text http://www.damirsystems.com/dp_images/observation_model_3.png

三个对象，例如

  Book 
 Title ='Gone with the Wind'
 Author ='Margaret Mitchell'
 ISBN ='978-1416548898'
 
 Cat 
 Name ='Phoebe'
 Color ='Gray'
 TailLength = 9'''''
 
啤酒瓶
 = 500'ml'
 Color ='Green'
  

：

 实体
 EntityID名称描述
 1'书''阅读'
 2 'cat''Fury cat'
 3'Beer Bottle''在'
  

。

  PropertyType 
 PropertyTypeID名称IsTrait说明
 1'Height''NO'具有高度'
 2'宽度''NO''对于任何具有宽度'
 3'的卷''''NO''对于可以具有卷'
 4'的东西'标题'' ''有些东西有标题'
 5'作者''是''可以创作'
 6'颜色''是的'东西的颜色'
 7'ISBN''YES ''书'需要这个'
 8'TailLength''NO''有长尾的东西
 9'名称''YES''东西的名称'
  

。

  b PropertyID EntityID PropertyTypeID 
 1 1 4  -  book，title 
 2 1 5  -  book，author 
 3 1 7  -  book，isbn 
 4 2 9  -  cat ，名称
 5 2 6  -  cat，color 
 6 2 8  -  cat，tail length 
 7 3 3  - 啤酒瓶，体积
 8 3 6  - 啤酒瓶子，颜色
  

。

 测量
 PropertyID单位价值
 6英寸9  - 尾巴长度
 7'ml'500  - 啤酒瓶，体积
  

。

  
 PropertyID Value 
 1'Gone with the Wind' -  book，title 
 2'Margaret Mitchell' -  book，author 
 3'978-1416548898' -  book， isbn 
 4'Phoebe' - 猫，名称
 5'灰色' - 猫，颜色
 8'绿色' - 啤酒瓶，颜色
  
 
 
 
 编辑：
 
 
 
 Jefferey提出了一个有效点
 
 
 
该模型允许动态创建任意数量的实体
任何类型的属性没有模式更改。 Hovewer，这种灵活性具有价格 - 存储和搜索比通常的表设计更慢和更复杂。 
 
 
 
时间一个例子，但首先，为了使事情变得更容易，我会将模型变成一个视图。
 将视图vModel创建为
 select 
 e.EntityId 
，x.Name为PropertyName 
，m.Value为MeasurementValue 
，m.Unit 
，t.Value as TraitValue 
从Entity as e 
将属性作为p连接到p.EntityID = p.EntityID 
将PropertyType连接为x on x .PropertyTypeId = p.PropertyTypeId 
 left join测量为m on m.PropertyId = p.PropertyId 
 left join Trait as t on t.PropertyId = p.PropertyId 
; 
 
要使用注释中的Jefferey示例
 与
 q_00 as（ - 所有图书
选择EntityID 
从vModel 
其中PropertyName ='object type'
 TraitValue ='book'
），
 q_01 as（ - 所有美国书籍
从vModel中选择EntityID 
作为
连接q_00作为b在b.EntityID = a.EntityID 
其中PropertyName ='发布商国家'
和TraitValue ='美国'
），
 q_02 as（ -  2008年出版的所有美国图书
 select EntityID 
从vModel作为
连接q_01作为b在b.EntityID = a.EntityID 
其中PropertyName ='year published'
和MeasurementValue = 2008 
） 
 q_03 as（ -  2008年发行的所有美国图书都不会中止
从vModel中选择EntityID 
作为
加入q_02作为b在b.EntityID = a.EntityID 
其中PropertyName ='停止'
和TraitValue ='no'
），
 q_04 as（ -  2008年发行的所有美国图书都不停止，成本小于$ 50 
从vModel选择EntityID 
作为
连接q_03作为b在b.EntityID = a.EntityID 
上，其中PropertyName ='price'
和MeasurementValue< 50 
和MeasurementUnit ='USD'
）
 select 
 EntityID 
，max（当'title'比TraitValue else null结束时为PropertyName）as标题
，max（case'Name'当'ISBN'比TraitValue，否则null结束）作为ISBN 
从vModel作为
加入q_04作为b在b.EntityID = a.EntityID 
组根据EntityID; 
  
这看起来很复杂，但是仔细检查你可能会注意到CTE的模式。 p> 
 
 
现在假设我们有一个标准的固定模式设计，其中每个对象属性都有自己的列。 
查询将类似于：
 从vModel中选择EntityID，Title，ISBN 
 
 WHERE ObjectType ='book'
 and PublisherCountry ='US'
 and YearPublished = 2008 
 and IsDiscontinued ='no'
 and Price< 50 
和Currency ='USD'
; 
  
 
So I am trying to design a database that will allow me to connect one product with multiple categories. This part I have figured. But what I am not able to resolve is the issue of holding different type of product details. 


For example, the product could be a book (in which case i would need metadata that refers to that book like isbn, author etc) or it could be a business listing (which has different metadata) .. 

How should I tackle that?
解决方案
This is called  the Observation Pattern.

alt text http://www.damirsystems.com/dp_images/observation_model_3.png

Three objects, for the example
Book
Title = 'Gone with the Wind' 
Author = 'Margaret Mitchell'
ISBN   = '978-1416548898'

Cat
Name = 'Phoebe'
Color = 'Gray'
TailLength = 9 'inch'

Beer Bottle
Volume = 500 'ml'
Color = 'Green'
This is how tables may look like:
Entity
EntityID    Name            Description
   1        'Book'            'To read'
   2        'Cat'             'Fury cat' 
   3        'Beer Bottle'     'To ship beer in'
.
PropertyType
PropertyTypeID   Name        IsTrait         Description
   1            'Height'     'NO'       'For anything that has height' 
   2            'Width'      'NO'       'For anything that has width' 
   3            'Volume'     'NO'       'For things that can have volume'
   4            'Title'      'YES'      'Some stuff has title' 
   5            'Author'     'YES'      'Things can be authored' 
   6            'Color'      'YES'      'Color of things' 
   7            'ISBN'       'YES'      'Books would need this'
   8            'TailLength' 'NO'       'For stuff that has long tails'
   9            'Name'       'YES'      'Name of things'
.
Property
PropertyID   EntityID  PropertyTypeID      
    1           1              4     -- book, title
    2           1              5     -- book, author
    3           1              7     -- book, isbn
    4           2              9     -- cat, name
    5           2              6     -- cat, color
    6           2              8     -- cat, tail length
    7           3              3     -- beer bottle, volume
    8           3              6     -- beer bottle, color
.
Measurement
PropertyID     Unit       Value 
    6          'inch'       9          -- cat, tail length
    7          'ml'        500         -- beer bottle, volume
.
Trait
PropertyID         Value 
    1         'Gone with the Wind'     -- book, title
    2         'Margaret Mitchell'      -- book, author
    3         '978-1416548898'         -- book, isbn
    4         'Phoebe'                 -- cat, name
    5         'Gray'                   -- cat, color
    8         'Green'                  -- beer bottle, color
EDIT:

Jefferey raised a valid point (see comment), so I'll expand the answer.

The model allows for dynamic (on-fly) creation of any number of entites
with any type of properties without schema changes. Hovewer, this flexibility has a price -- storing and searching is slower and more complex than in a usual table design. 

Time for an example, but first, to make things easier, I'll flatten the model into a view.
create view vModel as 
select 
      e.EntityId
    , x.Name  as PropertyName
    , m.Value as MeasurementValue
    , m.Unit
    , t.Value as TraitValue
from Entity           as e
join Property         as p on p.EntityID       = p.EntityID
join PropertyType     as x on x.PropertyTypeId = p.PropertyTypeId
left join Measurement as m on m.PropertyId     = p.PropertyId
left join Trait       as t on t.PropertyId     = p.PropertyId
;
To use Jefferey's example from the comment
with 
q_00 as ( -- all books
    select EntityID
    from vModel
    where PropertyName = 'object type'
      and TraitValue   = 'book' 
),
q_01 as ( -- all US books
    select EntityID
    from vModel as a
    join q_00   as b on b.EntityID = a.EntityID
    where PropertyName = 'publisher country'
      and TraitValue   = 'US' 
),
q_02 as ( -- all US books published in 2008
    select EntityID
    from vModel as a
    join q_01   as b on b.EntityID = a.EntityID
    where PropertyName     = 'year published'
      and MeasurementValue = 2008 
),
q_03 as ( -- all US books published in 2008 not discontinued
    select EntityID
    from vModel as a
    join q_02   as b on b.EntityID = a.EntityID
    where PropertyName = 'is discontinued'
      and TraitValue   = 'no' 
),
q_04 as ( -- all US books published in 2008 not discontinued that cost less than $50
    select EntityID
    from vModel as a
    join q_03   as b on b.EntityID = a.EntityID
    where PropertyName     = 'price'
      and MeasurementValue < 50 
      and MeasurementUnit  = 'USD'
)
select
      EntityID
    , max(case PropertyName when 'title' than TraitValue else null end) as Title
    , max(case PropertyName when 'ISBN'  than TraitValue else null end) as ISBN
from vModel as a
join q_04   as b on b.EntityID = a.EntityID
group by EntityID ;
This looks complicated to write, but on a closer inspection you may notice a pattern in CTEs.

Now suppose we have a standard fixed schema design where each object property has its own column.
The query would look something like:
select EntityID, Title, ISBN
from vModel
WHERE ObjectType       = 'book'
  and PublisherCountry = 'US'
  and YearPublished    = 2008
  and IsDiscontinued   = 'no'
  and Price            < 50
  and Currency         = 'USD'
;


                        
这篇关于设计数据库以保存不同的元数据信息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！