PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Modeling a relationship with SQL

I have the following in tables in PostgreSQL:

Relationship:

  • ID: int
  • Status: string

RelatedParty:

  • RelationshipID: int
  • PersonID: int
  • Role: string

The idea is that a relationship has two related parties, each of which has a role in the relationship. Only two parties can have the same relationship, and any two parties can have at most one relationship with the identical roles. I’m having trouble figuring out how to ask the following questions:

  • With which parties do I have a relationship where I have the "employer" role and the other party has the "employee" role
  • Retrieve the relationship where I (party 1) have the role of "employer" and somebody else has the role of "employee"?

(The roles here are arbitrary for the sake of this example. The actual set of roles is larger.) I’m imagining some sort of select with two sub-selects to pick the two parties, but I can’t quite get there from here.

Tags:

2

Answers


  1. 0

    It all depends on how these relationships have been modelled in Postgres, looks like you’re going to have to chase down some examples to help make sense of it.

    I wonder if in the RelatedParty table, for a given relationship Id, you’d expect to find 2 (or more??) related parties.

    As you correctly identify, it wont be clear who plays the part of the employer or the employee for each relationship, but I suspect that’s what the role attribute is there to do.

    A better model for most relationships is to adopt a subject, predicate, object model, where the predicate describes a directed relationship.

    For example, Employs is a directed relationship, and in usage, if Subject:Company XYZ, Employs, Object: Person x, it’s clear who’s who, and you don’t need that additional "role" field.

    You could express all that in a single table with Subject, Predicate, Object as fields. The advantage is you only need one row per relationship. The disadvantage is that it’s difficult to express relationships that draw together more than two parties at once.

    If you want to search all the employment relationships, in this proposed model, all you have to do is select * where predicate = 'Employs', if you want all the relationships where you employ someone, select * where subject = 'You'. And so on.

    But that’s just one out of many ways someone might have chosen to model this, looks like they chose something a bit more fluffy in this case.

    In this model, you’d have to first create a view by creating a join on the RelatedParty table using the RelationshipID field, and expressly picking out the roles played by parties (assuming there are only two role-types you’re seeking to access, say Employer and Employee) to convert to the Subject(role=Employer ), Predicate, Object (role=Employee) format outlined earlier, where in this case, Predicate would be "Employs".

    Something like:

    select 
employer.PersonID as subject, 
'employs' as predicate, 
employee.PersonID as object
from 
RelatedParty employer 
join RelatedParty employee
on employer.RelationshipID = employee.RelationshipID
   and employer.role = "employer"
   and employee.role = "employee"

    But a lot hinges on how those role fields are used/populated.

    If you set that up as a view, you could then select from that, the employers or employees of your choice and see what’s what.

    Login or Signup to reply.
  2. 0

    You could join to RelatedParty twice and get both persopns and their role on the same row. This returns all the relationships which someone has employee and someone has employer.we join on the R.ID since an R.ID must have 2 and only 2 related records. we also know the personID fo each can’t be the same or it would violate PK constrains of related party. so we just need to make sure the PersonID from the first related party doesn’t match the personID of the 2nd party.

    SELECT R.ID 
FROM Relationship R
INNER JOIN RelatedParty RP1
 on R.ID = RP1.RelationshipID
AND RP1.PersonID < RP2.PersonID
INNER JOIN RelatedParty RP2
 on R.ID = RP2.RelationshipID
AND RP1.PersonID < RP2.PersonID
WHERE ((RP1.Role = 'Employer' and Rp2.Role = 'Employee') OR 
      (RP2.Role = 'Employer' and Rp1.Role = 'Employee'))

    we use < on the RP1.personID vs PR2.PersonID so we don’t have do deal with the duplication resulting from having Person A in RP1 and Person B in RP2 vs Person B in RP1 and Person A in RP2.

    By using < instead of <> we avoid the duplicate.

    I guess we don’t even need the relationship table we could just do a self join

    So to answer:

    With which parties do I have a relationship where I have the "employer" role and the other party has the "employee" role

    SELECT *
FROM RelatedParty RP1
INNER JOIN RelatedParty RP2
 on RP1.RelationshipID = RP2.RelationshipID
AND RP1.PersonID < RP2.PersonID
WHERE ((RP1.Role = 'Employer' and Rp2.Role = 'Employee') OR 
       (RP2.Role = 'Employer' and Rp1.Role = 'Employee'))

    I don’t understand the second question well enough to formulate a response for it.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search