Creating a Mysql database [closed]

It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For he开发者_高级运维lp clarifying this question so that it can be reopened, visit the help center. Closed 12 years ago.

I want to create a MySQL database which should contain all the users of my site, and also There should be an option of adding other users as friends. What should be the fields of database.

---

I cannot tell what your user table should cover. You need to know yourself. ;) But you need a second table, e.g. "friend", holding two fields: One for the actual user and one for a friend of this user. So, you have a m:n relation between the user table and itself.

---

SiteUser
    UserID
    -- anything else you want

UserFriend
    UserID_1
    UserID_2
    -- timestamp, status?

The bad thing about this is that the query to pull all Friends of a User would have to check both columns UserID_1 and UserID_2. As an alternative:

UserFriend
    UserID
    FriendID

This would have twice as many rows (two for each pair), but be much faster to query.

---

simple example - hope it helps...

-- TABLES

drop table if exists users;
create table users
(
user_id int unsigned not null auto_increment primary key,
username varbinary(32) unique not null
)
engine=innodb;

drop table if exists user_friends;
create table user_friends
(
user_id int unsigned not null,
friend_user_id int unsigned not null,
created_date datetime not null,
primary key (user_id, friend_user_id) -- note clustered composite PK (innodb only)
)
engine=innodb;

-- TRIGGERS

delimiter #

create trigger user_friends_before_ins_trig before insert on user_friends
for each row
begin
 set new.created_date = now();
end#

delimiter ;

-- STORED PROCEDURES

drop procedure if exists insert_user_friend;

delimiter #

create procedure insert_user_friend
(
in p_user_id int unsigned,
in p_friend_user_id int unsigned
)
proc_main:begin

  if p_user_id = p_friend_user_id then
    leave proc_main;
  end if;

  insert into user_friends (user_id, friend_user_id) values (p_user_id, p_friend_user_id);

end proc_main #

delimiter ;

drop procedure if exists list_user_friends;

delimiter #
create procedure list_user_friends
(
in p_user_id int unsigned
)
proc_main:begin

  select
    u.*,
    uf.created_date,
    date_format(uf.created_date, '%e-%b-%Y') as created_date_fmt
  from
    user_friends uf
  inner join users u on uf.friend_user_id = u.user_id
  where
    uf.user_id = p_user_id
  order by
   u.username;

end proc_main #

delimiter ;

-- TEST DATA (call these sproc from your php !)

insert into users (username) values ('f00'),('bar'),('alpha'),('delta'),('omega'),('theta');

call insert_user_friend(1,2);
call insert_user_friend(1,3);
call insert_user_friend(1,4);
call insert_user_friend(1,1); -- oops

call insert_user_friend(2,1);
call insert_user_friend(2,5);

call insert_user_friend(4,1);

call insert_user_friend(6,1);

call list_user_friends(1);

---

You only need one database, in this database you need two tables:

Users:
Username, id, otherstuffyouwanthere

Freinds:
UserID, FriendID

When you want to call up a users freinds list, simply find the user id and run this:

SELECT Users.Username FROM Friends JOIN Users ON Friends.FriendID=Users.id WHERE Friends.UserID="mycurrentid"

---

@Flinch: you can achieve the same effect with one table using The Adjacency List Model

but... this is the schema I use

CREATE TABLEmy_users(
idint(5) unsigned NOT NULL auto_increment,
login_namevarchar(50) character set utf8 NOT NULL,
passwordvarchar(255) character set utf8 NOT NULL default '',
full_namevarchar(255) character set utf8 NOT NULL,
user_department_codevarchar(255) character set utf8 default NULL,
emailvarchar(255) character set utf8 default NULL,
superiorvarchar(255) character set utf8 default NULL,
access_levelint(5) default '2',
rankvarchar(100) character set utf8 default '1',
account_statusvarchar(20) character set utf8 default NULL,
lftint(5) default NULL,
rgtint(5) default NULL,
PRIMARY KEY (id),
UNIQUE KEYlogin_name(login_name)
) ENGINE=MyISAM AUTO_INCREMENT=01 DEFAULT CHARSET=latin1

lft and rgt are for superior/subordinates calculation ( hierarchy etc ) you can see http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/ for further info about hierarichal data in mysql