Database Schema for an almost tree

Question

I have the following structure:

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

Block B
  Foo 3

• Every Foo belongs to exactly on Block.
• Every Bar belongs to exactly on Block.
• A Bar can belong to none, one or more Foo's of the same Block.

The schema is currently like this:

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

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?

Answer 1

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)
  ) ;