我最近遇到一个项目,我想在数据库中添加一些应用程序。每个应用程序都有1:1、1:n或n:m关系的附加信息。虽然我知道如何存储这种关系,但我在每个应用程序的开发人员/发布者方面都遇到了一些麻烦。
情况:
- 数千个应用程序
- 每个应用程序都有自己的id
- 几千家公司
- 每家公司(开发者/发行商(都有自己的id
- 每个应用程序可以有0个、1个或多个开发人员
- 每个应用程序可以有0个、1个或多个发布者
- 每个开发人员可以有一个或多个应用程序
- 每个发布者可以有一个或多个应用程序
很明显,这是一种多对多的关系,因此需要一个连接表。不幸的是,至少有两种可行的选择。
公司
CREATE TABLE `company` (
`id` smallint(5) UNSIGNED NOT NULL,
`name` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
(我在这个表中合并了开发者和发布者,因为开发者也可以是发布者,反之亦然。我认为这比在两个单独的表中有冗余要好,不是吗?(
选项1:
第一种选择是创建两个单独的表。
应用程序开发程序
CREATE TABLE `app_developer` (
`id` mediumint(8) UNSIGNED NOT NULL,
`app_id` mediumint(8) UNSIGNED NOT NULL,
`company_id` mediumint(8) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
app_publisher
CREATE TABLE `app_publisher` (
`id` mediumint(8) UNSIGNED NOT NULL,
`app_id` mediumint(8) UNSIGNED NOT NULL,
`company_id` mediumint(8) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
选项2:
第二种选择是创建一个表,并为每个应用程序/公司组合添加标志(0/1(。
CREATE TABLE `app_company_rel` (
`id` mediumint(8) UNSIGNED NOT NULL,
`app_id` mediumint(8) UNSIGNED NOT NULL,
`company_id` mediumint(8) UNSIGNED NOT NULL,
`developer` tinyint(1) UNSIGNED NOT NULL,
`publisher` tinyint(1) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
我不知道将来是否会要求搜索特定开发者/发行商的所有应用程序,或者这只是一个没有进一步目的的附加信息。
哪种选择会更好(在一致性、冗余性、性能方面(,还是没有显著差异?
你的第一个选择会非常有效。存储在两个不同的表中实际上是很好的。创建两个表,并使用app_id作为外键。存储在两个不同的表中可以使数据非常清晰,数据检索也将非常容易和快速。如果有任何疑问,请告诉我,我会清楚地向您解释
选项3:类似于#2,但带有用于dev和pub的ENUM
或SET
。
我会考虑备选方案1或备选方案3。但对于简单的多对多映射表,我不会包含id
;它会减慢速度。
关于如何编写最佳多对多表的更多讨论和提示。
您的第二个选项正朝着正确的方向发展。但这不仅建立了公司和项目之间的关系,还建立了关系类型——开发人员或发布者。
create table ProjCompany(
ProjID int not null references Projects( ID ),
CompanyID int not null references Company( ID ),
TypeID char( 1 ) not null references Types( ID ), -- 'D' or 'P'
constraint OK_ProjCompany primary key( ProjID, CompanyID, TypeID )
);
一个项目可以让每个公司只作为开发者列出一次,但同一家公司也可以作为出版商出现。一家公司可以是任意数量项目的开发人员和/或发布者。
如果任何表需要对特定项目的特定开发人员的FK引用,它将使用项目id、公司id和开发人员标志来引用此表。如果该公司没有被定义为该项目的开发商,那么该推荐信将被拒绝。
此外,我建议使用一个显示每个项目及其开发人员的视图,以及一个显示各个项目及其发布者的视图。这对于只与开发人员或发行商合作的代码部分来说会很有用。