mysql父母表和子女表合并,最佳MySQL表结构：2个父母，1个孩子

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!

解决方案

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