User and friends database structure

[LeXave]

Hi all

I've got the following problem in my database structure : I have a
table "User". Each user can have one or more friends which are users
too. And I have no idea about the way I must think my structure. My
first idea was to create a second table "User2" which would be an
exact copy of the table "User", but I can't because of the fact that
an user can have several friends...

The best way would be to store friends in a collection, and store this
collection in a field of the table "User". But I have no idea about
the way to do that (if it's possible).

So, do you have another solution ?

Thanks in advance

[Allen Browne]

Put all the users (including friends) into one table.

The way to link them depends on the kinds of relationships that exist
between them. If they are directional relationships (User 1 has user 2 as a
friend, but that does not imply that user 2 has user 1 as a friend), the 2nd
table would have these fields:
UserID Number relates to the primary key of tblUser
FriendID Number relates to the primary key of tblUser

The combination of the 2 fields together would be primary key in this 2nd
table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"LeXave" <fr*********@gmail.comwrote in message
news:ed**********************************@m34g2000 hsc.googlegroups.com...

>
I've got the following problem in my database structure : I have a
table "User". Each user can have one or more friends which are users
too. And I have no idea about the way I must think my structure. My
first idea was to create a second table "User2" which would be an
exact copy of the table "User", but I can't because of the fact that
an user can have several friends...

The best way would be to store friends in a collection, and store this
collection in a field of the table "User". But I have no idea about
the way to do that (if it's possible).

So, do you have another solution ?

Thanks in advance

[Roger]

On Mar 12, 2:27*am, LeXave <[e-mail]:

Hi all

I've got the following problem in my database structure : I have a
table "User". Each user can have one or more friends which are users
too. And I have no idea about the way I must think my structure. My
first idea was to create a second table "User2" which would be an
exact copy of the table "User", but I can't because of the fact that
an user can have several friends...

The best way would be to store friends in a collection, and store this
collection in a field of the table "User". But I have no idea about
the way to do that (if it's possible).

So, do you have another solution ?

Thanks in advance

tblUser
userId (pk)
name
address
etc

tblFriend
userId (fk to tblUser.userId)
friendId (fk to tblUser.userId)