How should an object that can reference an object of multiple types be modeled in a relational schema?

Question

Apologies for the obscure question, it may make more sense with a concrete example:

In my application, I may create a portfolio that contains a set of projects. However, I may also add a 'sub-portfolio' nested within a portfolio (which then may contain projects). Currently, I have my schema drawn out the following way:

+------------+    +-----------------------+    +------------+
| Portfolios |    | Portfolio_project_map |    | Projects   |
+------------+    +-----------------------+    +------------+
|   port_id  |    |   parent_id           |    |   proj_id  |
+------------+    +-----------------------+    +------------+
|            |    |   child_id            |    |            |
                  +-----------------------+
                  |   child_is_portfolio  |
                  +-----------------------+

This seems extremely ugly. What is the proper way of modeling such a relationship in a relational database? Or is there really not one, and such details should be closely enforced in the business logic? Is it even possible to specify foreign key constraints on the Portfolio_project_map such that child_id references either port_id or proj_id?

Also, what exactly is such a problem called? I found 'one-to-either' referenced in a couple places, but I have a feeling that's not completely correct?

score 3 · Answer 1 · answered Mar 25 '12 at 15:41

If my memory serves me well, 'One to either' is called Polymorphic Association. Usually, it can be resolved by using a common parent table (index definitions are skipped for simplicity):

PortfolioParent (id int not null PRIMARY KEY,
portfolio_type char(1) not null,
CONSTRAINT UQ_PORTFOLIO_PARENT UNIQUE (id,portfolio_type),
-- unique constraint may seem redundant, but it's required so other tables can refer to 
-- (id,portfolio_type)
CONSTRAINT CHK_PORTFOLIO_PARENT_TYPE CHECK portfolio_type IN ('P','S')) --'P' - portfolio,'S'-subportfolio

); 

Portfolio(id int not null PRIMARY KEY,
parent_id int not null,
portfolio_type char(1) not null,
--portfolio attributes
CONSTRAINT CHK_PORTFOLIO_TYPE CHECK (portfolio_type ='P'),
CONSRTRAINT FK_PORTFOLIO_PORT_PARENT FOREIGN KEY (parent_id,portfolio_type) 
  REFERENCES PortfolioParent(id,portfolio_type));

SubPortfolio (id int not null PRIMARY KEY,
parent_id int not null,
portfolio_type char(1) not null,
-- sub-portfolio attributes, portfolio_id
CONSTRAINT CHK_SUBPORTFOLIO_TYPE CHECK (portfolio_type ='S'),
CONSRTRAINT FK_SUBPORTFOLIO_PORT_PARENT FOREIGN KEY (parent_id,portfolio_type)
   REFERENCES  PortfolioParent(id,portfolio_type));

Project(id int not null PRIMARY KEY,
parent_id int not null,
--project attributes
CONSTRAINT FK_Project_PARENT FOREIGN KEY(parent_id) REFERENCES PortfolioParent(id));

ConcernedOfTunbridgeWells · Accepted Answer · 2012-03-24T17:20:04.273

If the relationship is strictly hierarchical (i.e. a portfolio can have several sub-portfolios or projects, but a project cannot appear in more than one sub-portfolio) then you can model portfolio and project using a subtype pattern, e.g.

-- === PortfolioItem  table ===========================================
-- Supertype table that records the relationships between portfolio 
-- items.
create table PortfolioItem (
       PortfolioItemID      int not null
      ,PortfolioItemRef     varchar (20) not null 
      ,PortfolioItemType    varchar (10) not null -- either 'PROJ' or 'PORT'
      ,ParentItemID         int -- Null for root
)
go

alter table PortFolioItem
  add constraint PK_PortfolioItem
      primary key nonclustered (PortfolioItemID)
go

alter table PortfolioItem
  add constraint UQ_PortfolioItem
      unique nonclustered (PortfolioItemRef, PortfolioItemType)
go


-- Parent-child relationship
--
alter table PortfolioItem
  add constraint FK_PortfolioItem_Parent
      foreign key (ParentItemID)
      references PortfolioItem
go



-- === PortfolioSubType ===============================================
-- This subclass table joins against the unique identifier but the 
-- check constraint restricts it to joining against 'portfolio' nodes
--
create table PortfolioSubType (
       PortfolioItemRef      varchar (20) not null
      ,PortfolioItemType     varchar (10) not null
      -- Portfolio attributes
)

alter table PortfolioSubType
  add constraint PK_PortfolioSubType
      primary key nonclustered (PortfolioItemRef, PortfolioItemType)
go

-- Cab only join against portfolio parent items
--  
alter table PortfolioSubType
  add constraint CK_Portfolio_Type
      check (PortfolioItemType = 'PORT')    
go

alter table PortfolioSubType
  add constraint FK_PortfolioSubType_SuperType
      foreign key (PortfolioItemRef, PortfolioItemType)
      references PortfolioItem (PortfolioItemRef, PortfolioItemType)
go


-- === ProjectSubType =================================================
-- This subclass table has the project specific items and a check 
-- constraint that prevents it from joining against parent nodes
-- that represent portfolios
--
create table ProjectSubType (
       PortfolioItemRef      varchar (20) not null
      ,PortfolioItemType     varchar (10) not null
      -- Project attributes
)

alter table ProjectSubType
  add constraint PK_ProjectSubType
      primary key nonclustered (PortfolioItemRef, PortfolioItemType)
go

-- Check constraint restricts this to projects
--
alter table ProjectSubType
  add constraint CK_Portfolio_Type
      check (PortfolioItemType = 'PROJ')    
go

alter table ProjectSubType
  add constraint FK_ProjectSubType_SuperType
      foreign key (PortfolioItemRef, PortfolioItemType)
      references PortfolioItem (PortfolioItemRef, PortfolioItemType)
go

You could enforce an integrity rule that prevents a project node from having children with a trigger similar to the following:

-- === Trigger to enforce integrity ===================================
-- The trigger prevents project nodes from having children.
--
create trigger ProjectNodeIntegrity 
    on PortfolioItem
   for insert, update
as
    if exists 
        (select 1
           from PortfolioItem p_i
           join inserted i
             on i.ParentItemID = p_i.PortfolioItemID
            and p_i.PortfolioItemType = 'PROJ') begin
        raiserror ('Only portfolios may have children', 16, 1)
        rollback transaction
        return
    end
go

This will bounce attempts to insert a child under a project node.

How should an object that can reference an object of multiple types be modeled in a relational schema?

2 Answers2