Got a table of people I want to connect with each other, many to many, and the links are bidirectional

Question

Imagine that you live on a very simplified example of the earth - and imagine that there is a table of people in your MySQL database:

create table person ( person_id int, name text ) select * from person; +-------------------------------+ | person_id | name | +-------------------------------+ | 1 | Alice | | 2 | Bob | | 3 | Carol | +-------------------------------+ 

and these people should collaborate / work together, so you have a link table that links one person’s record to another:

 create table person__person ( person__person_id int, person_id int, other_person_id int ) 

This setting means that the links between people are unidirectional - that is, Alice can refer to Bob without linking Bob to Alice and, even worse, Alice can link Bob, and Bob can link to Alice at the same time, in two separate link entries. Since these links represent a working relationship, in the real world this is all two-way relationship. In this setting, everything is possible:

 select * from person__person; +---------------------+-----------+--------------------+ | person__person_id | person_id | other_person_id | +---------------------+-----------+--------------------+ | 1 | 1 | 2 | | 2 | 2 | 1 | | 3 | 2 | 2 | | 4 | 3 | 1 | +---------------------+-----------+--------------------+ 

For example, with person__person_id = 4 above, when you view the Carol profile (person_id = 3), you should see a relationship with Alice (person_id = 1), and when you view the Alice profile, you should see a relationship with Carol, although the link goes to another side.

I understand that I can make allied and different queries, and still not represent the relationship as mutual in the user interface, but is there a better way? I got the feeling that there is a better way: the one where this problem will melt carefully, setting up the database correctly, but I do not see it. Has anyone got a better idea?

Answer 1

I'm not sure if there is a better way to tune your tables. I think that the way you have them is the right one and will be the way I implement it.

Since your relationship table may indicate unidirectional relationships, I would suggest treating them as such. In other words, for each link, I would add two lines. If Alice collaborates with Bob, the table should be as follows:

 select * from person__person; +---------------------+-----------+--------------------+ | person__person_id | person_id | other_person_id | +---------------------+-----------+--------------------+ | 1 | 1 | 2 | | 2 | 2 | 1 | +---------------------+-----------+--------------------+ 

The reason is that in many ActiveRecord (Rails) systems, such as systems, a multi-valued table object would not be smart enough to request both person_id and other_person_id. By storing two lines, objects like ActiveRecord will work correctly.

What you have to do is ensure the integrity of your data at the code level. Each time a relationship is established between two users, two entries must be inserted. When a relationship is destroyed, both records must be deleted. Users are not allowed to establish relationships with themselves.

Answer 2

There is no better idea. Relational databases cannot provide what you ask, so you need to write a special query to retrieve data and a trigger to force constraint restrictions.

To get related parties for @person, I would go for:

 SELECT CASE person_id WHEN @person THEN other_person_id ELSE person_id END as related_person_id FROM person_person WHERE ( person_id=@person OR other_person_id=@person ) 

Answer 3

I do not see the possibility of using simple relational concepts. You will need to add the code "business" to ensure compliance with your relationship between person and person.

• One way could be to force the second record of relationships using an insert trigger
• then declare one of the entries "primary" (for example, one where person_id is less than the other _person_id)
• then create views and glue code for your applications (select, update, delete) that access data with this knowledge.

Answer 4

You should find this post helpful:

http://discuss.joelonsoftware.com/default.asp?design.4.361252.31

As @user posted, it’s usually better for you to create two entries for a bi-directional relationship (Alice to Bob and Bob to Alice in your example). It makes many requests easier and accurately reflects the relationship. If you really have a true one-way relationship, this is the only way to fly.