1 Reply Latest reply: Mar 5, 2013 3:28 PM by sb92075 RSS

    Create a User Friend Relationship Table

      am trying to create a relationship table in sql but the challenge I am facing is

      If there are 3 users, u1, u2, u3 in user_table

      create table user_tab
      (user_id varchar2(10) not null,
      user_name varchar2(20),
      user_friend_id varchar2(10),
      constraint u_pk primary key (user_id),
      constraint fk_user_snap
      foreign key (u_s_id)
      references pic_table(user_id));

      insert into user_tab values(U1,user1)
      insert into user_tab values(U2,user2,U1)
      insert into user_tab values(U3,user3,U1)

      u1 adds u2, u3 in his friends list, then u2 and u3 will have a friend_id as u1

      Select * from user_tab;

      User_id User_Name User_Friend_id
      U1 User1
      U2 User2 U1
      U3 User3 U1

      But if U2 adds U1 and U3 as friends, then how will the friend_id column in the row u3 hold both u1 and u2 as foreign keys? What I am trying to achieve is if U1 adds U2 as friend then the relationship can be saved by adding another column and storing U1 as friend_id, but if U2 does not add U1 as a friend then when U2 friends list is generated it should store U3, U4, so on, user_ids which U2 added

      How can I address this issue? Do I create a separate table to hold relationships?