Single Foreign to Multiple tables - EF Core

Question

In my scenario, i'm having a multiple classes with common list of object.

[Table("ClassATable")]
public class ClassA
{
    public string id { get; set; }
    public List<CommonData> dataList { get; set; }
    //... other properties
}

[Table("ClassBTable")]
public class ClassB
{
    public string id { get; set; }
    public List<CommonData> dataList { get; set; }
    //... other properties
}

[Table("ClassZTable")]
public class ClassZ
{
    public string id { get; set; }
    public List<CommonData> dataList { get; set; }
    //... other properties
}

[Table("CommonDataTable")]
public class CommonData
{
    public string id { get; set; }
    //... other properties
}

Using EF Core 3.1.6, I can convert this into Table where CommonData table holds FK of each of the other table. (i.e it will be having 'n' number of FK columns). It is very strange to hold all the 'n' columns where there will be only one FK for the CommonData table row. I need a solution to use only one column in CommonData which needs to hold the FK of other tables.

Kindly help. Thanks in advance.

Answer 1

What you are talking about is not viable in database table relationships in persisting the data to MS SQL database tables.

If CommonData is a child table to all the other parent tables, then it has to have a foreign-key column for each of the possible parent table records it is linked to.

EF expects that all primary-key ID properties be integers that are controlled the the database engine as it auto-increments the number for each table record inserted into a table. And in addition, EF auto populates the primary-key id of the parent record to the child record.

What is the purpose of this? As I see it, it is not an optimal design and it's not viable IMHO.