Results 1 to 9 of 9

Thread: Database Design

  1. #1
    Join Date
    May 2009
    Posts
    8

    Question Unanswered: Database Design

    I am trying to design two simple tables - user and friends
    a user can have multiple friends.

    user

    id,
    name,
    password,
    address,
    email

    I am confused how can I maintain multiple relations with friends table.
    Can someone help me/ explain me how to achieve good design?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I am trying to design two simple tables - user and friends
    What are the attributes for friends table?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    May 2009
    Posts
    8
    id ,
    name,
    address,
    email.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    attributes for FRIENDS look very similar to USERS table; so why is separate FRIENDS table needed.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    May 2009
    Posts
    8
    A user can have multiple friends.. for example .. consider the site like facebook.. every user has multiple friends..

  6. #6
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by ron87 View Post
    A user can have multiple friends.. for example .. consider the site like facebook.. every user has multiple friends..
    .. who are also users at the same time.
    If this is your case, you need two tables - USER (columns ID, NAME, PASSWORD, ADDRESS, EMAIL, ...) and FRIEND (columns ID, FRIEND_ID - both foreign keys to USER table, preferably with primary key on both columns)

  7. #7
    Join Date
    May 2009
    Posts
    8
    Exactly.. I have the same concept but what is the use of that columns_ID ?
    And how can I manage the relations in those two table..say I want know how many friends an user has?

  8. #8
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by ron87 View Post
    Exactly.. I have the same concept but what is the use of that columns_ID ?
    In FRIENDS table? It defines the relationship - user identified with ID has a friend identified with FRIEND_ID.
    Quote Originally Posted by ron87 View Post
    And how can I manage the relations in those two table..say I want know how many friends an user has?
    Just use SQL commands INSERT/UPDATE/DELETE/SELECT - e.g. for your request use
    Code:
    SELECT COUNT(DISTINCT friend_id)
    -- better COUNT(*), but it requires (ID, FRIEND_ID) to be unique
    FROM friends
    WHERE id = <id_of_given_user>

  9. #9
    Join Date
    May 2009
    Posts
    8
    okk.. thanks for ur help

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •