CODEWITHSUNDEEP
asp.net-mvc-3entity-relationship
MissioDei
MissioDei

Reputation: 809

Data Structure Question

What's the best way to handle this scenario?

I have a customer Model(Table) contains contact info for customers I have Prospect Model(Table) contains contact info for store visitors that aren't customers I have an Opportunity Model (Table) when either a customer or Prospect visits the store.

In my view I want to generate a new oppportunity. An opportunity can only contain either 1 customer association or 1 prospect association but not both.

In my opportunity model I currently have both the customer and prospect as nullable foreign Id's and and navigation properties. I also have an ICollection<> for Customers and Prospects on the opportunity model.

Is this the right way to do handle a conditional association?

When it comes to the view, I'm stuck on how would I make the customer or prospect association?

Upvotes: 1

Views: 86

Answers (1)

Jack
Jack

Reputation: 9252

I am a computer science student, and this is my understanding on DB relationships:

Since you have two types of "People" - Customer - and Prospect - you could potentially have a table called "Person". In the Person table any common data among both entities would be stored (FirstName, LastName, Address1, Address2, City, State, Zip, etc...).

To indicate that a Person is a Prospect, you would have a Prospect table, which would have a PersonId to link to the person table. You can store more specific attributes about a prospect in this table.

The same would go for a Customer - you would have a Customer table - that would have a PersonId column to link to the Person table, and any specific attributes for the Customer entity.

Now you have a database in which you can derive other entities ... say an Employee entity > you have your base Person Entity to start from. And your Employee table would link back to it, and also have other custom columns for employee specific data.

Does that make sense?

Or maybe I'm going about this all wrong :). Please correct me if I am wrong as I am still a student.

I think you are stuck because you now have two fields on an "Opportunity" record (Customer OR Prospect), one of which MUST be null. With the model I proposed, your Opportunity would link to a Person, in which you can define custom business rules restricting say... an Employee Opportunity (which actually might not be a bad idea).

For the referenced Person in your Opportunity model, it would not be an ICollection (since you specifically said that an opportunity can have ONLY one person). It would simply be a single class such as:

private virtual Person Person { get; set; }

EDIT: If you don't want to restructure your entire database, you could just have a dropdown that asks what type of Opportunity this is (Customer, or Prospect). Based on the selection, you would add a foreign key in your Opportunity table to link to your [Customer or Prospect].

Upvotes: 1

Related Questions