Prevent cyclic parent_ID `table per type` vs `table per hierarchy` vs `table per class`

Question

I am using Table Per Hierarchy TPH in Entity Framework, which is basically a partial class for common core attributes to the extended data types.

I have 2 Objects/Classes; Course Object and a Student Object which share the same base properties as the MyBaseCommonEntity. The goal is to be able to perform a Service/ticket on either of the objects. I need help in modeling this, will this be a generic type or inheritance type?

My questions: I am using int to refer to a parentID. The pK Key of the table is also an int.

How can I prevent cyclic references
Is this check done while setting
Does Hierarchy id type give me this functionality by default?

What is the difference between table per type vs table per hierarchy vs table per class`

public partial class MyBaseCommonEntity
{
    public int Id { get; set; }
    //Does it have any parents, does it belong to anyone
    public int? ParentId { get; set; }
    public string Name { get; set; }
    public string Description { get; set; }

    public System.DateTime CreatedDate { get; set; }
    public System.DateTime StartDate { get; set; }
    public System.DateTime? EndDate { get; set; }

    // Audit Stuff, whom, when why?
    public System.DateTime? AuditDate { get; set; }
    public string AuditUser { get; set; }
    public string AuditComments { get; set; }
}

Nikolay · Accepted Answer · 2015-11-02T07:42:13.897

I think you are mixing up two things here - hierarchy support (id/parentid) and class inheritance (re-using one base class). First three points are about hierarchy; the fourth is about inheritance.

Cyclic reference check in the style you mean is not directly supported with EF, as far as I know. You could do it yourself though (in case of SQL server, by creating a CHECK constraint with CTE for example)
Since cyclic checks in that form are not supported by EF, so it is not checked at at all.
HierarchyID is not directly supported by EF. You could check out some third-party library which adds support for hierarchyid though.
This point is actually not about parent/child, but about how to represent class inheritance in a database. All options you mentioned are explained in the corresponding KB article here: Implementing Inheritance with the Entity Framework

In general, it does not seem to be a good idea to make Student and Course inherit from one thing; these are mostly unrelated. You could just make one more entry, like "AuditInfo" and add a reference to that in Student and Course class. Could make more sense.

Prevent cyclic parent_ID `table per type` vs `table per hierarchy` vs `table per class`

1 Answers1