EF6 - Modeling complex hierarchy for Customer Management System

Question

I am having major problems figuring out how I should model this relationship hierarchy, and was hoping someone could make a suggestion, as well as point out any special config I would have to do with FluentAPI.

Basically, I am creating a customer management system. I want the base object to be a "Person", which will hold an id, name, contact info, etc.

Next, I want to have the concept of a "Customer", which is a Person in the system with some other data, such as a CustomerNumber string slapped on it.

Then, I want each Person to have a collection of other People that are "Relatives/Friends". It also needs to specify the type of relationship (brother, father, etc).

So basically, it boils down the goal of having a huge database of People - any of which may or may not be a Customer, and any of which can be set up as Relatives/Friends of any other Person/Customer in the entire system. A Person can have an unlimited number of relatives/friends, and can be a relative/friend of any number of other people (or none at all).

I hope I have explained that well enough. I have modeled this in several different ways (from using inheritance, to just directly modeling a Join Table with foreign keys) and so far nothing ends up with the correct database structure when I generate the model.

Answer 1

I would suggest a database structure something like this:

Customer/Person:

I haven't treated a Customer as a special case of Person because that falls apart the first time one of your customers is a business or some other type of organization.

If you treat a Customer as an entity type in its own right and allow it to contain one or more persons then you have the advantage of being able to track people as individuals, including all of their relationships, once and only once - even if they are your customer individually, and also collectively as part of a business or other organization.

For a lot of customers, there is only one record in Commercial_Relationship, which is fine, but the model provides the flexibility to track each person only once, even if they deal with you commercially in different contexts.

For example, I buy pens at Staples because I sometimes need a pen at home. However, I also sometimes buy pens at Staples because I need a pen in my office. My accountant makes me use different credit cards for these personal and business expenses in order to keep the two clearly distinct. I'm glad, however, that I don't get two sets of email spam from Staples because I buy from them in two different contexts.

Personal Relationships:

This is simply a many-to-many relationship between two Person records. All that's required is an intersection entity type. Note, however, that naming this relationship is often directional. In some cases, only one noun is needed, regardless of which direction you're traversing the relationship. Bob is Billy's friend and Billy is Bob's friend. However, Bob is Sally's father but Sally is Bob's daughter (or child, etc.). Therefore, the Personal_Relationship requires two descriptions, depending on which direction you're traversing the relationship.