Model Inheritance and DB Normalization in Core

c# database-normalization entity-framework-core inheritance

Question

Doing C# ASP.NET Core 2.1 MVC and following along with the Contoso tutorials where you have a Student model and Instructor model that inherits from a People model with the common properties. Once migrated, this makes a single People table with a "Discriminator" field.

Is this single table with Discriminator acceptable when wanting 3NF normalization or is there an approach where I can still reduce redundant code while also generating a normalized layout for the db admins?

Edit in response to comments:

This is what I like about stack overflow, the sharing of ideas and interpretations. So here is my interpretation of 3NF and if I am wrong, please share your interpretations so I can learn.

public abstract class BaseModel
{
    public int ID { get; set; }
}

public abstract class Person : BaseModel
{
    public string LastName { get; set; }
    public string FirstName { get; set; }
}

public class Student : Person
{
    public DateTime EnrollmentDate { get; set; }
}

public class Instructor : Person
{
    public DateTime HireDate { get; set; }
}

Result is a single "People" table as such: ID | LastName | FirstName | Discriminator | HireDate | EnrollmentDate

Discriminator is NVARCHAR(MAX) or in other words a string which will be either "Instructor" or "Student"

Discriminator therefore will have redundant values which should be in their own table and use an integer foreign key.

Also there are transitive dependencies because Student determines EnrollmentDate but has nothing to do with HireDate, and the other way around for Instructor. So either date column doesn't belong within this table either and I believe this table doesn't even meet the requirements of 2NF.

I believe a properly normalized set of tables would be as such:

-People- ID | LastName | FirstName

-Students- PersonID | EnrollmentDate

-Instructors- PersonID | HireDate

OR

-Students- ID | LastName | FirstName | EnrollementDate

-Instructors- ID | LastName | FirstName | HireDate

So back to the original post and question, is a "master" table with a discriminator column ok for normalization or not because the goal should be properly structured tables for the db admins and at the same time removal of redundant properties for the programmer to worry about writing formats and validations and error messages for. With that second point too, please share other solutions like if data validations can be done in DTOs or ViewModels instead.

1
0
12/12/2018 2:22:24 PM

Accepted Answer

The fix was a single line removal

Models:

public abstract class BaseModel
{
    public int ID { get; set; }
}
public abstract class BasePerson
{
    public string LastName { get; set; }
    public string FirstName { get; set; }
}
public class Student
{
    public DateTime EnrollmentDate
}
public class Instructor
{
    public DateTime HireDate
}

DbContext:

public class SchoolContext : DbContext
{
    public SchoolContext(DbContextOptions<SchoolContext> options)
        : base(options)
    {
    }
    public DbSet<Student> Students { get; set; }
    public DbSet<Instructor> Instructors { get; set; }
    // public DbSet<BasePerson> People { get; set; }
}

If you include the People DbSet like the Contoso tutorials show, when you perform a migration and update-database, you get a single People table in your database with a column named Discriminator after all the inherited fields that will have a value of either Student or Instructor and any unique fields at the end of the table.

It occurred to me that there wasn't a single Model table from BaseModel because there isn't a DbSet< BaseModel > Models in the DbContext. So I tried it and it worked, remove the line for DbSet< BasePerson > People and what you get is separate tables for Students and Instructors with all inherited fields.

0
12/12/2018 2:21:48 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow