子类型的表关系
我有一个名为网站"的父表，其中包含有关网站的记录.我有一个名为SupportSystem"的子表，它保存有关不同类型支持系统(例如电子邮件、电话、票务、实时聊天等)的记录.有一个中间表Website_SupportSystem"以多对多关系连接这些表.
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.
如果网站的 SupportSystem 是票务，我还想记录软件平台.例如WHMCS.我的直觉是创建一个名为 SupportPlatform 的新查找表，并将其与现有的连接表Website_SupportSystem"相关联，并将数据存储在那里.但是，SupportSystem 和 SupportPlatform 之间没有关系.如果我将它们联系起来，那么我最终会得到一个循环引用.
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 表包含所有支持系统通用的列.
- 电子邮件、票务、电话和LiveChat表都有各自特定的列.
- 子类型表中的主键也是超类型表的外键.
- 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.
相关文章