problem in MYSQL database design....

[gurmeet]

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....

[Twey]

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.

[gurmeet]

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..

[boogyman]

-- &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 ...

[gurmeet]

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?

[Twey]

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.

[gurmeet]

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 ?

[Twey]

... no. Databases are quite powerful, and designed with efficiency over large datasets in mind. Why do you think it would cause problems?