Win a copy of Learn Spring Security (video course) this week in the Spring forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Retrieving data from database - Architecture issue

 
Henrique Boreg
Greenhorn
Posts: 23
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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?

Thanks.
 
Bear Bibeault
Author and ninkuma
Marshal
Pie
Posts: 64613
86
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Please take the time to choose the correct forum for your posts. This forum is for questions on Advanced Java.

For more information, please read this.

This post has been moved to a more appropriate forum.

 
Jhakda Velu
Ranch Hand
Posts: 167
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.

Jhakda
 
Henrique Boreg
Greenhorn
Posts: 23
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
My DB schemas are as follows (I am only showing the tables and columns relevant to this problem):

Table: User
- userId

Table: Message
- userId (the mesage was written by this userId)
- message_contents (the actual contents of the message)

Table: FriendsWith
- 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.

Thanks.
 
Jhakda Velu
Ranch Hand
Posts: 167
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I think i will use joins in one query to fetch the data

Jhakda
 
John Kimball
Ranch Hand
Posts: 96
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You mean something like this?



You can do it without the UNION if you really want to, but I think this is far clearer.

Edit: Sorry, I used Sybase syntax without thinking
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic