消除组合关系乘法 [英] Eliminating Combinatorial Relationship Multiplication
问题描述
假设我有可以属于组织的用户。组织
排列在一棵树上。每个组织只有一个父母
组织，但用户可能是多个组织的成员。
我面临的问题是组织和个人
用户可能与其他实体有关系，这些实体在语义上是相同的。例如，个人用户可以购买
的东西，组织也可以购买。个人用户可以拥有
业务合作伙伴，组织也可以。所以我似乎需要一个重复的链接表集合，用于将用户链接到
购买，然后是一个将组织链接到<的并行链接表。购买
。如果我有N个实体，用户和组织
可能有关系，那么我需要2 * N个链接表。这本身没有什么错误
错误但只有两个不同的
表对于一个性质相同的关系而言并不优雅，例如
purchaseserm> purchaseitem。
我想到的另一种方法是创建一个中间的
实体（比如它叫做'持有人）将用于持有参考资料
给组织和个人
可能拥有的所有关系。将有2个链接表链接到
" holder"和用户持有者。持有人将参考
购买，合作伙伴等。在这种情况下，链接表的数量
将是N + 2而不是2 * N，但它的性能成本为
额外的连接。
有没有更好的方法来建模这个2个不同实体的概念
可以拥有与其他N个实体类似的关系？
Suppose I have users that can belong to organizations. Organizations
are arranged in a tree. Each organization has only one parent
organization but a user maybe a member of multiple organizations.
The problem that I''m facing that both organizations and individual
users may have relationships with other entities which are
semantically the same. For instance, an individual user can purchase
things and so can an organization. An individual user can have
business partners and so can an organization. So it seems that I would
need to have a duplicate set of link tables that link a user to a
purchase and then a parallel link table linking an organization to a
purchase. If I have N entities with which both users and organizations
may have relationships then I need 2*N link tables. There is nothing
wrong with that per se but just not elegant to have two different
tables for a relationship which is the same in nature, e.g.
purchaser->purchaseditem.
One other approach I was thinking of is to create an intermediate
entity (say it''s called "holder") that will be used to hold references
to all the relationships that both an organization and an individual
may have. There will be 2 link tables linking organizations to
"holder" and users to "holder". Holder will in turn reference the
purchases, partners and so on. In this case the number of link tables
will be N+2 as opposed to 2*N but it will have a performance cost of
an extra join.
Is there a better way of modelling this notion of 2 different entities
that can possess similar relationships with N other entities?
推荐答案
" Jeff Lanfield" < JL *********** @ yahoo.com>在消息中写道
news：23 ************************** @ posting.google.c om ...
"Jeff Lanfield" <jl***********@yahoo.com> wrote in message
news:23**************************@posting.google.c om...
假设我有可以属于组织的用户。组织安排在一棵树上。每个组织只有一个父组织，但用户可能是多个组织的成员。
我面临的问题是组织和个人
用户可能有关系与其他在语义上相同的实体。例如，个人用户可以购买东西，组织也可以购买。个人用户可以拥有业务合作伙伴，组织也可以。因此，我似乎需要一组重复的链接表，将用户链接到
购买，然后是一个将组织链接到
购买的并行链接表。如果我有N个实体，用户和组织可能都有关系，那么我需要2 * N个链接表。本身没有什么错误，但是对于一个性质相同的关系而言，只有两个不同的表格并不优雅，例如
购买者 - > purchaseitem。
我想到的另一种方法是创建一个中间
实体（比如说它叫做持有者），它将用于保存所有关系的参考。一个组织和一个人可能有。将有2个链接表将组织链接到
holder。和用户持有者。持有人将参考
购买，合作伙伴等。在这种情况下，链接表的数量将是N + 2而不是2 * N，但是它将具有额外连接的性能成本。
这是客户的常见情况，例如，客户可以b
然后是组织或个人。
有没有更好的方法来建模2个不同实体的概念
可以与其他N个实体拥有类似的关系？
Suppose I have users that can belong to organizations. Organizations
are arranged in a tree. Each organization has only one parent
organization but a user maybe a member of multiple organizations.
The problem that I''m facing that both organizations and individual
users may have relationships with other entities which are
semantically the same. For instance, an individual user can purchase
things and so can an organization. An individual user can have
business partners and so can an organization. So it seems that I would
need to have a duplicate set of link tables that link a user to a
purchase and then a parallel link table linking an organization to a
purchase. If I have N entities with which both users and organizations
may have relationships then I need 2*N link tables. There is nothing
wrong with that per se but just not elegant to have two different
tables for a relationship which is the same in nature, e.g.
purchaser->purchaseditem.
One other approach I was thinking of is to create an intermediate
entity (say it''s called "holder") that will be used to hold references
to all the relationships that both an organization and an individual
may have. There will be 2 link tables linking organizations to
"holder" and users to "holder". Holder will in turn reference the
purchases, partners and so on. In this case the number of link tables
will be N+2 as opposed to 2*N but it will have a performance cost of
an extra join.
This is common scenario for a Customer, for example, where the Customer can
then be either an organization or an individual.
Is there a better way of modelling this notion of 2 different entities
that can possess similar relationships with N other entities?
如果你只是在寻找对它进行建模，然后使用接口/实现
方法可以在客户（持有人）作为接口的情况下工作，并且
组织和人员都实现该接口。你可以为
关系实现投入一个抽象类（接口的部分实现）并扩展它。在我看来，在UML OO类型的图表中建模比使用关系更加方便
。
但是，如果你想在模型中建模它为了在SQL数据库中实现
那么我认为你是用你的Holder模式走向正确的道路，
虽然可能还有其他的方法我错过了太。我建议
为Holder关系选择一个有意义的名字做一些工作
（例如，它让每个人完全理解客户可能是一个< br $> b $ b b person or or org）。
干杯！ --dawn
If you are just looking to model it, then using an interface/implementation
approach would work where you have Customer (Holder) as an interface and
Organization and Person both implementing that interface. You could toss in
an abstract class (partial implementation of the Interface) for the
relationship implementations and extend that too. This is more handily
modeled in UML OO types of diagrams than with relations, it seems to me.
But, if you are looking to model it in order to implement in a SQL database
then I think you are headed down the right path with your Holder pattern,
although there could be other approaches that I''m missing too. I''d suggest
putting some work into picking a meaningful name for the Holder relation
(for example, it makes complete sense to everyone that a Customer could be a
person or an org).
Cheers! --dawn
" Jeff Lanfield" < JL *********** @ yahoo.com>在消息中写道
news：23 ************************** @ posting.google.c om ...
"Jeff Lanfield" <jl***********@yahoo.com> wrote in message
news:23**************************@posting.google.c om...
假设我有可以属于组织的用户。组织安排在一棵树上。每个组织只有一个父组织，但用户可能是多个组织的成员。
我面临的问题是组织和个人
用户可能有关系与其他在语义上相同的实体。例如，个人用户可以购买东西，组织也可以购买。个人用户可以拥有业务合作伙伴，组织也可以。因此，我似乎需要一组重复的链接表，将用户链接到
购买，然后是一个将组织链接到
购买的并行链接表。如果我有N个实体，用户和组织可能都有关系，那么我需要2 * N个链接表。本身没有什么错误，但是对于一个性质相同的关系而言，只有两个不同的表格并不优雅，例如
购买者 - > purchaseitem。
我想到的另一种方法是创建一个中间
实体（比如说它叫做持有者），它将用于保存所有关系的参考。一个组织和一个人可能有。将有2个链接表将组织链接到
holder。和用户持有者。持有人将参考
购买，合作伙伴等。在这种情况下，链接表的数量将是N + 2而不是2 * N，但它将具有额外连接的性能成本。
是否有更好地建模2个不同实体的概念，可以与其他N个实体拥有相似的关系吗？
Suppose I have users that can belong to organizations. Organizations
are arranged in a tree. Each organization has only one parent
organization but a user maybe a member of multiple organizations.
The problem that I''m facing that both organizations and individual
users may have relationships with other entities which are
semantically the same. For instance, an individual user can purchase
things and so can an organization. An individual user can have
business partners and so can an organization. So it seems that I would
need to have a duplicate set of link tables that link a user to a
purchase and then a parallel link table linking an organization to a
purchase. If I have N entities with which both users and organizations
may have relationships then I need 2*N link tables. There is nothing
wrong with that per se but just not elegant to have two different
tables for a relationship which is the same in nature, e.g.
purchaser->purchaseditem.
One other approach I was thinking of is to create an intermediate
entity (say it''s called "holder") that will be used to hold references
to all the relationships that both an organization and an individual
may have. There will be 2 link tables linking organizations to
"holder" and users to "holder". Holder will in turn reference the
purchases, partners and so on. In this case the number of link tables
will be N+2 as opposed to 2*N but it will have a performance cost of
an extra join.
Is there a better way of modelling this notion of 2 different entities
that can possess similar relationships with N other entities?
您需要将以下内容转换为ERD。我已经提供了参与
（可能）约束和基数约束。完成ERD后，转换为关系模式即可获得
snap。
人们（可能）有m个BUSINESS_PARTNERS
组织n（可能）拥有BUSINESS_PARTNERS
人民（可能）属于组织
人民币1（可能）购买商品
组织1（可能）买n货物
因此，在模拟伪ERD形式：
PEOPLE ==>有m：n = => BUSINESS_PARTNERS< == n：m< == ORGANIZATIONS
" ==> people_order ==> 1：m GOODS m：1
< == orgs_order< =="
所以，你结束了
人物
person_id PK
等
组织
org_id PK < br $>
等
BUSINESS_PARTNERS
person_id PK（FK）
org_id PK（FK）
商品
item_id（PK）
描述
等
PEOPLE_ORDER
order_id PK
person_id（FK）
item_id（FK）
order_date
等
您现在可以创建ORGS_ORDER表，或者将org_id添加到
PEOPLE_ORDER表中，并将表名更改为ORDER 。这是一个基于很多东西的决定，主要是person_id和org_id的语义。如果
它们是互斥的，那么你可以简单地通过
添加org_id来组合表格。如果它们不是相互排斥的，那么可怕的选择是将
添加到ORDERS表中的标志，指示订单是来自
的人还是组织。不要这样做。
You need to convert the following into an ERD. I''ve supplied particpation
(may) constraints and cardinality constraints. Once the ERD is done, it is a
snap to convert to a relational schema.
PEOPLE n (may) have m BUSINESS_PARTNERS
ORGANIZATIONS n (may) have BUSINESS_PARTNERS
PEOPLE n (may) belong_to m ORGANIZATIONS
PEOPLE 1 (may) buy n GOODS
ORGANIZATIONS 1 (may) buy n GOODS
So, in mock pseudo ERD form:
PEOPLE == >have m:n ==> BUSINESS_PARTNERS <== n:m have <== ORGANIZATIONS
" ==> people_order ==> 1:m GOODS m:1
<== orgs_order <== "
So, you wind up with
PEOPLE
person_id PK
etc
ORGANIZATIONS
org_id PK
etc
BUSINESS_PARTNERS
person_id PK (FK)
org_id PK (FK)
GOODS
item_id (PK)
description
etc
PEOPLE_ORDER
order_id PK
person_id (FK)
item_id (FK)
order_date
etc
You can now either create an ORGS_ORDER table, or add in org_id to the
PEOPLE_ORDER table, and change the table name to ORDER. This is a decsion
based on many things, primarily the semantics of person_id and org_id. If
they are mutually exclusive, then you can combine the tables simply by
adding org_id. If they are not mutually exclusive, a horrible option is to
add a flag to the ORDERS table, indicating whether the order is from a
person or an organization. Don''t do it.
这是Amazon.com上关于Trees& Sons的新书的链接。层次结构
in SQL"
http://www.amazon.com/ exec / obidos / tg ... roduct-details
将树结构与节点分开。我喜欢嵌套的
模型的结构，但你可以选择最适合你的
情况。然后节点可以进入另一个表。
经典场景要求一个具有所有常见的
属性的根类，然后是它下面的专用子类。作为一个例子，
让我们拿车辆类并找到行业标准
标识符（VIN），并添加两个互斥的子类，Sport < b / b
多功能车和轿车（''SUV''，''SED''）。
CREATE TABLE车辆
（vin CHAR（17）NOT NULL PRIMARY KEY，
vehicle_type CHAR（3）NOT NULL
CHECK（vehicle_type IN（''SUV''，''SED''）） ，
UNIQUE（vin，vehicle_type），
..）;
注意重叠的候选键。然后我使用复合候选人
键（vin，vehicle_type）和每个子类表中的约束来确定vehicle_type是否已锁定且与车辆<同意br />
表。添加一些DRI动作，你就完成了：
CREATE TABLE SUV
（vin CHAR（17）NOT NULL PRIMARY KEY，
vehicle_type CHAR（3）DEFAULT''SUV''非空
CHECK（vehicle_type =''SUV''），
UNIQUE（vin，vehicle_type），
FOREIGN KEY（vin，vehicle_type）
参考车辆（vin，vehicle_type）
ON UPDATE CASCADE
ON DELETE CASCADE ，
..）;
CREATE TABLE轿车
（vin CHAR（17）NOT NULL PRIMARY KEY，
vehicle_type CHAR（3）DEFAULT''SED''非空
CHECK（vehicle_type =''SED''），
UNIQUE（vin，vehicle_type ），
FOREIGN KEY（vin，vehicle_type）
参考车辆（vin，vehicle_type）
ON UPDATE CASCADE
ON DELETE CASCADE，
..）;
我可以继续构建这样的层次结构。例如，如果我有一个分为双门和四门轿车的
a轿车表，我可以用这样的架构来表示：
CREATE TABLE轿车
（vin CHAR（17）NOT NULL PRIMARY KEY，
vehicle_type CHAR（3）DEFAULT''SED''NOT NULL
CHECK（vehicle_type IN（''2DR''，''4DR''，''SED''），
UNIQUE（vin，vehicle_type），
FOREIGN KEY（vin，vehicle_type）
参考车辆（vin，vehicle_type）
ON UPDATE CASCADE
ON DELETE CASCADE，
..）;
CREATE TABLE TwoDoor
（vin CHAR（17）NOT NULL PRIMARY KEY，
vehicle_type CHAR（3）DEFAULT''2DR''NOT NULL
CHECK（vehicle_type ='''2DR''），
UNIQUE（vin，vehicle_type） ，
FOREIGN KEY（vin，vehicle_type）
参考轿车（vin，vehicle_type）
ON UPDATE CASCADE
ON DELETE CASCADE，
..）;
CREATE TABLE FourDoor
（vin CHAR（17）NOT NULL PRIMARY KEY，
vehicle_type CHAR（3）DEFAULT''4DR''NOT NULL
CHECK（vehicle_type =''4DR''），
UNIQUE（vin，vehicle_type），
FOREIGN KEY（vin，vehicle_type）
REFERENCES轿车（vin，vehicle_type）
ON UPDATE CASCADE
ON DELETE CASCADE，
..）;
我们的想法是在UNIQUE（）
约束中构建一系列标识符和类型，当您使用REFERENCES约束时，该约束会出现在树上。
显然，你可以做这个技巧的变种来获得不同的类
结构。
如果一个实体不必是专属的一个子类型，你玩
类层次结构的根：
CREATE TABLE车辆
（vin CHAR（17） NOT NULL，
vehicle_type CHAR（3）NOT NULL
CHECK（vehicle_type IN（''SUV''，''SED''）），
PRIMARY KEY（vin，vehicle_type），
..）;
现在立即开始在VIEW中隐藏所有这些内容并添加一个
INSTEAD OF触发这些视图。
Here is the link on Amazon.com for my new book on "Trees & Hierarchies
in SQL"
http://www.amazon.com/exec/obidos/tg...roduct-details
Separate the tree structure from the nodes. Ilike the nested sets
model for the structure, but you can pick whatever works best for your
situation. Then the nodes can go into another table.
The classic scenario calls for a root class with all the common
attributes and then specialized sub-classes under it. As an example,
let''s take the class of Vehicles and find an industry standard
identifier (VIN), and add two mutually exclusive sub-classes, Sport
utility vehicles and sedans (''SUV'', ''SED'').
CREATE TABLE Vehicles
(vin CHAR(17) NOT NULL PRIMARY KEY,
vehicle_type CHAR(3) NOT NULL
CHECK(vehicle_type IN (''SUV'', ''SED'')),
UNIQUE (vin, vehicle_type),
..);
Notice the overlapping candidate keys. I then use a compound candidate
key (vin, vehicle_type) and a constraint in each sub-class table to
assure that the vehicle_type is locked and agrees with the Vehicles
table. Add some DRI actions and you are done:
CREATE TABLE SUV
(vin CHAR(17) NOT NULL PRIMARY KEY,
vehicle_type CHAR(3) DEFAULT ''SUV'' NOT NULL
CHECK(vehicle_type = ''SUV''),
UNIQUE (vin, vehicle_type),
FOREIGN KEY (vin, vehicle_type)
REFERENCES Vehicles(vin, vehicle_type)
ON UPDATE CASCADE
ON DELETE CASCADE,
..);
CREATE TABLE Sedans
(vin CHAR(17) NOT NULL PRIMARY KEY,
vehicle_type CHAR(3) DEFAULT ''SED'' NOT NULL
CHECK(vehicle_type = ''SED''),
UNIQUE (vin, vehicle_type),
FOREIGN KEY (vin, vehicle_type)
REFERENCES Vehicles(vin, vehicle_type)
ON UPDATE CASCADE
ON DELETE CASCADE,
..);
I can continue to build a hierarchy like this. For example, if I had
a Sedans table that broke down into two-door and four-door sedans, I
could a schema like this:
CREATE TABLE Sedans
(vin CHAR(17) NOT NULL PRIMARY KEY,
vehicle_type CHAR(3) DEFAULT ''SED'' NOT NULL
CHECK(vehicle_type IN (''2DR'', ''4DR'', ''SED'')),
UNIQUE (vin, vehicle_type),
FOREIGN KEY (vin, vehicle_type)
REFERENCES Vehicles(vin, vehicle_type)
ON UPDATE CASCADE
ON DELETE CASCADE,
..);
CREATE TABLE TwoDoor
(vin CHAR(17) NOT NULL PRIMARY KEY,
vehicle_type CHAR(3) DEFAULT ''2DR'' NOT NULL
CHECK(vehicle_type = ''2DR''),
UNIQUE (vin, vehicle_type),
FOREIGN KEY (vin, vehicle_type)
REFERENCES Sedans(vin, vehicle_type)
ON UPDATE CASCADE
ON DELETE CASCADE,
..);
CREATE TABLE FourDoor
(vin CHAR(17) NOT NULL PRIMARY KEY,
vehicle_type CHAR(3) DEFAULT ''4DR'' NOT NULL
CHECK(vehicle_type = ''4DR''),
UNIQUE (vin, vehicle_type),
FOREIGN KEY (vin, vehicle_type)
REFERENCES Sedans (vin, vehicle_type)
ON UPDATE CASCADE
ON DELETE CASCADE,
..);
The idea is to build a chain of identifiers and types in a UNIQUE()
constraint that go up the tree when you use a REFERENCES constraint.
Obviously, you can do variants of this trick to get different class
structures.
If an entity doesn''t have to be exclusively one subtype, you play with
the root of the class hierarchy:
CREATE TABLE Vehicles
(vin CHAR(17) NOT NULL,
vehicle_type CHAR(3) NOT NULL
CHECK(vehicle_type IN (''SUV'', ''SED'')),
PRIMARY KEY (vin, vehicle_type),
..);
Now start hiding all this stuff in VIEWs immediately and add an
INSTEAD OF trigger to those VIEWs.
这篇关于消除组合关系乘法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
