aspose file tools*
The moose likes Java in General and the fly likes Retrieving data from database - Architecture issue Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » Java in General
Bookmark "Retrieving data from database - Architecture issue" Watch "Retrieving data from database - Architecture issue" New topic
Author

Retrieving data from database - Architecture issue

Henrique Boreg
Greenhorn

Joined: Aug 11, 2005
Posts: 23
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

Joined: Jan 10, 2002
Posts: 61222
    
  66

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.


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
Jhakda Velu
Ranch Hand

Joined: Feb 26, 2008
Posts: 166
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


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.
Henrique Boreg
Greenhorn

Joined: Aug 11, 2005
Posts: 23
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

Joined: Feb 26, 2008
Posts: 166
I think i will use joins in one query to fetch the data

Jhakda
John Kimball
Ranch Hand

Joined: Apr 13, 2009
Posts: 96
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
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Retrieving data from database - Architecture issue