最佳MySQL表结构:2个父母,1个孩子 [英] Best MySQL Table Structure: 2 Parents, 1 Child
问题描述
据我所见,构建子表有两个选项。
选择1:为ParentType包含一个字段,另一个为ParentID。 ParentType将是一个枚举(Business,Social),ParentID将是相应父表中的PK。
选项2:包含BusinessGroupID的字段,另一个用于SocialGroupID。在这种情况下,这些字段将需要为空,只有一个可以包含一个值。
任何想法最适合哪种方法?
我在MySQL中尝试了选项1,并从孩子创建了两个外键到父母。在插入值时遇到麻烦,因为MySQL期望在BOTH父表中有相应的值。
作为补充问题:如果我有一个更大的父母的数量,例如6?
谢谢!
CREATE TABLE组(
GroupID整数NOT NULL
,名称varchar(18)
,描述varchar(18)
,GroupType varchar(4)NOT NULL
- 任何组类型通用的所有列
);
ALTER TABLE组ADD CONSTRAINT pk_Group PRIMARY KEY(GroupID);
CREATE TABLE BusinessGroup(
GroupID integer NOT NULL
- 专门针对业务组的所有列
);
ALTER TABLE BusinessGroup
ADD CONSTRAINT pk_BusinessGroup PRIMARY KEY(GroupID)
,ADD CONSTRAINT fk1_BusinessGroup FOREIGN KEY(GroupID)参考组(GroupID);
CREATE TABLE SocialGroup(
GroupID integer NOT NULL
- 社会组特定的所有列
);
ALTER TABLE SocialGroup
ADD CONSTRAINT pk_SocialGroup PRIMARY KEY(GroupID)
,ADD CONSTRAINT fk1_SocialGroup FOREIGN KEY(GroupID)参考组(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)参考组(GroupID);
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) ;
这篇关于最佳MySQL表结构:2个父母,1个孩子的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!