Foreign Key dependency on another Foreign Key as it's parent key

Question

I am creating a library which works as a base for other projects, details of the project can be found here and the nuget package can be found here

Since this is a base library and I want to allow the users to extend models so I am creating interfaces instead of concrete libraries (default implementation is obviously there, but that is out of scope of current question), but since entity framework can accomplish navigation/foreign keys only with concrete class, I am going through the route of generic interfaces, it is working pretty well, but I have reached a point where I want to restrict the foreign key based on another foreign key in the same table.

While searching for the answers this question came closest to my requirement

SQL: Foreign key that is a child of another foreign key - Stack Overflow

The solution provided there says that technically you should remove the parent key as the child table already has parent key which can be used to associate to the parent table and I agree with it completely, however in my case that's not possible as it will lead to cyclic dependency hell which is something should be kept away from software projects at all costs.

IOrganization Interface

public interface IOrganization<TLinkedAddress> where TLinkedAddress : ILinkedAddress
{
    long ID { get; set; }
    string Name { get; set; }
    string Brand { get; set; }
    string Genre { get; set; }
    string Industry { get; set; }
    string Master_Security_Stamp { get; set; }
    long? Control_Branch_ID { get; set; }

    [ForeignKey("Control_Branch_ID")]
    TLinkedAddress Control_Branch { get; set; }

    IList<TLinkedAddress> Branches { get; set; }
}

ILinkedAddress Interface

public interface ILinkedAddress
{
    long ID { get; set; }
    string Address { get; set; }
    long LocationID { get; set; }
    string AddressType { get; set; }
    string Contact_Person_Name { get; set; }
    string Contact_Person_Number { get; set; }
    string Contact_Person_Relation { get; set; }
    [ForeignKey("OrganizationID")]
    long? OrganizationID { get; set; }
    [ForeignKey("UserID")]
    long? UserID { get; set; }
}

IEducation Interface

public interface IEducation<TUser, TOrganization, TLinkedAddress>
    where TUser : IUser
    where TOrganization : IOrganization<TLinkedAddress>
    where TLinkedAddress : ILinkedAddress
{
    long ID { get; set; }
    long UserID { get; set; }
    long InstituteID { get; set; }
    long InstituteLocationID { get; set; }
    string Degree { get; set; }
    string Major { get; set; }
    string Grade { get; set; }
    DateTime StartDate { get; set; }
    DateTime? EndDate { get; set; }
    string Socities { get; set; }
    string Description { get; set; }

    [ForeignKey(name: "UserID")]
    TUser User { get; set; }
    [ForeignKey(name: "InstituteID")]
    TOrganization Institute { get; set; }
    [ForeignKey(name: "InstituteLocationID")]
    TLinkedAddress InstituteLocation { get; set; }
}

Default implementations

LinkedAddress.cs

[Table(name: "LinkedAddress", Schema = "Arinsys_CRM")]
public class LinkedAddress : DBEntity<LinkedAddress>, ILinkedAddress
{
    public long ID { get; set; }
    public string Address { get; set; }
    public long LocationID { get; set; }
    public string AddressType { get; set; }
    public string Contact_Person_Name { get; set; }
    public string Contact_Person_Number { get; set; }
    public string Contact_Person_Relation { get; set; }
    [ForeignKey("OrganizationID")]
    public long? OrganizationID { get; set; }
    [ForeignKey("UserID")]
    public long? UserID { get; set; }
}

Organization.cs

[Table(name: "Organization", Schema = "Arinsys_CRM")]
public partial class Organization : DataContext.DBEntity<Organization>,
    IOrganization<LinkedAddress>
{
    public long ID { get; set; }
    public string Name { get; set; }
    public string Brand { get; set; }
    public string Genre { get; set; }
    public string Industry { get; set; }
    public string Master_Security_Stamp { get; set; }
    public long? Control_Branch_ID { get; set; }

    [ForeignKey("Control_Branch_ID")]
    public virtual LinkedAddress Control_Branch { get; set; }

    public virtual IList<LinkedAddress> Branches { get; set; }
}

Education.cs

[Table(name: "Education", Schema = "Arinsys_CRM")]
public class Education : IEducation<User, Organization, LinkedAddress>
{
    public long ID { get; set; }
    public long UserID { get; set; }
    public long InstituteID { get; set; }
    public long InstituteLocationID { get; set; }
    public string Degree { get; set; }
    public string Major { get; set; }
    public string Grade { get; set; }
    public DateTime StartDate { get; set; }
    public DateTime? EndDate { get; set; }
    public string Socities { get; set; }
    public string Description { get; set; }

    [ForeignKey(name: "UserID")]
    public virtual User User { get; set; }
    [ForeignKey(name: "InstituteID")]
    public virtual Organization Institute { get; set; }
    [ForeignKey(name: "InstituteLocationID")]
    public virtual LinkedAddress InstituteLocation { get; set; }
}

Now the question is that is it possible to put a constraint on InstitueLocationID in IEducation interface that it must be a branch of Organization defined by InstituteID or I would have no option other than to leave on the end developer to put a constraint himself

EDIT : Further Research

I started researching on transitive dependencies as suggested by @Ivan Starostin and came across this awesome blog post by @Jeff Atwood (Co-Founder (Emeritus) and co-creator of Stack Overflow) about the problems they faced with Stack Overflow in 2008

Maybe Normalizing Isn't Normal

Summary of the blog post is that normalization is not a silver bullet, and sometimes denormalized data will actually result in better productivity and performance, though that blog post didn't answered the exact question, it helped me gain a deeper understanding, so posting here for future readers.

Answer 1

Now the question is that is it possible to put a constraint on InstitueLocationID in IEducation interface that it must be a branch of Organization defined by InstituteID

EF don't know anything about interfaces but it only care about on classes. It is not actually possible to specify configurations that implies interfaces. Fluent API and Data Annotation configuration only work with classes. You can't create constraint for all classes that derive from IEducation but only on classes that implement this interface.

I would have no option other than to leave on the end developer to put a constraint himself

Take a example to ASP.Net Identity. To work with this API with zero configuration Microsoft give us default implementations for each class that we might need: IdentityUser, Role etc... Those default classes implement IUser, IRole etc...

If default implementations don't satisfy your needs you have two options:

• create new classes that derives from default implementations
• create new classes that implement interfaces given by the API (which are implemented by default classes)

If you choose the latter option, the EF configurations are left to you. You must code all configurations (using Fluent API or Data Annotations) to make other classes to work with your new classes. I mean classes implementing IUSerStore, IUserPasswordStore, IUserLoginStore etc... If you don't even want to use EF this API let you implement All I*Store interfaces etc...

My answer is to create a default implementation of all your interfaces and put constraints on those classes. If a developper want to extend your API he can subclass your default classes or he can create new classes by implementing the API interfaces and must create all constraints needed for the his new classes to work correctly with the rest of your API.

Answer 2

It's a bit theoretical question. InstituteID is not an attribute of the Education entity - it is the attribute of the LinkedLocation entity. So you have a transitive dependency in Education entity, which means it is not in 3rd normal form: InstituteID depends on InstituteLocationID.

So you have to treat InstituteID as denormalized data without relational constraints or remove it from Education entity.

Furthermore I think troubles come from LinkedAddress entity - it is a mix of Address entity and additional link entity which should store links between Addresses and Organizations. So if you extract entity Address from LinkedAddress and make LinkedAddress a real linking entity which stores tuples of AddressID and OrganizationID - probably this will make model more clear and presence of InstituteLocationID attribute will be enough to determine all related to Edication entity data.