SitePoint Sponsor

User Tag List

Results 1 to 13 of 13
  1. #1
    It's been real... Forbes's Avatar
    Join Date
    Dec 2004
    Location
    Yorkshire, England
    Posts
    676
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Adding a "scoring" option to a bookmarking system

    Hi guys!

    I've developed a bookmarking application, and it's become apparent that I need to deal with duplicate bookmarks in a sensible way.

    The long-term aim is to have a way of "scoring" bookmarks, so that when someone adds a bookmark that already exists, the score for the original is incremented.

    The reasons for doing this are, I no doubt, obvious.

    So far, the bookmarks table looks like this:
    Code:
    CREATE TABLE `bookmarks` (
      `id` int(11) NOT NULL auto_increment,
      `user_id` mediumint(11) NOT NULL,
      `url` text,
      `title` text,
      `snippet` text,
      `datetime` datetime default NULL,
      `status` enum('public','private') NOT NULL default 'public',
      PRIMARY KEY  (`id`),
      KEY `datetime` (`datetime`),
      FULLTEXT KEY `title` (`title`,`snippet`)
    );
    I have a few ideas in my mind how I might build a scoring system, but I'd much prefer to run this by you guys first, before committing to something that's either not practical or scales poorly over time.

    Any help would be much appreciated!

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    first thing you have to do is remove everything from that table that relates to which user(s) bookmarked a particular bookmark, and put that in a separate table
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    It's been real... Forbes's Avatar
    Join Date
    Dec 2004
    Location
    Yorkshire, England
    Posts
    676
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    first thing you have to do is remove everything from that table that relates to which user(s) bookmarked a particular bookmark, and put that in a separate table
    Hi, and thanks for the reply!

    That's one of the options I'm thinking of going with placing the URL itself into a separate table, along with the score and the ID of the originating bookmark.

    I was also thinking of chaining the bookmark URLs together, so that all other bookmarks would then fall into a parent-child relationship, along the lines of:
    Code:
    id, parent_id, child_id, url, score
    That way, when an originating parent bookmark is viewed, all of the child bookmarks can be retrieved.

    Is that a viable method, or are there better ways of doing this?

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    chaining? parent-child?

    on what basis are two urls related?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    It's been real... Forbes's Avatar
    Join Date
    Dec 2004
    Location
    Yorkshire, England
    Posts
    676
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    chaining? parent-child? On what basis are two urls related?
    As I said previously.

    Quote Originally Posted by Forbes View Post
    That way, when an originating parent bookmark is viewed, all of the child bookmarks can be retrieved.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    okay, but what does "originating" mean?

    i still don't see how two urls can be related

    i'm quite familiar with the parent-child structure, but i just don't see it applying here
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    It's been real... Forbes's Avatar
    Join Date
    Dec 2004
    Location
    Yorkshire, England
    Posts
    676
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    okay, but what does "originating" mean?

    i still don't see how two urls can be related

    i'm quite familiar with the parent-child structure, but i just don't see it applying here
    Someone will have added the first originating bookmark to which all duplicates are a child of.

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,247
    Mentioned
    59 Post(s)
    Tagged
    3 Thread(s)
    no, that's not a good idea

    duplicate the url each time? no
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    It's been real... Forbes's Avatar
    Join Date
    Dec 2004
    Location
    Yorkshire, England
    Posts
    676
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    no, that's not a good idea

    duplicate the url each time? no
    That's what I'm trying to figure out. But then there's the issue of determining exact matching URLs, which becomes a parameter cleansing issue.

    Rather than me guessing which is exactly what I'm doing right now I was hoping someone would actually explain the most appropriate way of doing this.

  10. #10
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Add a UNIQUE key on URL. Create a column called counter or something like that.

    See INSERT ON DUPLICATE UPDATE syntax in the manual.

    That way a duplicate is not inserted but your Counter column is updated instead.

  11. #11
    It's been real... Forbes's Avatar
    Join Date
    Dec 2004
    Location
    Yorkshire, England
    Posts
    676
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guelphdad View Post
    Add a UNIQUE key on URL. Create a column called counter or something like that.

    See INSERT ON DUPLICATE UPDATE syntax in the manual.

    That way a duplicate is not inserted but your Counter column is updated instead.
    Hi!

    I'm using INSERT ON DUPLICATE UPDATE elsewhere in the application, so I'm at least familiar with that. In fact, I'm doing almost exactly as you're recommending for the analytics side of things (user visits to bookmarks).

    Right, I can see how this would work now.

    I'm assuming I'm right to keep the parent-child method, so I can keep track of all those who added the same bookmark afterwards, yes?

    Thanks for your time! Much appreciated.

  12. #12
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No, you don't need a parent-child method at all, the link isn't a child of the same link, it is a duplicate of the link. You would use parent-child if you were drilling down:

    Electronics -> Televisions - Sanyo -> Model123

    If you want to know who added the bookmarks then you allow all inserts into the table with duplicate URLs and those who entered them. You would use a GROUP BY clause to group the URLs together and get a count of how many you had.

    You would be using three tables. The one you have above (bookmarks) with the user_id column removed entirely, a user table defining information about your users and a users_bookmarks table with two columns userid, url_id and a new row for each person who enters a bookmark. You may have 50 people with different userid(s) with the same url_id in the table.

  13. #13
    It's been real... Forbes's Avatar
    Join Date
    Dec 2004
    Location
    Yorkshire, England
    Posts
    676
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guelphdad View Post
    No, you don't need a parent-child method at all, the link isn't a child of the same link, it is a duplicate of the link. You would use parent-child if you were drilling down:

    Electronics -> Televisions - Sanyo -> Model123

    If you want to know who added the bookmarks then you allow all inserts into the table with duplicate URLs and those who entered them. You would use a GROUP BY clause to group the URLs together and get a count of how many you had.

    You would be using three tables. The one you have above (bookmarks) with the user_id column removed entirely, a user table defining information about your users and a users_bookmarks table with two columns userid, url_id and a new row for each person who enters a bookmark. You may have 50 people with different userid(s) with the same url_id in the table.
    This is actually the one option I thought there might be a way of avoiding.

    As I said, I'd thought through a few ideas, and this was the only one I could think of that would work fully. But I thought it was too complex and there was, perhaps, a better way.

    OK, if that's the way, so be it.

    And thanks for your time, here. Always appreciated.


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
  •