一般情景描述
该数据库旨在保留有关使用塞尔达系列的各种人员的数据.我想跟踪可以玩游戏的控制台,参与游戏开发的员工,员工的工作(许多员工在多个游戏中从事不同的工作)等.
商业规则
>多名员工可以参与多个游戏.
>多个游戏可以在同一个控制台上.
>多个控制台可以成为同一个游戏的平台.
>多个员工可以拥有相同的工作.
>员工可以拥有多个职位.
>游戏可以有多个员工.
>游戏可以在其开发中拥有多种类型的作业
>多个游戏可以附加相同类型的作业.
>控制台可以有多个人在上面工作.
>一个人可以在多个控制台上工作.
>员工姓名,可以拆分为第一个和最后一个(例如“John”和“Doe”)
>游戏标题(例如“时间之笛”)
>职称(例如“Level Design”,“Director”,“Composure”,“Level Designer”,“Programmer”,“Localization”等).
>控制台名称(例如“Game Boy Advance”)
问题
到目前为止,似乎无论我设计什么,数据冗余和M：N关系到处都是感兴趣的实体类型.但是我觉得数据库设计者必须一直遇到这种问题,所以必须有一个解决方案.
注意：我能够找到填充表格的数据,问题是将其组织成一个数据库,其中包含规范化形式的表格.
解决方法
通过这种方式,数据库建模练习的目标应该是高精度地反映所关注的商业环境的重要特征;因此,如果你正确地识别出有许多M：N关联,那么你必须在(a)概念模式中以及(b)相应的逻辑级声明中表达它们,无论它有多少个连接 – 或任何必须解决其他类型的基数问题.
商业规则
您提供了一个语境良好的问题,并且还澄清了您正在处理的数据库纯粹是低调的,这是一个重要的观点,因为我认为像正在考虑的业务领域的“真实世界”案例将是更广泛的.
我决定(1)对你提供的业务规则进行一些修改和扩展,以便(2)产生一个更具描述性的概念模式 – 尽管还是假设的 – .以下是我放在一起的一些配方：
> Party1是个人或组织
>一个Party按照一个PartyType进行分类
> PartyType对零一个或多个缔约方进行分类
>一个组织开发零一个或多个产品
>产品是系统或游戏
>产品按照一种ProductType进行分类
>系统由一个SystemType编目
>游戏可以通过一对多系统播放
>系统用于玩一对多游戏
>游戏按零一个或多个流派分类
>类型分类零一或多游戏
>产品发起一对多作业
>工作由零一人或多人完成,他们扮演协作者的角色
>一个人是零一对多作业的合作者
1缔约方在提及组成单一实体的个人或一组个人时,在法律背景中使用的术语,因此该面额适合代表人和组织.
IDEF1X图
随后,我创建了Figure 1中显示的IDEF1X2图表(确保单击链接以更高的分辨率查看它),在单个图形设备中合并上面提供的业务规则(以及其他一些看起来相关的规则)：
2信息建模的集成定义(IDEF1X)是一种非常值得推荐的数据建模技术,由美国国家标准与技术研究院(NIST)于1993年12月作为标准建立.它完全基于(a)由关系模型的发起者撰写的早期理论材料,即E. F. Codd博士; (b)Dr. P. P. Chen entity-relationship开发的数据视图;以及(c)由Robert G. Brown创建的逻辑数据库设计技术.
如您所见,我通过相应的关联实体类型仅描绘了三个M：N关联,即：
>合作者,
> SystemGame和
> GameGenre.
在其他方面,有两种不同的超类型 – 子类型结构,其中：
>人与组织是党的互斥实体子类型,其实体超类型;和
> Product是System和Game的超类型,它们是互斥的子类型.
如果您不熟悉超类型 – 子类型关联,您可能会找到帮助,例如,我对问题的答案
>“Modeling a scenario in which each Music Artist is either a Group or a Solo Performer”,
>“How to model an entity type that can have different sets of attributes?”,
>“How should I model a funds transfers business domain?”,和
>“Modelling a database structure for multiple user types and their contact information”.
说明性的逻辑sql-DDL布局
接下来,我们必须确保在逻辑层面：
>每个实体类型由单个基表表示;
>适用实体类型的每个单一属性由特定列表示;
>为每个列固定一个确切的数据类型,以确保它包含的所有值都属于特定且定义良好的集合,无论是INT,DATETIME,CHAR等;和
>以声明方式配置多个约束,以保证所有表中保留的行形式的断言符合在概念级别确定的业务规则.
所以我根据之前显示的IDEF1X图宣布了以下DDL安排：
CREATE TABLE PartyType ( -- Stands for an independent entity type.
PartyTypeCode CHAR(1) NOT NULL,-- To retain 'P' or 'O'.
Name CHAR(30) NOT NULL,-- To keep 'Person' or 'Organization'.
--
CONSTRAINT PartyType_PK PRIMARY KEY (PartyTypeCode)
);
CREATE TABLE Party ( -- Represents an entity supertype.
PartyId INT NOT NULL,PartyTypeCode CHAR(1) NOT NULL,-- To hold the value that indicates the type of the row denoting the complementary subtype occurrence: either 'P' for 'Person' or 'O' for 'Organization'.
CreatedDateTime TIMESTAMP NOT NULL,--
CONSTRAINT Party_PK PRIMARY KEY (PartyId),CONSTRAINT PartyToPartyType_FK FOREIGN KEY (PartyTypeCode)
REFERENCES PartyType (PartyTypeCode)
);
CREATE TABLE Person ( -- Denotes an entity subtype.
PersonId INT NOT NULL,-- To be constrained as (a) the PRIMARY KEY and (b) a FOREIGN KEY.
FirstName CHAR(30) NOT NULL,LastName CHAR(30) NOT NULL,GenderCode CHAR(3) NOT NULL,BirthDate DATE NOT NULL,--
CONSTRAINT Person_PK PRIMARY KEY (PersonId),CONSTRAINT Person_AK UNIQUE (FirstName,LastName,GenderCode,BirthDate),-- Composite ALTERNATE KEY.
CONSTRAINT PersonToParty_FK FOREIGN KEY (PersonId)
REFERENCES Party (PartyId)
);
CREATE TABLE Organization ( -- Stands for an entity subtype.
OrganizationId INT NOT NULL,-- To be constrained as (a) the PRIMARY KEY and (b) a FOREIGN KEY.
Name CHAR(30) NOT NULL,FoundingDate DATE NOT NULL,--
CONSTRAINT Organization_PK PRIMARY KEY (OrganizationId),CONSTRAINT Organization_AK UNIQUE (Name),-- Single-column ALTERNATE KEY.
CONSTRAINT OrganizationToParty_FK FOREIGN KEY (OrganizationId)
REFERENCES Party (PartyId)
);
CREATE TABLE ProductType ( -- Represents an independent entity type.
ProductTypeCode CHAR(1) NOT NULL,-- To enclose the values 'S' and 'G' in the corresponding rows.
Name CHAR(30) NOT NULL,-- To comprise the values 'System' and 'Person' in the respective rows.
--
CONSTRAINT ProductType_PK PRIMARY KEY (ProductTypeCode)
);
CREATE TABLE Product ( -- Denotes an entity supertype.
OrganizationId INT NOT NULL,ProductNumber INT NOT NULL,ProductTypeCode CHAR(1) NOT NULL,-- To keep the value that indicates the type of the row denoting the complementary subtype occurrence: either 'S' for 'System' or 'G' for 'Game'.
CreatedDateTime DATETIME NOT NULL,--
CONSTRAINT Product_PK PRIMARY KEY (OrganizationId,ProductNumber),-- Composite PRIMARY KEY.
CONSTRAINT ProductToOrganization_FK FOREIGN KEY (OrganizationId)
REFERENCES Organization (OrganizationId),CONSTRAINT ProductToProductType_FK FOREIGN KEY (ProductTypeCode)
REFERENCES ProductType (ProductTypeCode)
);
CREATE TABLE SystemType ( -- Stands for an independent entity type.
SystemTypeCode CHAR(1) NOT NULL,Name CHAR(30) NOT NULL,--
CONSTRAINT SystemType_PK PRIMARY KEY (SystemTypeCode)
);
CREATE TABLE MySystem ( -- Represents a dependent entity type.
OrganizationId INT NOT NULL,-- To be constrained as (a) the PRIMARY KEY and (b) a FOREIGN KEY.
SystemNumber INT NOT NULL,SystemTypeCode CHAR(1) NOT NULL,ParticularColumn CHAR(30) NOT NULL,--
CONSTRAINT System_PK PRIMARY KEY (OrganizationId,SystemNumber),CONSTRAINT SystemToProduct_FK FOREIGN KEY (OrganizationId,SystemNumber)
REFERENCES Product (OrganizationId,CONSTRAINT SystemToSystemType_FK FOREIGN KEY (SystemTypeCode)
REFERENCES SystemType (SystemTypeCode)
);
CREATE TABLE Game ( -- Denotes an entity subtype.
OrganizationId INT NOT NULL,-- To be constrained as (a) the PRIMARY KEY and (b) a FOREIGN KEY.
GameNumber INT NOT NULL,SpecificColumn CHAR(30) NOT NULL,--
CONSTRAINT Game_PK PRIMARY KEY (OrganizationId,GameNumber),CONSTRAINT GameToProduct_FK FOREIGN KEY (OrganizationId,GameNumber)
REFERENCES Product (OrganizationId,ProductNumber)
);
CREATE TABLE Genre ( -- Stands for an independent entity type.
GenreNumber INT NOT NULL,Name CHAR(30) NOT NULL,Description CHAR(90) NOT NULL,--
CONSTRAINT Genre_PK PRIMARY KEY (GenreNumber),CONSTRAINT Genre_AK1 UNIQUE (Name),CONSTRAINT Genre_AK2 UNIQUE (Description)
);
CREATE TABLE SystemGame ( -- Represents an associative entity type or M:N association.
SystemOrganizationId INT NOT NULL,SystemNumber INT NOT NULL,GameOrganizationId INT NOT NULL,GameNumber INT NOT NULL,CreatedDateTime DATETIME NOT NULL,--
CONSTRAINT SystemGame_PK PRIMARY KEY (SystemOrganizationId,SystemNumber,GameOrganizationId,-- Composite PRIMARY KEY.
CONSTRAINT SystemGameToSystem_FK FOREIGN KEY (SystemOrganizationId,SystemNumber) -- Multi-column FOREIGN KEY.
REFERENCES MySystem (OrganizationId,CONSTRAINT SystemGameToGame_FK FOREIGN KEY (SystemOrganizationId,GameNumber) -- Multi-column FOREIGN KEY.
REFERENCES Game (OrganizationId,GameNumber)
);
CREATE TABLE GameGenre ( -- Denotes an associative entity type or M:N association.
GameOrganizationId INT NOT NULL,GameNumber INT NOT NULL,GenreNumber INT NOT NULL,CreatedDateTime DATETIME NOT NULL,--
CONSTRAINT GameGenre_PK PRIMARY KEY (GameOrganizationId,GameNumber,GenreNumber),-- Composite PRIMARY KEY.
CONSTRAINT GameGenreToGame_FK FOREIGN KEY (GameOrganizationId,GameNumber)
REFERENCES Game (OrganizationId,-- Multi-column FOREIGN KEY.
CONSTRAINT GameGenreToGenre_FK FOREIGN KEY (GenreNumber)
REFERENCES Genre (GenreNumber)
);
CREATE TABLE Job ( -- Stands for an associative entity type or M:N association.
OrganizationId INT NOT NULL,JobNumber INT NOT NULL,Title CHAR(30) NOT NULL,CreatedDateTime DATETIME NOT NULL,--
CONSTRAINT Job_PK PRIMARY KEY (OrganizationId,ProductNumber,JobNumber),-- Composite PRIMARY KEY.
CONSTRAINT Job_AK UNIQUE (Title),-- Single-column ALTERNATE KEY.
CONSTRAINT JobToProduct_FK FOREIGN KEY (OrganizationId,ProductNumber) -- Multi-column FOREIGN KEY.
REFERENCES Product (OrganizationId,ProductNumber)
);
CREATE TABLE Collaborator ( -- Represents an associative entity type or M:N association.
CollaboratorId INT NOT NULL,OrganizationId INT NOT NULL,ProductNumber INT NOT NULL,JobNumber INT NOT NULL,AssignedDateTime DATETIME NOT NULL,--
CONSTRAINT Collaborator_PK PRIMARY KEY (CollaboratorId,OrganizationId,-- Composite PRIMARY KEY.
CONSTRAINT CollaboratorToPerson_FK FOREIGN KEY (CollaboratorId)
REFERENCES Person (PersonId),CONSTRAINT CollaboratorToJob_FK FOREIGN KEY (OrganizationId,JobNumber) -- Multi-column FOREIGN KEY.
REFERENCES Job (OrganizationId,JobNumber)
);
强调跨多个表的复合PRIMARY KEY约束的声明是恰当的,这些表代表在概念实体类型之间发生的连接的层次结构,这种安排在例如表达SELECT操作时对于数据检索是非常有益的.包括JOIN以获取派生表.
是,(i)每个M：N关联和(ii)每个关联实体类型由(iii)逻辑DDL结构中的对应表表示,因此要特别注意PRIMARY和FOREIGN KEY约束(以及表示这些概念元素的表格留下的评论,因为它们有助于确保相关行之间的连接符合适用的基数.
复合键的使用是从关系范式的起源于Dr. E. F. Codd引入的,正如他在1970年的开创性论文A Relational Model for Large Shared Data Banks中所包含的例子中所证明的那样(正是,它也提供了处理概念M：N关联的最优雅的方法). .
我提出了a db<>fiddle和a SQL Fiddle,它们都在Microsoft sql Server 2014上运行,因此可以对结构进行测试.
正常化
规范化是一个逻辑层次的过程,基本上说,
>通过第一范式消除非原子列,以便通过使用的数据子语言(例如,sql)更容易处理数据操作和收缩;和
>通过连续的正常形式消除特定表的列之间的不良依赖关系,以避免更新异常.
当然,必须考虑所讨论的表格和列所带有的含义.
我喜欢把规范化看作是建立在科学基础上的测试,一旦设计师设计了稳定的逻辑层次安排以确定其项目是否符合所有正常形式,设计师就会将其应用于相关元素.然后,如果需要,设计者采取适当的纠正措施.
冗余
在关系模型中,虽然列中包含的值的重复不仅可以接受,而且是预期的,但禁止重复行.在这种程度上,据我所知,在之前暴露的逻辑布局中包含的所有表中都会阻止重复行和其他类型的有害冗余,或许您想澄清您在这方面的关注.
无论如何,您当然可以(a)通过正常形式评估您自己的结构,以确定它是否符合要求,以及(b)在必要时进行修改.
相关资源
>在this series of posts中,我提出了一些关于直接M：N关联的讨论,该关联可以使两种不同实体类型的实例相互关联.
>在this other one中,我提出了一种处理“物料清单”或“零件爆炸”构造的方法,其中我描述了如何连接相同类型实体的不同实例.
三元协会
Every time I try to make a bridge the elements in that bridge also have a Many to Many,I’m under the impression that isn’t allowed or at least discouraged.
这种情况似乎表明您的一个问题与概念三元关联有关.基本上,当存在(1)涉及(2)其他两种关系的关系时,这种关联就出现了,换句话说,“关系之间的关系” – 一种典型的情况 – .
在适当管理的情况下,这些安排也不会带来有害的冗余.并且,是的,如果存在某个用例,您确定此类关系在“真实世界”实体类型中出现,则必须(i)建模并且(ii)在逻辑级别上准确地声明它们.
> Here is a question and answer我们分析了一个关于调查的话语领域,其中包括一个三元关联的例子.
>在this very good answer中,@Ypercube提供了一个图表和相应的DDL结构,用于形成有趣的菱形关系,这与这类场景非常相似.
