[RESOLVED] Need an idea for MySQL tables design (facebook style wall script)

lpa

Need an idea for MySQL tables design for a Facebook style wall script.

When user publish something, he or she can select one or more friends groups who can view this post.

All users can subscribe to their friends posts. Each friend belong to some group of friends and can see only posts marked for this group.

I know solution if each post is marked for a single group:

SELECT w.id, w.text w.uid, w.allowed_group u.username from wall_posts w join usertable u on w.uid=u.id
where w.allowed_group IN (SELECT belong_to_group FROM subscriptions WHERE subscriber = '$id') 
order by w.id desc limit $start, $step") or die('Error');

But what if a post is available for 2,3...5 groups? I have no idea how can I design tables for this situation.

Yes, I know, I can save allowed_group this way - "2,4,5,7,8" but if I do it, the column can not be INT and this will lead to full (huge) table scan and this will kill the server because table will contain millions of rows.

Any ideas for this situation?

traq

you need an association table.

-- psuedocode ! ! !

CREATE TABLE user ...

CREATE TABLE group ...

CREATE TABLE user_group(
    user_id
   ,group_id
);

bradgrafelman

And since I've always been a fan of fully describing constraints/relations inside the DB schema... if you're using InnoDB, you might want to add FOREIGN KEY constraints in that user_group table.

lpa

traq, I have an associaten table called "subscriptions". Probably my question is not clearly defined. An example.

I have a friend John and I belong to his group of friends with ID 123.

John posts something to the wall and allows to read this post to all friends who belong to the group 123.
The script will save to the MySQL table data in this way;

$result = mysql_query("INSERT INTO wall_posts
VALUES (NULL,'$poster','$post_text,'$friends_group_who_can_view_this_post')") or die('Error');

No problems so far and I can easy get all posts which I may read:

$result = mysql_query("SELECT poster, text, FROM wall_posts 
WHERE friends_group_who_can_view_this_post='123'") or die('Error');

But now John posts another post and allows to read it to all friends who belong to groups 77, 123 and 321.

If I save it in this way:

$result = mysql_query("INSERT INTO wall_posts 
VALUES (NULL,'$poster','$post_text,'77,123,321')") or die('Error');

it will cause selection problems because the column friends_group_who_can_view_this_post can not be INT and this will lead to full table scan.

The only solution that I see is to save all allowed groups as different posts:

[code=php][code=php]$result = mysql_query("INSERT INTO wall_posts VALUES (NULL,'$poster','$post_text,'777')") or die('Error');

$result = mysql_query("INSERT INTO wall_posts VALUES (NULL,'$poster','$post_text,'123')") or die('Error');

$result = mysql_query("INSERT INTO wall_posts VALUES (NULL,'$poster','$post_text,'321')") or die('Error');

[/code][/code]

But I don't know if it's the best solution. There are scripts like Phpfox that allow to set different and complicated permissions for each post (can read: friends, friends of friends, specific group of friends) but I don't want to spend 300 USD for a copy of script just to see 1 function.

bradgrafelman

That last method you described is close, but you're still duplicating data.

What you'd want to do is remove the 'group' column altogether from the wall_posts table and instead have a separate table that relates posts to groups. That separate table would need to contain only two pieces of data - a PRIMARY (or UNIQUE) KEY of a wall_post, and a PRIMARY (or UNIQUE) KEY of a group. (Note it could still contain other attributes specific to the relationship... examples might be when the relationship was created, who created the relationship, etc.)

To get the posts that are associated with a given group, then, you'd simply do an INNER JOIN between the wall_posts table and this new table where the group column(s) in the new table contain the value(s) of a given group. You could even show multiple group at a time, e.g. "WHERE new_tbl.group IN (123, 456, 789)".

EDIT: For a more general description of what's going on here... you've got a many-to-many relationship between posts and groups. For N-M relationships, you'll want a separate table that contains candidate keys for both sides of the relationship - one row per "association." So if post #1 belongs to group 1 and 2 and post #2 belongs to group 2, 3, and 4, you'd have these tuples in your separate cross-reference table (post ID listed first):

(1, 1), (1, 2), (2, 2), (2, 3), (2, 4)

If the relationship is instead 1-N ("one-to-many"), then you could instead store the primary key of the 'N' side of the relationship inside of the table for the '1' side's data. For an example, if you have many groups but a given post is only allowed to belong to at most one group at a time, the '1' side would be the posts table and the 'N' side would be the groups table.

lpa

thanks bradgrafelman.