Updating Screen Name or Thumnail Profile photo on all posts or comments

Question

Updating Screen Name or Thumnail Profile photo on all posts or comments

Fin_777

Grasshopper

Points: 18
More actions
May 4, 2008 at 11:38 pm

#190159

Hi All,
I am developing a social networking site which is based on SQL 2005 servers and ran into some trouble with my design.
When user logs on, or when he wants to visit another user's pages, the system uses data dependant routing to direct user to the appropriate server where the requested data is actually stored based on the username.
The DB design is such that, there is one lookup table that maps usernames to data servers. There are then several "Data" servers that hold all data belonging to particular subset of users.
The idea is that once member logs on, he will continue interacting only with their own server without the need to increase load the other servers.
Now that was the initial design and idea. We have now however added the ability for other users to add comments and display photo & screen name next to that comment.
So for example if User1 has all data on Server1 and then User2 (from Server2) leaves a comment, this comment will be stored in "Comments" table on Server1 (and currently the photo and screen name of User2 will be stored there too).
Now when User1 opens their comments page, he will be able to see comments from different people (and it's all being read from this Comments table)
This is all fine until the User2 changes their screen name or profile photo.
I would like to have the screen names & photos update immediatelly on all related posts across the system when it changes.
However don't seem to find the best solution.
I figure there are 3 options:
1) Keep the current design - and when the photo is changed, connect to all servers in the system and update the photo path on all comments on all servers. (don't like it much as this may cause trouble when many users keep changing their photos/screen names often)
2) Change the design so that the Photo/screen name is basically read from one location only (the owners Users Table).
Since this would be a live link, all comments and pages would read it from here. But if there was a comments page with comments from 50 users from 50 servers, I would have to make connection to 50 servers to display that one page ... not ideal either.
3) Have a special table holding screen name/photo of ALL users - and then have it replicated to all Data servers. The name/photo would be read from the "local" replica on that server. This however requires linked servers, replication .. seems to add a lot more complexity.
.....
So my question is, is there a better way?
How do big sites like Flickr, Facebook, Cyworld update the profile image on all posts when this image changes? It seems to happen instantly, and I am sure those guys have tonts of servers running their systems.
Any ideas?
Thanks

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

RBarryYoung SSC Guru Points: 143329 More actions · Answer 1

I would suggest having a separate table that just holds the profile information that needs to be available to all servers and the set that table up as a replicated multi-writer table with a replication partitioned ID as the primary key.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Fin_777 Grasshopper Points: 18 More actions · Answer 2

Thanks for your suggestion.

I tried to set up a separate db and table to hold the screenname & photo and then use peer-to-peer replication to make this db/table writable and available on all servers. However since I have only SQL server 2005 Standard - P2P replication is not supported.

So I resorted to using 'Transactionl Publication with Updatable Subscribers" type of replication. It seems to be working OK so far. The table is writable on all data servers (through Updatable Subscription), and then replicated to other servers. There is a slight delay which is acceptable.

We will see how this behaves with large number of users/servers.

Thanks again.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

Let us know how it works out.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]