I wonder if or not hibernate can solve the following problem( with advanced performance)
I want to implement an application as follows:
Modelling:
people are the entity.
relationship(friend,classmate...) are the links between people
distance (between two person): 1 direct friend, 2 friend's friend,3 friend's friend's friend, 4 ..., 5 others
use case:
the system can be used to search a bunch of persons by specifying the critera and ordered by the distance.


pure relational database is not applicable for this kind of impementation for the performance requirement.

if neither is hibernate, what database is applicable for this kind of implementation. OODB?

thx in advanced

since you think a relational database is not fast enough (why do you think so ?. do you have any numbers, tests, stats ?) :
Hibernate is an Object Relational Mapper (ORM), mapping Objects to a relational database. So if you believe relational database is to slow, then Hibernate is not for you :-)

pascal

I'd be more interested in why this doesn't work and why it should be slow? The friend relationships are in a separate index-only table in cache memory of the database and you can cache all of this probably with Hibernate too. I don't know what should be faster.

Its the usual problems with hierarchical models in relational world. Especially the fact that he wants to be able to specify "distance".

This might be doable with some trick like the one you were going to blog about ages ago Christian (I forget the name).

nested sets?

Right

the user could be 10000 or more
the relationship is store in table friend_tb(user_id,friend_id)
every person has 30 friends in average
this table singlely is very large

and the usecase seems to require at least 4 friend_tb table joins in order to determine the friend in distance 4 , (Is there any other solution for SQL for better performance?)

the search performance is very important for the system.

this application is a network model, is there any smart solution to that for relational db?


thanks for all the replys

nested set?
whatever, all the hibernate operations are translated into sql.
the 4 friend_tb tables join is also inevitable for the hibernate . right?

I never really test the search in real circumstance.
all the programmers in my team judge that the search will not be fast enough under such implementation.

Prototype the nested set model.

nested sets would not seem to apply here as the graph isn't representable as a DAG. If a relationship node is simply an identifier and list of adjacent nodes then the entire relationship network could easily be held in-memory, by Hibernate or whatever mechanism you'd like.

Someone correct me if I'm wrong, but this scenario with Hibernate would degenerate to 1 join per depth if the relationship table is being updated often causing the invalidation of the collection queries that get the adjacency lists, see http://hibernate.bluemars.net/213.html.

the searched results need to be display as pages
user can navigate to ramdon page
that is the search should get all results at the first time
or if not the search latter can be performed "in specific location"

1 join for every note deep down seems not reasonable

I read your initial posting again and I don't understand it. Please show the relations, attributes, and relationships in an example (ie. show the data model). Also show the result you'd like to achieve.

tables:
user (userid primarykey
, name
,email)

friends(userid foriegnkey
,friendid foriegnkey)

the relationship is bidirect that is if there is (1,2) in friends table there must be (2,1)


example:
rows in friends table:(only show unidirection)
1, 2 2, 3 4, 5 5,7 10,11
1, 3 2, 4 4, 6
1, 4 2, 5
..........................


for the user 1 1 degree away from 2,3,4
2 degree away from 5,6
3 degree away from 7
5 degree away from 10 11
user 1 perform the search for all users result:
2 3 4 5 6 7 10 11....
user 1 perform the search for the third degree users result: 7
when specify user 1 and 7 , get the distance
3 (shortest path1---->4------->5------->7)
when specify user 1 and 5 , get the referrals:
2 , 4 (1------>4----->5, 1--------->2------>5)

thanks!!!

sorry the above seems a little confusing....

tables:
user (userid primarykey
, name
,email)

friends(userid foriegnkey to user.userid
,friendid foriegnkey to user.userid)

the relationship is bidirect that is if there is (1,2) in friends table there must be (2,1)


example:
rows in friends table:(only show unidirection)
1, 2| 2, 3|4, 5 | 5,7 |10,11
1, 3| 2, 4|4, 6 |
1, 4| 2, 5|
..........................


for the user 1 1 degree away from 2,3,4
2 degree away from 5,6
3 degree away from 7
5 degree away from 10 11
user 1 perform the search for all users result:
2 3 4 5 6 7 10 11....
user 1 perform the search for the third degree users result: 7
when specify user 1 and 7 , get the distance
3 (shortest path1---->4------->5------->7)
when specify user 1 and 5 , get the referrals:
2 , 4 (1------>4----->5, 1--------->2------>5)

thanks!!!

correcting:
for the user 1, 1 degree away from 2,3,4


sorry for my poor editing.