CODEWITHSUNDEEP
entity-frameworkentity-framework-4.1entity-framework-mapping
Aaron Bar
Aaron Bar

Reputation: 620

Entity Framework Modeling - How to model this simple data structure?

Data Structure

This is a relatively simple data structure, but I am having a hard time figuring out the best EF strategy to model it. It's obviously not Table Per Hierarchy. So I tried Table Per Type and it didn't like the relationships between Client and Person or Customer and Person. After reading about Table Per Concrete Type, it doesn't follow that model either. S how would one model this ERD?

#

Update

#

The business use case behind it is that we deal with several companies. Some of these companies have purchased other companies. The parent company (Acme) has been a long time customer. The child company (Zeta) has been a long time client. When Acme bought Zeta they put one person in charge of handling the relationships between the company (Adam). Adam now has 2 distinct email addresses that need to be notified of events. [email protected] gets customer notifications. [email protected] gets client notifications. That was the reason I had it modeled like it was. Adam is a bit OCD and likes to keep his notifications separated into individual mailboxes. As a programmer, I don't like Adam. :D

There are several other classifications in the system than Client and Customer. We also have Vendors, Technicians, and Distributors. I ended up modeling it very similar to the below suggestion, but split the Email table into type specific tables (CustomerEmail and ClientEmail). Not perfect but in order to just "get it done", it works.

Upvotes: 0

Views: 335

Answers (1)

Slauma
Slauma

Reputation: 177163

A person can be both a client and a customer.

Then Client and Customer cannot inherit from Person. You must model this with composition. Not: A Person is a Client or Customer. Instead: A Person has an attribute to be a Client and/or Customer.

With EF this would be two one-to-one relationships:

public class Person
{
    public int PersonId { get; set; }
    public Client Client { get; set; }
    public Customer Customer { get; set; }
}

Client and Customer can have a Person reference refering back to Person. (They don't need to have this reference but it makes sense to enable access to the FirstName and LastName via this navigation property.)

public class Client / Customer
{
    public int PersonId { get; set; }
    public Person Person { get; set; }
}

I assume that a Person doesn't need to have always both a Client and a Customer attribute, but sometimes only one (or even zero?). In this case Person is the principal in both relationships and Client/Customer are the dependents: They must have a reference to a Person, but the Person only has optional references to Client and Customer. With Fluent API this would be modeled like so:

modelBuilder.Entity<Person>()
    .HasOptional(p => p.Client)
    .WithRequired(c => c.Person);

modelBuilder.Entity<Person>()
    .HasOptional(p => p.Customer)
    .WithRequired(c => c.Person);

The relationships between Person and Note are two ordinary one-to-many relationships. Person can have two navigation collections...

public ICollection<Note> NotesAsNotator { get; set; }
public ICollection<Note> NotesAsNotatee { get; set; }

...and Note can have two references to Person together with the two foreign key properties (they are not required to be exposed in the model but often helpful):

public int NotatorIDFK { get; set; }
public Person Notator { get; set; }

public int NotateeIDFK { get; set; }
public Person Notatee { get; set; }

And the relationships are defined with Fluent API as follows:

modelBuilder.Entity<Person>()
    .HasMany(p => p.NotesAsNotator)
    .WithRequired(n => n.Notator)
    .HasForeignKey(n => n.NotatorIDFK)
    .WillCascadeOnDelete(false);

modelBuilder.Entity<Person>()
    .HasMany(p => p.NotesAsNotatee)
    .WithRequired(n => n.Notatee)
    .HasForeignKey(n => n.NotateeIDFK)
    .WillCascadeOnDelete(false);

Cascading delete must the disabled here (at least for one of the two relationships), otherwise Person can delete Notes through more than one cascading delete path due to the two relationships which is - at least in SQL Server - forbidden.

The relationships between Location and Client and between Location and Customer are again two one-to-many relationships each (four in total) with four collections in Location and two references to Location in Client and Customer. They are modeled similarly to the relationships above.

The relationships between Client and Email and Customer and Email are a problem because they are one-to-one but apparently with a foreign key PersonIDFK (with unique key constraint?) which is not supported by EF. As already asked by Gert Arnold in the comments: Why isn't the relationship between Person and Email? Are the two relationships supposed to express that a person can have another email address as a client than as a customer? I don't understand this part of the model.

Upvotes: 3

Related Questions