locked
How to model two tables with two relationships? RRS feed

  • Question

  • I have two tables in my project-- Person and Employee.  Right now, there is a 1<--->0..1 relationship between Person and Employee.  Meaning that a Person can have one Employee and an Employee must have a Person.

    Person in this instance is a one-stop shop for all information regarding someone-- name, phone #, address, etc.

    Things got a bit more complex, in that now we want employees to have zero or one emergency contacts.  Those emergency contacts are, naturally enough, Person entities.  

    So now I have to have two relationships between Employee and Person-- the 'Personal' information for the Employee, and the 'Personal' information for the Employee's emergency contact.

    I took a quick shot at it in the data designer but got errors mentioning circular references, so I obviously did something wrong.

    Is it possible to model two different relationships between entities?  

    Tuesday, March 22, 2016 6:02 AM

Answers

  • Sorry Jim you're correct.

    Another way to handle this is to have a Many to many between Employee and Person for Emergency Contacts.  You'd do that by creating a new table EmergencyContacts and two relationships between that and Employee, that and Person respectively.  That way your emergency contacts collection would be Employee.EmergencyContacts.

    Alternatively, you could create a self-referential relationship between Person and Person zero-or-one to many.  That way your emergency contacts collection would be Employee.Person.EmergencyContacts

     HTH,

    Josh



    • Marked as answer by jim bancroft Wednesday, March 23, 2016 2:55 PM
    • Edited by joshbooker Tuesday, March 29, 2016 2:42 PM
    Wednesday, March 23, 2016 11:52 AM

All replies

  • Hi Jim,

    Are you using the intrinsic db?  It should be fine either way, but with external db you'll need to add a field to the table for the foreign key.

    This works for intrinsic db:

    Relationship1: Employee.Person

    From: Employee

    Multiplicity: Zero or One

    NavProp: Person

    To: Person

    Multiplicty: One

    NavProp: Employee

    Relationship2:  Employee.EmergencyContact

    From: Employee

    Multiplicity: One

    NavProp: EmergencyContact

    To: Person

    Multiplicity: Zero or One

    NavProp: Employee_EmergencyContact

    HTH,

    Josh

    Wednesday, March 23, 2016 2:04 AM
  • Hi Josh,

    Thanks for double-checking my schema modification.  Does it matter about the From and To bits, if they're reversed in my instance?  My relationships are from Person to Employee but the multiplicities are the same as yours I believe, so I think it should be ok?

    (Yes, I'm using the ApplicationData intrinsic database.)

    I'll attach a couple of screenshots to show what I'm doing.  Right now, with this schema I get compile errors about there being "an invalid circular relationship between types" and "Remove or fix one of those relationships: Relationship 'Person_Employee1' inside the Module 'LightSwitchServiceApplication', Relationship 'Person_Employee' inside the Module 'LightSwitchServiceApplication'"

    If I remove the second relationship, the 'EmergencyContact" one, all is well again.



    Wednesday, March 23, 2016 5:33 AM
  • Sorry Jim you're correct.

    Another way to handle this is to have a Many to many between Employee and Person for Emergency Contacts.  You'd do that by creating a new table EmergencyContacts and two relationships between that and Employee, that and Person respectively.  That way your emergency contacts collection would be Employee.EmergencyContacts.

    Alternatively, you could create a self-referential relationship between Person and Person zero-or-one to many.  That way your emergency contacts collection would be Employee.Person.EmergencyContacts

     HTH,

    Josh



    • Marked as answer by jim bancroft Wednesday, March 23, 2016 2:55 PM
    • Edited by joshbooker Tuesday, March 29, 2016 2:42 PM
    Wednesday, March 23, 2016 11:52 AM
  • Good points Josh, I'll investigate those alternatives, either one sounds fine for my purposes. Much appreciated.

    I am a bit curious why my first attempt failed; I kind of see how a circular relationship could result based on the diagrams above ('Person must have an Employee' and 'Employee must have a Person') but if that's the case I wonder why the designer let me do it. 

    I thought the different named navigation properties would separate the relationships appropriately but I'm not familiar enough with how LS handles the entity framework plumbing to say for certain.  My initial use case is a bit out of the ordinary I'll admit.

    Wednesday, March 23, 2016 2:55 PM