几乎一棵树的数据库架构 [英] Database Schema for an almost tree

问题描述

我具有以下结构：

Block A
  Foo 1
    Bar 1
    Bar 2
  Foo 2
    Bar 1
    Bar 3
  Bar 4

Block B
  Foo 3

• 每个Foo都完全属于Block。


• 每个酒吧都完全属于Block。


• 酒吧不能属于同一酒吧的一个或多个Foo 。



模式当前如下：

    Block
   1/   \1
  n/     \n
Foo-n---m-Bar

问题这样做是因为可以有Bar的
属于不同Block的Foo的

The problem with this is that there can be Bar's which belong to Foo's of different Block's

是否存在既没有冗余也没有
的架构允许的不一致之处？

Is there a schema which has neither redundancy nor allows inconsistencies?

推荐答案

是的，有一种方法。在关联表中包括 Block 的主键列，并使用它来扩展外键约束：

Yes, there is a way. Include the primary key column of Block into the association table and use it to extend the foreign key constraints:

CREATE TABLE Blocks
  ( BlockID INT 
  , PRIMARY KEY (BlockID)
  ) ;

CREATE TABLE Foos
  ( BlockID INT
  , FooID INT
  , PRIMARY KEY (FooID)
  , FOREIGN KEY (BlockID)
      REFERENCES Blocks (BlockID)
  , UNIQUE (BlockID, FooID)              -- the Unique constraints are needed for 
  ) ;

CREATE TABLE Bars
  ( BlockID INT
  , BarID INT
  , PRIMARY KEY (BarID)
  , FOREIGN KEY (BlockID)
      REFERENCES Blocks (BlockID)
  , UNIQUE (BlockID, BarID)              -- the composite FKs below
  ) ;

CREATE TABLE Foo_Bars                    -- the m:n association tabletable
  ( BlockID INT
  , FooID INT
  , BarID INT
  , PRIMARY KEY (FooID, BarID)
  , FOREIGN KEY (BlockID, FooID)         -- composite FK constraints to Foos
      REFERENCES Foos (BlockID, FooID)
  , FOREIGN KEY (BlockID, BarID)         -- and Bars
      REFERENCES Bars (BlockID, BarID)
  ) ;

这篇关于几乎一棵树的数据库架构的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！