1 / 21

Unary Relationships

Unary Relationships. On the Job. Is this a legitimate design? Do you see problems with it?. The Family. Is this a legitimate design? Do you see problems with it?. Complicated Relationships Among People*. Workers have supervisors, who also have supervisors

allen-kerr
Download Presentation

Unary Relationships

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Unary Relationships

  2. On the Job • Is this a legitimate design? • Do you see problems with it?

  3. The Family • Is this a legitimate design? • Do you see problems with it?

  4. Complicated RelationshipsAmong People* • Workers have supervisors, who also have supervisors • Men and women can marry more than once • Children born out of wedlock • Half-brothers and -sisters • What design will accommodate all of these? * Not intended as a comment on social mores.

  5. Relating an Entity to Itself • An entity can be related to itself. • Called a “unary” or “recursive” relationship • Where do you put the foreign keys?

  6. Unary 1:M • Recursive foreign key placed in entity • Name of FK must be different from name of PK • Second instance of entity required in queries and relationships screen

  7. Unary 1:1 or M:M • Associative entity required

  8. Unary 1:1 or M:M • Name of FKs must be different in associative entity • Second instance of entity required in queries and relationships screen

  9. Common Uses of Unary Relationships • People • sometimes supertype/subtype required to fully document relationship • Bill of Materials • products composed of other products • Political Geography • Precincts within cities within counties within states within countries within continents

  10. More about the 1:1 or M:M Interpersonal Unary Relationship • Interpersonal relationships are often two-way, with the reciprocal relationship sometimes having a different name • Parent-child • Sibling-sibling • Spouse-spouse • Supervisor-worker • Employer-employee

  11. More about the 1:1 or M:M Interpersonal Unary Relationship • In general, it is better to have only one record for a given relationship • Do not have • One record where person A is “child of” B • A second record where B is “parent of” A • Instead • A single record that records both directions of the relationship • Requires that there be the ability to indicate the reciprocal of a relationship

  12. A Solution for the 1:1 or M:M Interpersonal Unary Relationship A “Relationship Type” table is created to hold both “forward” and “reciprocal” terms for the relationship

  13. A Solution for the 1:1 or M:M Interpersonal Unary Relationship • Only one entry per pair (if 1:1)

  14. A Solution for the 1:1 or M:M Interpersonal Unary Relationship • Carefully define the directionality • In these examples • [second person] is the [relationship] of [first person] • [first person] is the [reciprocal] of [second person]

  15. A Solution for the 1:1 or M:M Interpersonal Unary Relationship • Create a view for one direction that includes PersonID, Name, and Relationship with Other Person

  16. A Solution for the 1:1 or M:M Interpersonal Unary Relationship • Expression that returns the “Relationship with Other Person” Relationship: [tblRelationshipType]![RelationshipType] & " of " & fnNameFirstLast([tblPerson_1]![FirstName], [tblPerson_1]![MidName],[tblPerson_1]![LastName])

  17. A Solution for the 1:1 or M:M Interpersonal Unary Relationship • Create a view for the reciprocal direction

  18. A Solution for the 1:1 or M:M Interpersonal Unary Relationship Data entry form allows for the specification of relationship in one direction

  19. A Solution for the 1:1 or M:M Interpersonal Unary Relationship Already-entered reciprocal relationships are shown to discourage duplicate entries

  20. A Solution for the 1:1 or M:M Interpersonal Unary Relationship • A UNION query returns all records from both views SELECT * FROM vueSecondPersonRelationship UNION SELECT * FROM vueFirstPersonRelationship; • Must be entered in SQL view • Normally, UNION query eliminates duplicate rows

  21. A Solution for the 1:1 or M:M Interpersonal Unary Relationship • A UNION query returns all records from both views There is only one relationship record for persons 1 and 2, but both directions of the relationship are returned

More Related