Composite Foreign Key to Dynamic Table, Bad Practice

Question

First off, I am not a DBA, I am a C# developer. I am working on a pretty complex web application and I want to start with a solid database. So here is my issue: I am trying to create a Settings table that will hold settings for multiple objects. ie: User settings, Season settings, League settings, Team settings.

Instead of creating a table for each of those I would like to keep everything in the Settings table but I can't figure out how to create the foreign key.

My approach is to have three columns in the Settings table: TableName PrimaryKey ID. These three columns would create a composite key that would reference the appropriate object. Is my approach considered bad practice or am I on the right track? Is there a way I can do this that will still work with entity framework?

Answer 1

Here is my way of handling this.

In this diagram, ConfigId is primary key which is used as an FK in Person table as EyeColor, BloodType, PersonType and many more. These columns also hold a Check constraint to ensure no value apart from eye color or corresponding column get stored based on ConfigType. However this comes at a cost of broader datatype which may be smallint or int instead of tinyint.

Only difference is, I am calling these settings as ConfigType in your case {User, Team, Season etc.} and any extension can be done by inheritance concept by creating another table using ConfigId as FK in child table.

Answer 2

If you use inheritance, by default EF will use one table for the entire hierarchy. This is known as Table Per Hierarchy or TPH

public abstract class Setting
{
   public string Key { get; set; }
   public string Value { get; set; }
}

public class UserSetting : Setting
{
   public int UserId { get; set; }
   public virtual User User { get; set; } 
}

public class SeasonSetting : Setting
{
   public int SeasonId { get; set; }
   public virtual Season Season { get; set; }
}