Results 1 to 8 of 8

Thread: problem in MYSQL database design....

  1. #1
    Join Date
    Feb 2009
    Posts
    156
    Thanks
    0
    Thanked 4 Times in 3 Posts

    Default problem in MYSQL database design....

    i want to design a table in which i want to store the names of friends... for instance
    there are three users

    user 1 having 5 friends
    user 2 have 8 friends
    user 3 have 2 friends


    i just want to save the name of the user and the names of all friends in 1 or in more tables, so that i can retrieve all the friend's name by giving 1 query.... plz give me the suggestion how may i design table

    n there is no limit to make friends, so the counting may by cross 100 also....

  2. #2
    Join Date
    Jun 2005
    Location
    英国
    Posts
    11,878
    Thanks
    1
    Thanked 180 Times in 172 Posts
    Blog Entries
    2

    Default

    Code:
    CREATE TABLE users (
      id INT AUTO_INCREMENT,
      username VARCHAR(255) NOT NULL,
      -- &c.
    
      PRIMARY KEY(id),
      UNIQUE username
    );
    
    CREATE TABLE friendships (
      user1_id INT,
      user2_id INT,
    
      PRIMARY KEY(user1_id, user2_id)
    );
    Standard self-referencing many-to-many.
    Last edited by Twey; 02-06-2009 at 09:50 PM.
    Twey | I understand English | 日本語が分かります | mi jimpe fi le jbobau | mi esperanton komprenas | je comprends franšais | entiendo espa˝ol | t˘i Ýt hiểu tiếng Việt | ich verstehe ein bisschen Deutsch | beware XHTML | common coding mistakes | tutorials | various stuff | argh PHP!

  3. #3
    Join Date
    Feb 2009
    Posts
    156
    Thanks
    0
    Thanked 4 Times in 3 Posts

    Default

    plz give me full detail of this coding.... i am not very good in MYSQL... why do u used
    '-- &c.' this word.... n this code is not working when m applying it on my server...plz explain the code too so that i can mold it according to my own need.. thanks again..

  4. #4
    Join Date
    Jul 2006
    Location
    just north of Boston, MA
    Posts
    1,806
    Thanks
    13
    Thanked 72 Times in 72 Posts

    Default

    -- &c.
    is another way of stating etcetc...

    He is saying you may add in any other "personal" fields to the user information field, eg, Full name / Email / Telephone / Address ...

  5. #5
    Join Date
    Feb 2009
    Posts
    156
    Thanks
    0
    Thanked 4 Times in 3 Posts

    Default

    CREATE TABLE friendships (
    user1_id INT,
    user2_id INT,

    PRIMARY KEY(user1_id, user2_id),
    FOREIGN KEY(user1_id) REFERENCES users,
    FOREIGN KEY(user2_id) REFERENCES users
    );

    this code is also giving errors...
    i think it doesnot support the 2 foreign keys? isit?

  6. #6
    Join Date
    Jun 2005
    Location
    英国
    Posts
    11,878
    Thanks
    1
    Thanked 180 Times in 172 Posts
    Blog Entries
    2

    Default

    Could be (or rather, that it doesn't like the primary keys also being foreign keys). You could try it without the foreign-key constraints (as edited), but you must then take care of any invalid entries yourself.
    Twey | I understand English | 日本語が分かります | mi jimpe fi le jbobau | mi esperanton komprenas | je comprends franšais | entiendo espa˝ol | t˘i Ýt hiểu tiếng Việt | ich verstehe ein bisschen Deutsch | beware XHTML | common coding mistakes | tutorials | various stuff | argh PHP!

  7. #7
    Join Date
    Feb 2009
    Posts
    156
    Thanks
    0
    Thanked 4 Times in 3 Posts

    Default

    ok i ll... but this code will store the data of every user in this table only.... suppose i have 2000 users, ll it not give any problem ?

  8. #8
    Join Date
    Jun 2005
    Location
    英国
    Posts
    11,878
    Thanks
    1
    Thanked 180 Times in 172 Posts
    Blog Entries
    2

    Default

    ... no. Databases are quite powerful, and designed with efficiency over large datasets in mind. Why do you think it would cause problems?
    Twey | I understand English | 日本語が分かります | mi jimpe fi le jbobau | mi esperanton komprenas | je comprends franšais | entiendo espa˝ol | t˘i Ýt hiểu tiếng Việt | ich verstehe ein bisschen Deutsch | beware XHTML | common coding mistakes | tutorials | various stuff | argh PHP!

Bookmarks

Posting Permissions

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