StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POuser activity database structure
text
Body
copied!<p>I am working on a community website. I want to show the user's activity in <strong>2</strong> places in the website.</p> <ol> <li>The User "A" Profile.</li> <li>The Friends page of the user "A" friends. <em>"What are your friends doing?"</em></li> </ol> <hr> <p>The tables for instance are:</p> <ul> <li>members</li> <li>members_gallery</li> <li>members_videos</li> <li>members_friends</li> </ul> <hr> <p>my problem is in the Sql structure. I've read this question <a href="https://stackoverflow.com/questions/561961/user-recent-activities-php-mysql">"User recent activities - PHP MySql"</a></p> <p>The "union" idea is good but I have an alternative one. I am going to make a new table called</p> <ul> <li>members_activity</li> </ul> <p>The fields:</p> <pre><code>id | user_id | photo | video | friend | p_id | v_id | f_id | datetime </code></pre> <p>let's say that the user has just <em>uploaded an image</em>.</p> <pre><code>id | user_id | photo | video | friend | p_id | v_id | f_id | datetime 1 | 15 | 1 | 0 | 0 | 1203 | 0 | 0 | NOW() </code></pre> <p><strong>advantages:</strong></p> <ul> <li>When i make a SELECT QUERY, i can easily know if it's a photo, video, or a friendship activity.</li> <li>The user can delete the 'photo activity' but keep the photo.</li> <li>Can notify friends of the user easily.</li> </ul> <p><strong>disadvantages:</strong></p> <ul> <li>Huge number of table rows?</li> </ul> <p>Any ideas, or suggestions how the big websites deal with it? digg, facebook, etc. </p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

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