Best MySQL Table Structure: 2 Parents, 1 Child

Question

I have two parent tables, BusinessGroup and SocialGroup, and one child table, Members. A Member can belong to either parent, but not both.

As far as I can see, there are two options for constructing the child table.

Opt 1: Include a field for ParentType, and another for ParentID. The ParentType would be an enum (Business, Social) and the ParentID would be the PK from the respective parent table.

Opt 2: Include a field for BusinessGroupID, and another for SocialGroupID. In this case, the fields would need to be nullable, and only one could contain a value.

Any ideas on which approach is best?

I tried option 1 in MySQL, and created two foreign keys from the child back to the parents. I ran into trouble when inserting values though, since MySQL was expecting a corresponding value in BOTH parent tables.

As a supplementary question: how do things change if I have a larger number of parents, e.g. 6?

Thanks!

Note: Option #2 includes what is called an exclusive arc, and is generally regarded as bad practice. Please see these two questions they may assist you: http://stackoverflow.com/questions/987654/in-a-stackoverflow-clone-what-relationship-should-a-comments-table-have-to-quest/987685#987685 http://stackoverflow.com/questions/621884/database-development-mistakes-made-by-appdevelopers — Nathan Koop, Jul 21 '10 at 03:36

Damir Sudarevic · Accepted Answer · 2016-08-13T16:56:57.960

CREATE TABLE Group ( 
    GroupID       integer    NOT NULL
  , Name          varchar(18)
  , Description   varchar(18)
  , GroupType     varchar(4) NOT NULL
  -- all columns common to any group type
);
ALTER TABLE Group ADD CONSTRAINT pk_Group PRIMARY KEY (GroupID) ;


CREATE TABLE BusinessGroup ( 
    GroupID   integer NOT NULL
  -- all columns specific to business groups
);
ALTER TABLE BusinessGroup
    ADD CONSTRAINT pk_BusinessGroup  PRIMARY KEY (GroupID)
  , ADD CONSTRAINT fk1_BusinessGroup FOREIGN KEY (GroupID) REFERENCES Group(GroupID) ;


CREATE TABLE SocialGroup ( 
    GroupID    integer NOT NULL
  -- all columns specific to social groups
);
ALTER TABLE SocialGroup
    ADD CONSTRAINT pk_SocialGroup  PRIMARY KEY (GroupID)
  , ADD CONSTRAINT fk1_SocialGroup FOREIGN KEY (GroupID) REFERENCES Group(GroupID) ;


CREATE TABLE Person ( 
    PersonID  integer NOT NULL
  , GroupID   integer NOT NULL
);
ALTER TABLE Person
    ADD CONSTRAINT pk_Person  PRIMARY KEY (PersonID)
  , ADD CONSTRAINT fk1_Person FOREIGN KEY (GroupID) REFERENCES Group(GroupID) ;

Thanks Damir, much appreciated. For anyone else looking for an explanation, have a look at this excellent slideshow from Bill Karwin http://www.slideshare.net/billkarwin/sql-antipatterns-strike-back — DatsunBing, Jul 21 '10 at 23:21

score 1 · Answer 2 · answered Jul 21 '10 at 03:13

"parent tables" is probably a misnomer - in the relational model I'd flip your relationship. e.g. make members the master records table, and a join table that allows 1-1 mapping, so have a PK of member_id or whatever the right field is, and map to the FK in either BusinessGroup or SocialGroup.

Best MySQL Table Structure: 2 Parents, 1 Child

2 Answers2

Linked