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

I would add a new column 'SupportPlatformId" to the "SupportSystem" table which lookup to the table "SupportPlatform", because "SupportSystem" to "SupportPlatform" is probably one-to-one or many-to-one.

Hence: Website -> (via Website_SupportSystem) SupportSystem -> SupportPlatform

Answer 2

Data about a Support Platform should be stored in the SupportPlatform table.

You can add a third foreign key, namely SupportPlatfromID, to the Website_SupportSystem table. If you do this, your intermediate table now records a ternary relationship, of the kind many-to-many-to-many. If this reflects the reality, then so be it.

If you want to relate SupportSystems and SupportPlatforms, just use the intermediate table as an intermediate table in the joins. You can even do a three way join to join all three entities via the intermediate table.

An alternative would be to create another intermediate table, SupportPlatform_SupportSystem, with a pair of foreign keys, namely SupportSystemID and SupportPlatformID. If this reflects the reality better, so be it. Then you can join it all together with a five table join, if needs be.

Answer 3

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.