Tables that Link To Many Other Tables (but only one at any time per record)

All we need is an easy explanation of the problem, so here it is.

I am redesigning the application that drives our company phone system.

There are various types of features within the phone application, such as menu systems, announcements, time-based routing of calls etc.

Those are all currently stored in one table called "features", with an Enum which describes the feature type.
However, that means that all fields in that table have generic names, such as "Setting1", "Setting2" etc.
In addition, each "features" record in the table is linked to another "features" record called "TargetFeature", so that the phone system knows what to do after completing the action of the current record.
This can get complicated when it comes to menu systems, as there could be up to 11 different TargetFeatures, options 0 to 9 and STAR.

With the redesign, I’d like to separate the features into their own tables. IE. A table for Menus, a table for time-based call routing etc.

I can’t figure out how best to establish that relationship. Each feature table can link to any other type of feature table.

If there are 10 different feature tables, each feature table would need a nullable ID to reference all the possible tables that could be the TargetFeature.

However, with menu systems, as there are 11 options to choose from, that would require a table with 11 options x 10 feature table IDs, with 90% of those fields being Null.

Would anyone be able to recommend a suitable structure that allows multiple tables to link with multiple other tables, without having to create ID fields for every possible other table?

I was thinking of using link tables with guids, but can’t figure that one out.

Many thanks!

Solution

Thanks to David Spillett for giving me the understanding I needed.

I created a "ParentFeatures" table, and then sub-tables for each type of Feature.

All sub-tables have a ParentFeaturesId field so they can relate to the ParentFeatures table that contains generic fields, such as Name and UserId.

Finally, all subtables have the required number of TargetParentFeatureId fields dependant on how many targets each type of Feature requires to function.

So all Features can now point to a TargetFeature by using their TargetParentFeatureId fields.

Using EF Core 5.0 and MySQL 8.0.19 using the Pomelo.EntityFrameworkCore.MySql library v5.0.1, I have successfully tested this method and it appears to work perfectly.

Thanks again.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

Handling Different Types With The Same Relationships

For breaking entities with common relationships (and perhaps other properties) but entity specific properties, look into table inheritance. Some DBs have build-in support to help out a little with this pattern, but it is a common pattern you can implement anywhere:

ThingsTable                     SubThing1
===========                     ==================
ThingID             (PK) <----- ThingID       (FK) 
ThingType                   |   SubThing1ID   (PK) 
ThingName                   |   SubThing1Property1
OtherCommonProperty         |   SubThing1Property2
LinkToSomethingElse (FK)    |
                            |   SubThing2
                            |   ==================
                            `-- ThingID       (FK) 
                                SubThing2ID   (PK) 
                                SubThing2Property1
                                SubThing2Property2

Obviously using much better names than Thing and SubThing! And of course each SubThingNPropertyN should actually be a meaningful name like "ServiceDate" or "ThribbleLength". There is an argument for not having a separate SubThingID for each sub-type as the main identifier will be unique in each sub-table anyway so can act as primary key.

This way, only common properties are present on the main “Things” table, entity specific properties go on their own tables reducing the proliferation of per-type properties that contain NULL most of the time. Relationships to other entities are usually common properties on the main Things table but there could be type specific relationships elsewhere particularly if one or more of the types are modelled as a multi-table manner on their own.

Handling Many, Usually NULL, Properties

Table inheritance deals with properties that are usually NULL as they only relate to one of many possible entity types, because properties that only relate to one type will only exist in that sub-table, but you may still have types with many commonly NULL properties. This is probably OK as it is, though if not you could consider looking at the “entity-attribute-value” (EAV) model. Be warned that EAV is often considered an anti-pattern for good reason, so only use it if it really is most efficient for your needs.

More Detailed Explanations

If you search for “table inheritance” and “EAV pattern” you will find a lot of information about these modelling methods, when they are appropriate, and more importantly when they are not. I’ll not try to fill this answer with books worth of discussion that can be found elsewhere!

More Specific?

These are general recommendations of models to look into. As @bbaird suggests in his comment, providing sample data may help people provide more specific recommendations for your circumstance.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply