CODEWITHSUNDEEP
databasehibernatejpaorm
JerryCai
JerryCai

Reputation: 1693

How to design a table which have many-to-many relationship with itself?

For example, A Users entity has a friends property, how can I design this friends property, in my thought there are 2 ways:

  1. friends property is a String with all usernames splitted by "," in this way it's hard to read and modify.
  2. friends property is a Set like Set<Users>, but in this way I don't know how to write in entities?

Anyone knows the best practise?

Upvotes: 2

Views: 1556

Answers (2)

siebz0r
siebz0r

Reputation: 20339

If a User can have multiple friends you could annotate your User entity like this:

@Entity
public class User
{
    @Id
    private Long id;
    private String name;
    @ManyToMany
    @JoinTable(
            name = "user_friends",
            joinColumns =
            { @JoinColumn(
                    name = "user_id") },
            inverseJoinColumns =
            { @JoinColumn(
                    name = "friend_id") })
    private Set<User> friends;
}

This way a table will get created for User and a join table for the relationship between Users. The User table will have 2 columns, 'id' and 'name'. The user_friend table will have 2 columns, 'user_id' and 'friend_id'. The columns in user_friend are both foreign keys to the User table.

Upvotes: 1

Neil McGuigan
Neil McGuigan

Reputation: 48256

This is covered Enterprise Model Patterns by Hay.

A party represents a person (or an organization):

Party
id
name

A party can have a relationship to another party, over a time period:

PartyRelationship
fromPartyId
toPartyId
fromDate
toDate nullable

A basic diagram:

Party -< PartyRelationship >- Party

Sample SQL:

insert into party values (1, 'Jerry');
insert into party values (2, 'Neil');

insert into partyRelationship values (1, 2, getDate(), null);

Upvotes: 2

Related Questions