Relationship with another Entity when using inheritance on parent class or on child classes

Question

I have the following inheritance. A doctor is a parent class. A doctor can be a paediatrician, a dentist or an orthopaedic. Each doctor type have different properties (different domain logic) but they all share the same Id, Name and they also all can have many appointments.

public abstract class Doctor
{
    public long Id { get; set; }

    public string Name { get; set; }
}

public class Paediatrician : Doctor
{
    // add some other properties specific only to Paediatrician.
}

public class Orthopaedic : Doctor
{
    // add some other properties specific only to Orthopaedic.
}

public class Dentist : Doctor
{
    // add some other properties specific only to Dentist.
} 

public class Appointment
{
    public long Id { get; set; }
    public DateTime DateOfAppointment { get; set; }
}

public class Country
{
    public long Id { get; set; }
    public string Name { get; set; }
}

There is a one to many relationship here (a doctor can have many appointments). Question here is where do I add the Appointment relationship? Inside each child class or on the parent class? I have to set this relationship to my ORM so adding this relationship into each child class would produce three different relationships into my db whereas if I add it to the parent class only one? The same stands for Country table. Where do I add Country relationship? Only on the parent class or child classes?
One of the systems requirement that might affect my selection is that I have is to show a list view with the name of the doctor and the appointment date. So there are two ways of doing this. Either by calling each child class and aggregate the results or by calling parent class directly and get the appointments if I add the Appointment relationship to the parent class. I am stuck and don't know what is the correct way of doing this fundamentally.

This is not really a programming question, but more a relational database question. However, inheritance is described in the Microsoft docs. (In recent years the Microsoft docs have become quite good (compared to the past).) I think the table-per-hierarchy can become quite big if there are mutliple derivatives. Table-per-type seems more logical, although there's an extra level of indirection, which has inferior performance (see bottom comment on page). — JHBonarius
@JHBonarius but even in TPT where do common relationships are added? In the parent class or child classes. The document of EF Core does not state such a scenario (most of the examples in general show very simple examples) leaving the more complex scenarios with a big questionmark — pantonis
I'll improve my answer later. But to my best knowledge, the list of appointments should be in the base. Why wouldn't it? Just as id and name are common... why would the common list be different? — JHBonarius
If it is in the base can I query the base class directly? Isn't the purpose of the inheritance to hide the base class and work with the child classes only? — pantonis

JHBonarius JHBonarius · Accepted Answer · 2021-02-01T10:07:48

The question is not specific for C# .Net or entity framework, but it's generally about object relationships.

The first point is inheritance. A derived class inherits all public and protected properties (and has access to methods with the same qualifiers) from the base class. I.e. A Dentist will also have an Id and a Name, although you don't declare them in the class. They are inherited from Doctor.

The second point is object relations. Clearly a Dentist can make an Appointment and so can a Paediatrician. And both are a type of Doctor. As you state, there's a one-to-many relationship. The "many" is the Appointment, but what do you want the "one" to be? Of course you can choose it to be Dentist or Paediatrician. But you'll have to make an appointment a generic class

    public class Appointment<T> where T : Doctor
    {
        [Key]
        public long Id { get; set; }
        [Required]
        public DateTime DateOfAppointment { get; set; }
        [Required]
        public T DoctorNavigation { get; set; }
    }

Each derived class would then have its own appointment list

    public class Dentist : Doctor
    {
        ...
        public List<Appointment<Dentist>> DentistAppointments { get; set; }
    }

However, I would expect that there's only one list of Appointments, for which the "one" relationship is a Doctor. Of course, you could still give each Derived class its own list of appointments, but that would cause a strange Appointment entity, which has an optional navigation property to each of the derived classes.

More logically, you would put the list of Appointments in the Doctor (base) class.

~~You will need to add a Discriminator to be able to map back from Doctor to a derivative.~~ Hmm, the discriminator doesn't work for TPT... trying to figure out how to do this.

Relationship with another Entity when using inheritance on parent class or on child classes

1 Answers