Databases
StackOverflow2013
Postsdbo
POdatabase structure for google plus circles?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POdatabase structure for google plus circles?
    primarykey
    data
    text
    <p>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:</p> <ol> <li>user can belong to many circles</li> <li>user can be add/removed from circles</li> <li>posts can be public or can be shared to circles/individual users</li> <li>if a post is shared to a circle, and new user is added to this circle then this user can also view the post. </li> <li>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</li> </ol> <p>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.</p> <p>Currently, my get-around hack is to <strong>share to circles only</strong>, even <strong>for each individual user I create a 1 user only circle</strong>. </p> <p>So my current database tables look a bit like this:</p> <p>circle_to_user:</p> <pre><code>id circle_id user_id friend_id </code></pre> <p>post:</p> <pre><code>id user_id is_public </code></pre> <p>post_to_circle</p> <pre><code>id post_id circle_id </code></pre> <p>To query out the list of posts a user can view, the query is rather complicated and consists of multiple joins:</p> <pre><code>$q = Doctrine_Query::create() -&gt;addSelect('s.*') -&gt;addSelect('u.id, u.first_name, u.last_name, u.username, u.avatar') -&gt;from('UserStatus s') -&gt;leftJoin('s.User u') -&gt;orderBy('s.created_at DESC'); $userId = sfContext::getInstance()-&gt;getUser()-&gt;getUserId(); if ($userId == $viewUserId) { $q-&gt;orWhere('s.user_id = ?', $userId); $q-&gt;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-&gt;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-&gt;orWhere('s.user_id = ? AND s.is_public = ?', array($viewUserId, true)); $q-&gt;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-&gt;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)); } </code></pre> <p>I hope that the above info is not too long, I just want to give lots of details. My questions are:</p> <ol> <li>Given the above requirements, is my implementation good enough, or is there anything I should change to make it better?</li> <li>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</li> <li>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?</li> </ol> <p>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.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USpoop-deck
    1. USmr1031011
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POdatabase structure for google plus circles?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POdatabase structure for google plus circles?
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POdatabase structure for google plus circles?
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    1. CODid you ever got around this issue ? I am facing a similar problem .
      singulars
      1. POdatabase structure for google plus circles?
      1. USPit Digger
    2. COnot quite, our final solution was not really optimized in term of performance I think. Probably we need to rethink the data structure (or even why we need to do that). Please share if you find anything
      singulars
      1. POdatabase structure for google plus circles?
      1. USmr1031011
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload