Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
1  
Here is an example. –  Nick Chammas Mar 25 '12 at 16:46
    
Nice clear, concise example of the 'Is-A' relationship, thank you. –  JKomusin Mar 25 '12 at 19:13

2 Answers 2

up vote 1 down vote accepted

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.

share|improve this answer
    
Why a trigger is suggested? And not make the ParentItemID reference the PortfolioSubType (instead of the PortfolioItem)? –  ypercube Mar 25 '12 at 0:19
    
@ypercube - that would work too, and might be a bit simpler. –  ConcernedOfTunbridgeWells Mar 25 '12 at 0:33
    
Interesting solution, though I'm using MySQL (my fault for not mentioning) so CHECK constraints are an issue, but they could be fixed through triggers. I like the compound keys between type and id though. Seems like I was on the right track. Thanks! –  JKomusin Mar 25 '12 at 19:37
    
@JKomusin - ypercube's suggestion to structure it so the parent-child relationship is modelled via the portfolio sub type might save you the trigger, although you'd get a potentially circular FK dependency. Maybe worth considering if you're using MySQL. Not sure why, but for some reason I had the impression you were using SQL Server. –  ConcernedOfTunbridgeWells Mar 26 '12 at 9:33

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));
share|improve this answer
    
+1 for Polymorphic Association, though I suspect your solution (while perhaps more concise) is very similar to Tunbridge's above. However, why is SubPortfolio necessary? I'm assuming my nesting structure may be infinitely deep, so being able to reuse a single Portfolio table and single Project table would be ideal. –  JKomusin Mar 25 '12 at 19:33
    
@JKomusin: I probably misunderstood relation between sub-portfolio and portfolio (I thought they are quite different entities ), my answer is more about general approach to refactor 1-to-either to regular one-to-many relationship. As to your question, you might want to store portfolios(surely if it makes sense for your task) as a tree, so your will have just 2 tables, portfolio and project –  a1ex07 Mar 25 '12 at 19:42
    
+1 noting that for mySQL your CHECK constraints need to be implemented using triggers. –  onedaywhen Mar 26 '12 at 10:21
    
I believe for MySQL (in this case) enum can also work as check constraint substitution . –  a1ex07 Mar 26 '12 at 13:38

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.