CODEWITHSUNDEEP
sql-serverasp.net-mvcentity-framework
CallumVass
CallumVass

Reputation: 11456

SQL Server / EF - Database Design

I'm currently going to begin a new project to help with Complaints. I have worked out all the fields etc.. but in doing so, I've realised I need more than 1 type of Complaint. They are Customer Complaint, Supplier Complaint or Defect Complaint. They share many of the same fields but differ slightly, so I'm not sure how I would design this in a SQL Server database and/or using EF. The application will be an ASP.NET MVC 4 application using C#, so I could take advantage of Code First but as I said, I'm not sure how I'd design these Entities:

All 3 complaint types share the following columns:

ComplaintType (int), Department (int), 
Code (int), DefectReference (string), Cause (int)

Customer complaint and supplier complaint share these columns:

ComplaintText (string), Orders (collection), Rectification (string)

Here are columns unique to customer complaint:

AccountNumber (string), CustomerName (string)

And finally, here are fields unique to Supplier Complaint:

SupplierNumber (string), SupplierName (string)

So my questions are:

  1. How would I approach this creating my Models to use with EF Code First or
  2. How would I design this using SQL Server?

Thanks

Note: There are more columns (ID's etc..) but I've shortened it for this example.

Upvotes: 0

Views: 200

Answers (2)

AA.
AA.

Reputation: 4606

Yes, generally the EF Code First strategy is the best option.

You should to create a Complaint class and to use (delegate) this class into Supplier, Customer and Defect classes. Inheritance hierarchy can harm your design extensibility. Always favor composition over inheritance.

Upvotes: 1

Mark Oreta
Mark Oreta

Reputation: 10416

There are a few ways to go about implementing inheritance in Code First, and it all depends on how you want the tables represented in SQL.

Do you want a single complaint table for all types, where some fields are left null based on what type it is? You'd use Table Per Hierarchy for that.

Do you want a main complaint table that house the common fields, and then relational tables which hold the extra columns, based on class? You'd use Table per Type for that.

If you want a single table per complaint type, with the common fields expressed in every table? That's Table per Concrete Type

Personally, with what you have I'd probably lean towards Table Per Type. Here's a shortened example:

public abstract class Complaint 
{
    public int ComplaintType { get; set; }
    public int Department { get; set; }
    public int Code  { get; set; }
}

[Table("CustomerComplaint")]
public class BankAccount : Complaint
{
    public string ComplaintText { get; set; }
    public string Rectification { get; set; }
}

[Table("SupplierComplaint")]
public class SupplierComplaint: Complaint
{
    public string SupplierNumber { get; set; }
    public string SupplierName { get; set; }
}

Upvotes: 2

Related Questions