CODEWITHSUNDEEP
postgresqldatabase-schema
Maxem
Maxem

Reputation: 2684

Self m:n Relation

I have persons and a person can contact multiple other persons, so basically the "default" tables would be:

persons (id)
contacts (person1_id, person2_id)

With this schema, I'd have to issue queries like 

SELECT   * 
FROM     contacts c
WHERE    ( person1_id = *id of person1* AND person2_id = *id of person2* )
         OR
         ( person1_id = *id of person2* AND person2_id = *id of person1* )

to get the relation between two persons when I insert such a relation only once.

What is the common practice to deal with this situation?

  1. Insert data once and do such an OR query
  2. Insert the relation twice so that person1_id = id of person1 AND person2_id = id of person2 is enough
  3. An entirely different approach?

Assuming:

Upvotes: 1

Views: 469

Answers (4)

Denis de Bernardy
Denis de Bernardy

Reputation: 78553

"Insert the relation twice so that person1_id = id of person1 AND person2_id = id of person2 is enough"

That is how I'd do it, personally. It allows to deal with the situation where A has the contact details of B but not the other way around (e.g. a girl gives a guy her number at the bar saying "call me" as she walks out). It also makes the queries simpler.

Upvotes: 0

Elkas
Elkas

Reputation: 599

Try this one mate =)

SELECT c.person1_id as id_person_1, c.person2_id as id_person_2, p1.name as name_person_1, p2.name as name_person_2
FROM contact c
LEFT JOIN person p1 ON p1.id = c.person1_id
RIGHT JOIN person p2 ON p2.id = c.person2_id;

I don't know if it will work.. but give it try mate =)

Upvotes: 0

Elkas
Elkas

Reputation: 599

Why don't you use Join between the tables?

something like this:

SELECT *
FROM contact c INNER JOIN person p ON p.id = c.person1_id

The the where and group bys you need to complete you're query =)

Take a look here how the results will be showed: http://www.w3schools.com/Sql/sql_join_inner.asp

Regards,
Elkas

Upvotes: 0

Alanyst
Alanyst

Reputation: 1410

If you write your insert logic such that person1_id < person2_id is true for all rows, then you can just write

SELECT *
FROM contacts c
WHERE person1_id = min(*id_of_person_1*, *id_of_person_2*)
AND person2_id = max(*id_of_person_1*, *id_of_person_2*)

Upvotes: 2

Related Questions