亚型的表关系 [英] Table relationship for subtypes

问题描述

我有一个名为网站的父表，其中包含有关网站的记录。我有一个名为SupportSystem的子表，其中记录有关不同类型的支持系统，如电子邮件，电话，票务，实时聊天等。有一个中间表Website_SupportSystem以多种关系连接这些表。

如果网站的支持系统是票务，我也想记录软件平台。 WHMCS。我的直觉是创建一个名为SupportPlatform的新查找表，并将其与现有连接表Website_SupportSystem相关联，并将数据存储在那里。但是，SupportSystem和SupportPlatform之间没有关系。如果我关联那些，那么我最终得到一个循环引用。

你能看到我做错了什么吗？对这些数据建模的最佳方法是什么？

解决方案

可以使用超类型/子类型关系，如图所示。

• 支持系统表包含所有支持系统共有的列。


• 电子邮件 票务，电话和 LiveChat 表格列具有每个列。


• 子类型中的主键表也​​是超类型表的外键。

I have a parent table called 'Website' which holds records about websites. I have a child table called 'SupportSystem' which holds records about different types of support systems such as email, phone, ticketing, live chat etc. There is an intermediate table 'Website_SupportSystem' which joins these tables in a many-many relationship.

If the SupportSystem for a Website is ticketing, I also want to record the software platform .e.g. WHMCS. My instinct is to create a new lookup table called SupportPlatform and relate this to the existing join table 'Website_SupportSystem' and store the data there. However, then there is no relationship between the SupportSystem and SupportPlatform. If I relate those then I end up with a circular reference.

Can you see what I am doing wrong? What would be the best way to model this data?

解决方案

You could use super-type/subtype relationship, as shown in the diagram.

• SupportSystem table contains columns common to all support systems.
• Email, Ticketing, Phone and LiveChat tables have columns specific to each one.
• Primary key in the subtype table is also a foreign key to the super-type table.

这篇关于亚型的表关系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！