How can a table have two nullable FKs, where only one of them has a value?

Question

I have a Project, which can either be owned by a Member of the organisation, or by an ExternalResourcePerson. That means that each of the FKs MemberId or ExternalResourcePersonId can be nullable, but not both. How can I set up this relationship?

I just started introducing the term "External Resource Person" into this. Maybe the ExternalResourcePerson-class should have a List<Project> instead of the List<ExternalResourcePersonEngagement>?

These are the classes:

public class Project
{
    public int Id { get; set; }

    // A Project can either have a Member as owner ...
    public int MemberId { get; set; }
    public Member ProjectOwner { get; set; }

    // ... or an ExternalResourcePerson
    public int ExternalResourcePersonId { get; set; }
    public ExternalResourcePerson ExternalProjectOwner { get; set; }
}

public class Member
{
    public int Id { get; set; }

    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Email { get; set; }
    public string Phone { get; set; }
    public string Address1 { get; set; }
    public string Address2 { get; set; }
    public string Zip { get; set; }

    public List<Project> Projects { get; set; }
}

public class ExternalResourcePerson
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Address { get; set; }
    public string Organisation { get; set; }
    public string Expertise { get; set; }
    public string Email { get; set; }
    public string Phone { get; set; }
    public string Notes { get; set; }
    public List<ExternalResourcePersonEngagement> Engagements { get; set; }
}

public class ExternalResourcePersonEngagement
{
    public int Id { get; set; }
    public int ProjectId { get; set; }
    public Project Project { get; set; }
    public int ExternalResourcePersonId { get; set; }
    public ExternalResourcePerson Person { get; set; }
}

I have seen this question, but I am not able to apply any of the answers to my case.

score 1 · Accepted Answer · answered Nov 18 '19 at 13:04

Just declare them as nullable and add methods that will be responsible for assigning it properly.

Some basic example that is not a production-ready solution:

//Marker
public interface IProjectOwner
{

}

public class Project
{
    public int Id { get; set; }

    // A Project can either have a Member as owner ...
    public int? MemberId { get; private set; }
    public Member ProjectOwner { get; private set; }

    // ... or an ExternalResourcePerson
    public int? ExternalResourcePersonId { get; private set; }
    public ExternalResourcePerson ExternalProjectOwner { get; private set; }

    public void SetProjectOwner(IProjectOwner projectOwner)
    {    
        //Add some validation for this. Like checking if the project owner is set.
        if(projectOwner == null)
            throw new Exception("Project owner cannot be null.");

        if(projectOwner is Member )
        {
            ProjectOwner = projectOwner;
            MemberId = projectOwner.Id;
        }

        if(projectOwner is ExternalResourcePerson)
        {
            ExternalProjectOwner = projectOwner;
            ExternalResourcePersonId = projectOwner.Id;
        }
    }
}

public class Member : IProjectOwner
{
    public int Id { get; set; }

    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Email { get; set; }
    public string Phone { get; set; }
    public string Address1 { get; set; }
    public string Address2 { get; set; }
    public string Zip { get; set; }

    public List<Project> Projects { get; set; }
}

public class ExternalResourcePerson : IProjectOwner
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Address { get; set; }
    public string Organisation { get; set; }
    public string Expertise { get; set; }
    public string Email { get; set; }
    public string Phone { get; set; }
    public string Notes { get; set; }
    public List<ExternalResourcePersonEngagement> Engagements { get; set; }
}

public class ExternalResourcePersonEngagement
{
    public int Id { get; set; }
    public int ProjectId { get; set; }
    public Project Project { get; set; }
    public int ExternalResourcePersonId { get; set; }
    public ExternalResourcePerson Person { get; set; }
}

You can also add some method that will retrieve this person.

I didn't use any of the interface-stuff, but simply setting them nullable and then in the controller making sure that one and only one have a value did the trick! :) — Stian, Nov 20 '19 at 16:01

How can a table have two nullable FKs, where only one of them has a value?

1 Answers1