Hello, I am developing a twitter-like service and am having a hard time to find the best solution for the following issue:
When a user logs in, he sees a list of all of the latest status updates from their friends.
The way the system is built now, there is a simple relation between the user and the status updates they post. This relationship is: a user has many messages (the same architecture is maintained in 2 different tables in the database: User and Messages).
If a user logs in, and this user has for example 50 friends, I want to avoid executing an SQL SELECT for each one of his 50 friends so that he can see a list of all of the latest status updates from his friends.
Does anyone know a better solution for this or have any idea how this feature is usually implemented, without jeopardizing performance?
Are you saying that SQL queries fired == Number of Friends a user has?
I think one query would be enough,if the DB is properly designed.
If I become filthy rich, I'll sponsor research for painless dental treatment at Harvard Medical School. Thats why,I'm learning Java.I have 32 teeth, 22 are man made.
Joined: Aug 11, 2005
My DB schemas are as follows (I am only showing the tables and columns relevant to this problem):
- userId (the mesage was written by this userId)
- message_contents (the actual contents of the message)
- userId (this userId has as a friend 'friendsWith')
- friendWith (this is also a userId from an existing user)
So, if I wanted to know what the latest status updates of a User's friends were, I would have to first fetch all of the User's friends, then for each one of those friends, fetch their latest message contents.
This to me seems like too many sql commands for a simple feature. I was wondering if there was a better strategy for similar issues, maybe modifying my DB schemas, or adding data redundancy, etc.
Joined: Feb 26, 2008
I think i will use joins in one query to fetch the data