sql - 如何为 M :N relationship 优化数据库模型

Question

2013 年 4 月 10 日编辑

为了让自己清楚，我添加了另一个(简化的)示例，展示了我想要实现的原理:

T1 - PERSONHAS                T2 - PRODUCTNEED
ANTON    has    WHEEL         CAR      need    ENGINE
ANTON    has    ENGINE        CAR      need    WHEEL
ANTON    has    NEEDLE        SHIRT    need    NEEDLE
BERTA    has    NEEDLE        SHIRT    need    THREAD
BERTA    has    THREAD        JAM      need    FRUIT
BERTA    has    ENGINE        JAM      need    SUGAR

Q3 - PERSONCANMAKE
ANTON    canmake    CAR
BERTA    canmake    SHIRT

Q4 - PERSONCANNOTMAKE
ANTON    cannotmake    SHIRT
ANTON    cannotmake    FRUIT
BERTA    cannotmake    CAR
BERTA    cannotmake    FRUIT

我有 T1 和 T2，想为 Q3 和 Q4 创建查询

2013 年 4 月 10 日结束编辑

前言:

为了创建产品 (P)，我需要具备某些通用能力(C - 如工厂、供应、电力、水等)
产品经理定义了创建他/她的产品所需的所有通用功能。

在一个位置 (L) 我有一定的通用能力 (C)
位置经理定义他/她的位置能够提供的能力。这可能是明确的"is"、“明确的否”，或者位置管理器根本没有列出某个功能。

数据库型号:

我创建了以下根实体

Location (PK: L) - values L1, L2, L3     // in real ca. 250 rows of L
Product (PK: P) - values P1, P2          // in real ca. 150 rows of P
Capability (PK: C) - values C1, C2, C3   // in real ca.  80 rows of C

以及以下子(依赖)实体

ProductCapabilityAssignment:P, C (PK: P, C, FK: P, C)
    P1 C1
    P1 C2
    P2 C1
    P2 C3

LocationCapabilityAssignment: L, C, Status (Y/N) (PK: L, C, FK: L, C)
    L1 C1 Y
    L2 C1 Y
    L2 C2 Y
    L2 C3 N
    L3 C1 Y
    L3 C2 Y
    L3 C3 Y

任务:

任务是找出某个产品是否可以在某个地点生产，因此为该产品定义的所有能力都必须在该地点出现。为了回答这个问题，我忍不住

创建位置和 ProductCapabilityAssignment (CL_Cart) 的笛卡尔积，以确保对于每个位置，我都列出了所有可能的产品及其 cpability 需求

CREATE VIEW CL_Cart AS
SELECT L.L, PCA.P, PCA.C
FROM Location AS L, ProductCapabilityAssignment AS PCA;

在 CL_Cart 和 LocationCapabilityAssignment 之间创建外部联接以匹配位置可以提供的所有功能

CREATE VIEW Can_Produce AS
SELECT X.L, X.P, X.C, LCA.Status
FROM CL_CArt AS X LEFT JOIN LocationCapabilityAssignment AS LCA ON (X.C = LCA.C) AND (X.L = LCA.L);

所以最后我得到

SELECT L, P, C, Status
FROM Can_Produce;
    L1 P1 C1 Y
    L1 P1 C2 NULL        // C2 not listed for L1
    L1 P2 C1 Y
    L1 P2 C3 NULL        // C3 not listed for L1
    L2 P1 C1 Y
    L2 P1 C2 Y
    L2 P2 C1 Y
    L2 P2 C3 N           // C3 listed as "No" for L2
    L3 P1 C1 Y
    L3 P1 C2 Y
    L3 P2 C1 Y
    L3 P2 C3 Y

这意味着L1既不能产生P1也不能产生P2，L2可以产生P1，而L3可以产生P1和P2。

所以我可以查询 Can_Produce对于特定的产品/位置，看看我有什么和我没有的能力。我还可以通过检查 Status="N" OR Status is NULL 来提供一个快捷的整体是/否答案。 - 如果是，则无法生产该产品。

问题:

对于像 MSSQL、MySQL、Oracle 这样的关系数据库(尚未决定且超出我的影响)，我想知道我是否为这种 M:N 关系选择了正确的数据模型，或者我是否可以做得更好。特别是我害怕与ca。 250 个位置、150 个产品和一个平均由 +/- 10 个功能定义的产品，因此可以说 375.000 行的笛卡尔积，由于巨大的内存消耗，该性能将崩溃。

我也很想避免存储过程。

任何想法都会受到欢迎。

Answer 1

     --Environment Variables    
Declare @Parent table (id int identity(1,1) primary key, Name varchar(20))  
Declare @Components table (id int identity(1,1) primary key, Name varchar(20))  Insert into @Components (Name) values ('Engine'),('Wheel'),('Chassis'),('NEEDLE'),('THREAD'),('FRUIT'),('SUGAR')
Declare @Objects table (id int identity(1,1) primary key, Name varchar(20)) 
Declare @Template table (id int identity(1,1) primary key, Name varchar(20), ObjectID int, ComponentID int) 
Insert into @Template (Name, ObjectID, ComponentID) 
Select 'Vehicle', O.ID, C.ID from @Objects O, @Components C where O.Name = 'Car' and C.Name in ('Engine','Wheel','Chassis')union    
Select 'Clothing', O.ID, C.ID from @Objects O, @Components C where O.Name = 'Shirt' and C.Name in ('Needle','Thread') union 
Select 'Confectionary', O.ID, C.ID from @Objects O, @Components C where O.Name = 'JAM' and C.Name in ('FRUIT','SUGAR')  
Declare @AvailableMaterials table (id int identity(1,1) primary key, TestType varchar(20), ParentID int, ComponentID int)   

--Test Data 
Insert into @AvailableMaterials (TestType,ParentID,ComponentID) 
Select 'CompleteSet', P.ID, T.ComponentID from @Parent P, @Template T where P.Name = 'Driver' and T.Objectid = (Select ID from @Objects where Name = 'Car') union   
Select 'CompleteSet', P.ID,  T.ComponentID from @Parent P, @Template T where P.Name = 'Seamstress' and T.Objectid = (Select ID from @Objects where Name = 'Shirt') union    
Select 'IncompleteSet', P.ID,  T.ComponentID from @Parent P, @Template T where P.Name = 'Confectionarist' and T.Objectid = (Select ID from @Objects where Name = 'Jam')     
and T.ComponentID not in (Select ID from @Components where Name = 'FRUIT')  




--/*What sets are incomplete?*/ 
Select *    
from @AvailableMaterials    
where ID in (   
Select  SRCData.ID  
from @AvailableMaterials SRCData cross apply (Select ObjectID from @Template T where ComponentID = SRCData.ComponentID) ObjectsMatchingComponents   
inner join @Template T  
on SRCData.ComponentID = T.ComponentID  
and T.ObjectID = ObjectsMatchingComponents.ObjectID 
cross apply (Select ObjectID, ComponentID from @Template FullTemplate where FullTemplate.ObjectID = T.ObjectID and FullTemplate.ComponentID not in (Select ComponentID from @AvailableMaterials SRC where SRC.ComponentID = FullTemplate.ComponentID)) FullTemplate 
)   


/*What sets are complete?*/ 
Select *    
from @AvailableMaterials    
where ID not in (   
Select  SRCData.ID  
from @AvailableMaterials SRCData cross apply (Select ObjectID from @Template T where ComponentID = SRCData.ComponentID) ObjectsMatchingComponents   
inner join @Template T  
on SRCData.ComponentID = T.ComponentID  
and T.ObjectID = ObjectsMatchingComponents.ObjectID 
cross apply (Select ObjectID, ComponentID from @Template FullTemplate where FullTemplate.ObjectID = T.ObjectID and FullTemplate.ComponentID not in (Select ComponentID from @AvailableMaterials SRC where SRC.ComponentID = FullTemplate.ComponentID)) FullTemplate 
)   

嗨

这是我能想到的最好的方法......它的前提是你必须知道完整的集合是什么，知道缺少什么。一旦你有了缺失的东西，你就可以从不完整的集合中分辨出完整的集合。

我怀疑这个解决方案能否很好地扩展，即使移动到带有索引的#tables。可能虽然...

我也有兴趣看到更清洁的方法。上述解决方案是在 SQL 2012 版本中开发的。注意交叉应用，这在某种程度上限制了笛卡尔效应。

希望这会有所帮助。