CODEWITHSUNDEEP
phpmysqldatabase-design
Joe
Joe

Reputation: 8292

How should a Facebook-Timeline system look on the database?

How would I store Users "Posts"? So that I can efficiently get them from the db and then process them to display chronologically when a specific users page is requested?

Do I store all Posts from All Users in a single table like so:

Post ID | Poster ID | Post Content | Posted to this Users Wall | Post Timestamp

Then when a user opens UserFoo's Page I just get all rows where Posted to this Users Wall = UserFoo?

Wouldn't the sample above make a table bulky?

Upvotes: 1

Views: 1612

Answers (2)

Ivan Yonkov
Ivan Yonkov

Reputation: 7034

users

id | name |

posts

|  id   |   u_id    |   content    |

wall

| id   |   u_id    |  post_id    |

u_id from posts is users.id where it's the Author

u_id from wall is users.id where it's the Target (on which wall is posted)

you can name it much more clear i.e. poster_id, target_id

Another way is to have

post

| id | poster_id |

wall 

| id | post_id  | target_id |

content

|  post_id | content |

You can also add another specific things, such as if the post is a comment or something, in another table, or a column in post table

function getUsersWallPosts($target_id) {
    $query = "SELECT c.content FROM content AS c, INNER JOIN wall AS w ON w.post_id = c.post_id WHERE w.target_id = $target_id";
    $result = someUserDefinedFunctionForQueryAndFetch($query);
    return $result
}

Upvotes: 2

RandomSeed
RandomSeed

Reputation: 29809

The layout you propose looks reasonable. 5 columns (four INT's and one TEXT), is not at all "bulky" a table.

If you have proper indexes, the query WHERE "Posted to this Users Wall" = "UserFoo" is virtually instant.

For your target query (display chronologically posts sent to the current user's wall), the best index is probably on (Posted to this Users Wall, Post Timestamp) (a two-columns index).

Upvotes: 2

Related Questions