B4J Question Which would be the most appropiate Database structure?

[JordiCP]

This question isn't related exactly to coding and also could be in the B4A forum, but since the doubt has arised in a B4J program that I am writing, here it goes.


This program is a server that communicates through UDPSockets with several devices running an app made with B4A.

The server has to be able to add, delete users, and also manage a list of "accepted friends" for each user

Up to now, and for debugging the rest of features, I had a file with some predefined users and all of them were allowed to comunicate with the others (so I didn't have to worry about this part)

But now I would like to use a database for a real approach. I'm quite new into this part

Which would be the most appropiate structure for a DB in which what I want is this:
a) Have a list of users (which allows me to insert, delete, modify...)
b) Each user has a number of friends. This number is different for each user (and also must allow to add, delete friends...)

I think structure of a table for a) is quite easy (user fields are finite and well defined: unique ID, mail address and nick) . But don't know which would be better for b) since for each user there may be different "friends" so the number of fields for each register (user) is not known

Even if the question may be obvious (sure) , a hint other than keep on reading manuals will be welcome.



Thanks!

 

[LucaMs]

[User]       [Friend]
ID           ID
Name         UserID
...          Name
...          ...

Join on User.ID - Friend.UserID

 

[JordiCP]

Thanks LucaMS!

Let me see if I understood. You mean creating a table in which each register is a "friendship" and then retrieve a users's list of friends by finding all such registers in which one of the fields is the user Id?


Sounds good

 

[LucaMs]

Query = "SELECT User.ID, User.Name, User.LastName, Friend.Name AS [Friend Of] FROM User INNER JOIN Friend ON User.ID = Friend.UserID"

 

[MaFu]

But what if two ore more user shares the same friends?
I would use three tables: User, Friend and Lookup

[User]    [Friend]    [Lookup]
ID        ID          UserID
Name      Name        FriendID
...       ...

The lookup table contains all user-friend relationships.

 

[LucaMs]

You're right, I've had too hastily (as usual) and too much sleep

 

[JordiCP]

I think I get the idea. Thanks to both!


But don't understant why 3 tables. [User] and [Friend] have the same structure, since each user is at the same time a friend of others (or not, poor man!)

Wouldn't it be enough having only a [User] and a [Lookup] (friendships) table? And each time we can get the list of friends of a given user just by selecting from [Lookup] all the registers in which UserID or FriendID are equal to ID?

 

[MaFu]

Thats right, in this case you only need two tables.