database plan

[beaaast337]

Again, I am back to you great guys to help me.

For those that don't know me, I'm a young guy learning how to script PHP and handle mySQL by doing a major project.

I began planning the project with this post (http://www.webdeveloper.com/forum/showthread.php?t=103906).

What exists now:
A database table called users, each user with a unique ID

What I want:
I want each user to be able to have 'friends' like myspace or similar sites. The catch is that I want them to be able to organize their friends into groups.

I'd like users to be able to create as many groups of friends as they want and name the groups whatever they want. I have many ideas to accomplish this.

So really the big problem is how to structure the databases.

[Nedals]

Create a table called groups where you can save the group names.
Add a column to the user table called group_id.

Now any user can be put into any group.

[beaaast337]

I'm sorry, maybe I phrased my question incoherently...

There will be multiple groups...out of many friends

I need a structure for the many friends, and the multiple groups of those friends

Your reply is a way to name the groups but does nothing else. Thank you anyway

[Ultimater]

You can use multiple tables for this purpose.

table: user_links

+---------+----------------+---------------+--------------------------------+
| user_id | group_name |link_text | link_href |
+---------+----------------+---------------+--------------------------------+
| 20 |search engines | yahoo | http://www.yahoo.com/ |
| 12 |search links | google | http://www.google.com/ |
| 37 |msn links | msn home | http://www.msn.com/ |
| 12 |search engines | yahoo | http://www.yahoo.com/ |
| 12 |search engines | aol | http://www.aol.com/ |
| 37 |forums | web d | http://www.webdeveloper.com/ |
| 37 |forums | http guru | http://www.httpguru.com/ |
| 12 |mail | aol | http://www.aol.com/ |
+---------+----------------+---------------+--------------------------------+

To get all the entries made by user 12:

SELECT group_name,link_text,link_href FROM user_links WHERE user_id=12


Which contains enough information to produce an array something like this:

array(
"search links" => array(
array("google", "http://www.google.com/")
),
"search engines" => array(
array("yahoo", "http://www.yahoo.com/")
array("aol", "http://www.aol.com/"),
),
"mail" => array(
array("aol", "http://www.aol.com/")
),
)

[Nedals]

Your reply is a way to name the groups but does nothing else.Then perhaps my reply was incoherent:)

User table:
+--------+---------+----
| user_id | group_id | .....

Group table:
+---------+------+-------
| group_id | name | other info (which you are sure to add in the future )

You can have as many groups as you wish, AND any user can belong to any group.

[delr2691]

Hey again.. Well I think you should create another table for the groups:
--- tbl_groups ---
ID <-- group unique ID
groupName <-- group name
userID <-- user who owns the group

also create a relational table which will relate -obviously- the friends to the groups.. and so, with the other user..
--- tbl_friends ---
friendID <-- the friend ID
groupID <-- the group ID

so with a sql like this you will grab all friends for user 1 in group 1:

SELECT tbl_friends.friendID, tbl_friends.groupID, tbl_groups.name as groupName, tbl_users.ID, tbl_users.name as friendName FROM tbl_users, tbl_groups, tbl_friends WHERE tbl_friends.groupID=tbl_groups.ID AND tbl_groups.ID=1 AND tbl_groups.userID=tbl_users.ID AND tbl_users.ID=1

[NogDog]

I would see at least 3 tables:

users : user_id, user_name, etc.
groups : group_id, group_name, created_by (user_id)
user_to_group : user_id, group_id

There would be a row in user_to_group for each member of each group.