Database structure that would allow my users to add a buddy list?

mlsbraves

I just need advise on how to set this up, not really the coding. When someone joins my site, the script will take the info and insert it into a mysql database. So far my database just concist of 1 table with 4 fields(id, username, pass, email). But I want to allow the user to add users to their profile. Since they can have numerous entries I just can't add another field to the user table. So I assume I need to make a new table(eg. buddy) and for each id they can add as many users as they want. Basiclly if I can get the structure right I can figure out how to write the code.

Maybe something like:
Database
-table users(id, username, pass, email)
-table buddy(addnumberid, userid, buddyname)

Saying when the user adds a buddy, it creates a new id#, userid would be the same as the userid in table users, and buddyname would be the text of the new buddy.

Would something like this work or am i off?

devinemke

a classic many-to-many data relationship. you're on the right track. create a second table called buddy_link:

buddy_link_id (INT) PK
user_id (INT)
buddy_user_id (INT)

user_id relates to the user adding the buddy and buddy_user_id relates to the user that is their buddy

mlsbraves

Oh I should have mentioned. The user there adding to there buddylist probably won't be in my database. As there adding users from other sites. So linking them would'nt work.

cheese

I've done something similar on a site I developed for someone else.

After suggestions from Roger Ramjet, all I ended up doing was almost exactly what you already have.

INDEX the userid in one table for quicker access and then (depending on how you are actually doing things you have a query something like;


SELECT * FROM tablebuddy WHERE userid = $something

$something being a variable that is passed to idientify the user.

Hope this helps,

Neil.