例如,假设我有一个关于车辆的数据库.假设我们实际上不太了解车辆,所以我们不能指定所有车辆的列.因此,我们将“标记”车辆信息.
- 1. manufacture: Mercedes
- model: SLK32 AMG
- convertible: hardtop
- 2. manufacture: Ford
- model: GT90
- production phase: prototype
- 3. manufacture: Mazda
- model: MX-5
- convertible: softtop
现在,你可以看到所有的汽车都被标记了他们的制造和型号,但其他类别并不是全部匹配.请注意,一辆汽车只能有一个类别. IE浏览器.一辆汽车只能有一个制造商.
我想设计一个数据库来支持所有梅赛德斯的搜索,或者能够列出所有的制造商.
我目前的设计是这样的:
- vehicles
- int vid
- String vin
- vehicleTags
- int vid
- int tid
- tags
- int tid
- String tag
- int cid
- categories
- int cid
- String category
我拥有所有正确的主键和外键,除了我不能处理每个车只能有一个制造商的情况.还是我可以
我可以在vehicleTags中的复合主键中添加外键约束吗? IE浏览器.我可以添加一个约束,使得复合主键(vid,tid)只能在车辆标签中没有一行,才能添加到车辆标签,以便对于同一个vid,没有一个tid同样的cid?
我的猜测是否定的我认为这个问题的解决方案是添加一个cid列到vehicleTags,并使新的复合主键(vid,cid).它看起来像:
- vehicleTags
- int vid
- int cid
- int tid
这样可以防止汽车有两个制造商,但现在我已经复制了cid中的信息.
我的模式应该是什么?
在我之前的问题How do you do many to many table outer joins?中,Tom注意到我的数据库模式中的这个问题
编辑
我知道,在这个例子中,制造业应该是车载表中的一列,但是我们说你不能这样做.这个例子只是一个例子.
解决方法
更可识别的EAV表格如下所示:
- CREATE TABLE vehicleEAV (
- vid INTEGER,attr_name VARCHAR(20),attr_value VARCHAR(100),PRIMARY KEY (vid,attr_name),FOREIGN KEY (vid) REFERENCES vehicles (vid)
- );
有些人强制attr_name引用预定义属性名称的查找表,以限制混乱.
您所做的只是简单地将一个EAV表传播到三个表上,但不改进元数据的顺序:
- CREATE TABLE vehicleTag (
- vid INTEGER,cid INTEGER,tid INTEGER,cid),FOREIGN KEY (vid) REFERENCES vehicles(vid),FOREIGN KEY (cid) REFERENCES categories(cid),FOREIGN KEY (tid) REFERENCES tags(tid)
- );
- CREATE TABLE categories (
- cid INTEGER PRIMARY KEY,category VARCHAR(20) -- "attr_name"
- );
- CREATE TABLE tags (
- tid INTEGER PRIMARY KEY,tag VARCHAR(100) -- "attr_value"
- );
如果您要使用EAV设计,您只需要车辆标签和类别表.
- CREATE TABLE vehicleTag (
- vid INTEGER,-- reference to "attr_name" lookup table
- tag VARCHAR(100,-- "attr_value"
- PRIMARY KEY (vid,FOREIGN KEY (cid) REFERENCES categories(cid)
- );
但请记住,您正在将数据与元数据进行混合.您无法对数据模型应用某些限制.
>如何使其中一个类别成为强制性(常规列使用NOT NULL约束)?
>如何使用sql数据类型验证一些标签值?您不能,因为您为每个标签值使用长字符串.这个字符串对于您将来需要的每个标签都足够长吗?你不能告诉
>如何将一些标签限制为一组允许的值(传统的表使用查找表的外键)?这是你的“软顶”与“软顶”的例子.但是,您不能对标签列做出约束,因为该约束将适用于其他类别的所有其他标记值.您可以有效地将发动机尺寸和油漆颜色限制为“软顶”.
sql数据库与此模型不兼容.得到正确的是非常困难的,查询它变得非常复杂.如果您继续使用sql,那么您将更好地对表进行常规建模,每个属性有一列.如果您需要“子类型”,然后根据子类型(Class-Table Inheritance)定义下属表,否则使用Single-Table Inheritance.如果每个实体的属性有无限变化,则使用Serialized LOB.
为这些流体,非关系数据模型设计的另一种技术是语义数据库,存储数据于RDF,并与SPARQL进行了查询.一个免费的解决方案是Sesame.