问题描述
所以我想设计一个数据库，让我连接一个产品与多个类别。这部分我想到了。但我不能解决的是持有不同类型的产品细节的问题。
例如，产品可能是一本书（在这种情况下，我需要引用该书的元数据，如isbn，作者等），或者它可以是商家列表（具有不同的元数据）。
我应该如何处理？
这称为观察模式。
三个对象，例如
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 exampleBook 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 entiteswith 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 commentwith 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' ;
这篇关于设计数据库以保存不同的元数据信息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！