Search code examples
entity-frameworkrelationshipentity-relationshipforeign-key-relationship

Entity Model Zero to Many Relationship

I have three tables named as
1. SubProcedure
2. UnitReference
3. TestReference

Here i am having the following conditions

  1. A Subprocedure should have atleast one UnitReference

  2. A SubProcedure Can have Zero or Many TestReferences

  3. A TestReference should have SubProcedure and UnitReference

TestRefernce Table Records Will be like this

Here's an TestRefenceTable Image link to TestReferences.

And My Existing Entity Model Diagram link is ExistingModel.

I am not able to post my image here so that i have added reference link for the diagram .

My Questions is

  1. How can i represent relation between SubProcedure => UnitReference .

  2. How can i represent relation between UnitReference => TestReference .

  3. How can i represent relation between SubProcedure => TestReference ..

I want to ensure my existing relationship between three tables is correct , If its wrong means please guide me to correct it .

Thanks in Advance

Solution

  • Your model looks fine, and does what you want it to; specifically:

    • Each UnitReference can have many SubProcedures
    • Each SubProcedure can have many TestReferences
    • A UnitReference can have many TestReferences

    If that is what you want then you have done it correctly.

    One thing I would note though is that you have redundancy in your model. You can navigate from UnitReference to TestReference via two different paths: directly, or via SubProcedure. This means that one of these relationships is redundant. Let me explain using LINQ method syntax:

    //To get all SubProcedures for a UnitReference
context.UnitReferences.Find(UnitReferenceId).SubProcedures;

//To get all TestReferences for a UnitReference
context.UnitReferences.Find(UnitReferenceId).TestReferences;

//instead of the line above you could use the other relationship like this
context.UnitReferences.Find(UnitReferenceId).SubProcedures.SelectMany(s => s.TestReferences);

    There is nothing wrong with having redundancy - it can be quite useful. Just be aware that you have two separate relationships to maintain in the case of an update. For example, if you wanted to move a TestReference from one SubProcedure to another, you need to update the .SubProcedure property of the TestReference, but also the .UnitReference property to the .UnitReference property of the new SubProcedure, or the data will be inconsistent.