mysql - Table relationship for subtypes

Question

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?

Answer 1

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.