Storing connections between people in a MySql database

Question

I would like to create a database where I store the links between people.

I then need to bring back the records detailing which people are connected to which others.

I need to output: person1 is connected to person2 is connected with person3 etc.

How can I get started with this task?

Answer 1

Ok, let me assume you mean you want to find out the relationships between certain entries in a MySQL database. The ideal way - in my opinion - would be to have your users table which in this case we shall call tblusers. I am sure you'd have many columns, but for this connection sake we can name one column 'ucons' for the relationships with other users.

As you may or may not be aware, the users table will have a primary key column, in this case I will name it 'uid'. These primary keys are ultimately used to target your user entries, as each user receives a unique id in the primary key column 'uid'.

Lets say we have three users: user1, user2, user3 who have the following user ID's:

user1 -> 1

user2 -> 2

user3 -> 3

Now, to your question about relationships, I would usually store the relationship keys in a separate table, this is because you could have thousands of users, and if each user had 200 connections, that is a lot of overhead work on the table, especially if other operations are being performed on the table at the same time! This is known as a one to many - many to one relationship.

Now, lets have a seconds table with two columns for our actual 'connection' between people, we shall call it 'tblconnections' with columns 'cid' 'cuser' and 'ccon'.

cid is a primary key cuser is the ID of the user from tblusers who has just created a connection ccon is the ID of the CONNECTED user in tblusers

So lets take the following two table layouts:

tblusers:

-------------------
uid | uname | uage
-------------------
0   | Joe   | 22
1   | Bob   | 54
2   | Tod   | 14

tblconnections

-------------------
cid | cuser | ccon
-------------------
0   | 1     | 2
1   | 1     | 0

This now tells use that user with id '1' has two connections, one to user '2' and one to user '0' in tblusers, so user 1(Bob) has a connection to Tod as well as Joe.

With this info you can perform a simple mysql query such as:

SELECT uid AS id, (SELECT GROUP_CONCAT(cconn) FROM tblconnections WHERE cuser = id) FROM tblusers;

This will return your usual iser id from 'tblusers' as well as a concatenated string of all connected users. So the user id may be '3' and the concatenated string may look like this: 2,6,22,45,67,8 which is the list of all users connected to user '3' in this case.

Please note, there are MANY other methods for returning each connected users info along with your query, this is simply an example.

If you want to read up on tutorials, simply google "database relationships", "one to many many to one relationships" or "mysql table relationships".

Hope this sheds some light on your issue.

Have a good one.