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.
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?
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.