SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Jul 2009
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Twitter / Facebook style activity stream

    As the title implies, I'm looking to build an activity stream in to a website. However, I am not sure about the most efficient method to achieve it. My method could amount in hundreds of thousands or even millions of database rows, which is a level of data I have never had to work with before and would make backing up the database very tricky!

    I envisioned two database tables, one with "activity templates" with pre-defined areas for variables to be inserted i.e. "{0} has changed their profile template to {1}" (where 0 represents a username and 1 represents a template name). The second table would reference this activity and supply a comma-separated list of variables to be placed in to an array and inserted in to the sentence, like so "User, Apples".

    In the above example the output would be:
    "User has changed their profile template to Apples"

    I'm not sure that this is the best method to achieve this, but I can't think of any alternative at the moment, so I welcome your suggestions. I'm also eager to hear if anyone has any insight in to exactly how sites like Facebook and Twitter achieve this on such a monumental scale without serious performance issues.

  2. #2
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    how often is the data to be read then?
    how long do you need to keep it?
    could it be archived after say, a week?
    what kind of sorting would you guess you'd be doing if you applied the 80:20 rule?

  3. #3
    SitePoint Member
    Join Date
    Jul 2009
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Cups View Post
    how often is the data to be read then?
    I can't answer that with any degree of certainty. Several times a day by each user on average.
    Quote Originally Posted by Cups View Post
    how long do you need to keep it?
    I'd like to use the facebook/twitter model of keeping a record of user activity going back to the moment they joined.
    Quote Originally Posted by Cups View Post
    could it be archived after say, a week?
    Yes.
    Quote Originally Posted by Cups View Post
    what kind of sorting would you guess you'd be doing if you applied the 80:20 rule?
    I'm not sure what the question is here tbh.

  4. #4
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    I'd look at using sqlite to store the values then.

    I wouldnt bother with a second table ( not for inserting anyway ).
    Code:
     -- table--
     User | Entity     | Value  | daydate
    ==============================
    user1 | template | green | <timestamp> 
    user2 | textsize  | large  | <timestamp>
    Or maybe create a table for each user?

    The 80:20 rule I meant was to best-guess what you think the majority of sql calls will be - and design your data to be either got out, or put in as efficiently as you can.

    For example you might want to avoid lots of complex joins.

    TBH this all smacks of something hateful called "premature optimisation" which is just about the root of all programming evil, I hear.

    You should just build it and then optimise when problems occur.

    But still, if you know with some certainty the kind of reports you are going to be generating - say hourly or daily, then you can bear these things in mind as you design.

    For what you want it does sound like sqlite is what you need, you can shift them round the file system as files, ftp them etc.

    If so - you might find the PEAR::Log package does what you want (and if you want a PDO Sqlite driver for sqlite then head over to my site )


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •