database structure for google plus circles?

I know for sure that Google does not use mysql, but in my case I happen to work on a project using mysql and has features that are very similar to circles:

1. user can belong to many circles
2. user can be add/removed from circles
3. posts can be public or can be shared to circles/individual users
4. if a post is shared to a circle, and new user is added to this circle then this user can also view the post.
5. If a post is shared to a circle, and an user is removed from this circle then: a. he/she can still view the post if he/she replied in the post b. he/she cannot view the post anymore otherwise

As you can already see, with the above requirements there are a lot going on in the database. If I really share both to circles and individual users, i will probably need 2 One2Many tables. If I share only to individual users by getting the list of users for each circle at the very beginning, then I run into troubles later on when users edit these circles.

Currently, my get-around hack is to share to circles only, even for each individual user I create a 1 user only circle.

So my current database tables look a bit like this:

circle_to_user:

id
circle_id
user_id
friend_id

post:

id
user_id
is_public

post_to_circle

id
post_id
circle_id

To query out the list of posts a user can view, the query is rather complicated and consists of multiple joins:

$q = Doctrine_Query::create()
            ->addSelect('s.*')
            ->addSelect('u.id, u.first_name, u.last_name, u.username, u.avatar')
            ->from('UserStatus s')
            ->leftJoin('s.User u')
            ->orderBy('s.created_at DESC');

        $userId = sfContext::getInstance()->getUser()->getUserId();
        if ($userId == $viewUserId) {
            $q->orWhere('s.user_id = ?', $userId);
            $q->orWhere('s.user_id IN (SELECT 开发者_高级运维DISTINCT cu1.friend_id FROM CircleUser cu1 WHERE cu1.user_id = ?) AND s.is_public = ?', array($userId, true));
            $q->orWhere('s.id IN (SELECT DISTINCT(us2.id)
                                                        FROM
                                                                UserStatus us2 INNER JOIN us2.UserStatusCircles usc2 ON usc2.user_status_id = us2.id
                                                                INNER JOIN usc2.Circle c2 ON c2.id = usc2.circle_id
                                                                INNER JOIN c2.CircleUsers cu2 ON cu2.circle_id = c2.id AND cu2.friend_id = ?)', $userId);
        } else {
            $q->orWhere('s.user_id = ? AND s.is_public = ?', array($viewUserId, true));
            $q->orWhere('s.id IN (SELECT DISTINCT(us1.id)
                                                        FROM
                                                                UserStatus us1 INNER JOIN us1.UserStatusCircles usc1 ON usc1.user_status_id = us1.id AND us1.user_id = ?
                                                                INNER JOIN usc1.Circle c1 ON c1.id = usc1.circle_id
                                                                INNER JOIN c1.CircleUsers cu1 ON cu1.circle_id = c1.id AND cu1.friend_id = ?)', array($viewUserId, $userId));
            $q->orWhere('s.id IN (SELECT DISTINCT(us2.id)
                                                        FROM
                                                                UserStatus us2 INNER JOIN us2.UserStatusCircles usc2 ON usc2.user_status_id = us2.id AND us2.user_id = ?
                                                                INNER JOIN usc2.Circle c2 ON c2.id = usc2.circle_id
                                                                INNER JOIN c2.CircleUsers cu2 ON cu2.circle_id = c2.id AND cu2.friend_id = ?)', array($userId, $viewUserId));
        }

I hope that the above info is not too long, I just want to give lots of details. My questions are:

1. Given the above requirements, is my implementation good enough, or is there anything I should change to make it better?
2. I want to search for articles regarding this type of specific database design problem but could not find much, is there any technical term for this type of database design
3. Would you suggest any alternatives such as using another type of database, or perhaps index the posts with a searchengine like elastic and let it handle the search instead of using mysql?

Thank you very much for reading until this point, if you find anything I should change in the question to make it easier to follow and to answer, please do let me know.

while your single user circle sounds like a nice try, how will you go about distinguishing it on the way out? when you see a post is shared to a circle, how do you know if that circle is a genuine one or a single user? because i imagine you want to display them differently on the interface. and you'd probably need to know when you fetch the fake circles from the db that the user should not be able to edit them.

while you might get away with avoiding linking to users you now have to handle special circle cases. i'd say go with your 2 x 1-* tables that link a post to multiple circles and separately to multiple users.

perhaps to encourage you to review your intention and leaving aside the 'friend' relationship that may add a special case, as i see it you're more or less looking to: fetch all posts that are public, or are shared with my user, or are shared with a circle i am in, or are posts that i replied to. that isn't too complicated i don't think and you don't have to get a list at the beginning or anything.

(on a related note, multiple JOINs is not a problem that stands out. more importantly you have multiple sub-queries. usually that is bad news. in most cases they can be reworked as normal joins and usually more cleanly).

This kind of problem is mostly not solved with a relational model , i think google uses the datastore , which then sits on bigtable , cassandra and hadoop equivalent.

I am also in same problem but i can suggest you something that i allready covered/completed that dont make two tables for post instead of that add column in Posts table named with/circle_id. And i also i want to tell you that add a/or more default circle entry(specifically Public and also All Friends/Circles in Circles table. Now your Post Pickup query will be like this.

    $id=$_SESSION["loged_in_user_id"];    
    $sql="SELECT * FROM `posts` as p,`circles` as c WHERE c.circle_create_id=$id and (p.with=c.id or p.with=1)";//p.with columns contain circle id and as i tell first entry will be public
$sql_fier=mysqli_query($sql);
/*-------I think you know how to manipulate fetched data---------*/

Connect me on social network http://www.funnenjoy.com (signup/login is required )