texomaleo (1) [Avatar] Offline
#1
Has anyone had any experience associating a class to itself in a many-to-many relationship in Hibernate? We have a need to associate users to each other as "friends" with no duplicate relationships. This should be equivalent to a graph of users. Each user has a set of friends. Our java code correctly manipulates the set for each friend. I.e., if B is added to A's set of friends, A is also added to B's set of friends without duplication.
shawn (2) [Avatar] Offline
#2
Re: Many-to-Many From a Class to Itself
Have you looked at Martin Fowler's book, "Analysis Patterns"? Specifically chapter 2. The whole pattern covers many different variations of this theme.

You'll need an intersect table. Unless this is a small project one usually discovers that more relationships are needed. The pattern Fowler uses actually ends up with 4 tables. The table of 'parties', i.e. users, clients, organizations. The table of 'accountabilities', i.e. relationships between parties, this is the intersect. Then a table of 'accountability types' is added, which covers all the different types of relationships that are possible. This table is an intersect of the 'party type' table which covers all possible types of parties.

With these 4 tables, and one interface that allows for editing relationships. We can knock out 6 or 7 different user interfaces in our in system with one code base, the only thing that changes is the 'accountability type' that's being edited. In fact it's so powerful in association, that we were requested not to use it's full power so that confidential data is kept compartmentalized.

However if it's a simple app 2 tables are all that is required. For example:

create table user
(
user_id varchar(10); // must be constrained unique
);

create table friend
(
user_id varchar(10); // Foreign key to above
friendly_with varchar(10); // Foreign Key to above
);

This should give you the basic idea. For the more general case see Fowler's book.

Shawn