php - adding users to different groups -

i have mysql table entitled users. has uid, rating, username, password, etcetera.

my goal make system (tribes) similar facebook's friends list. each user able view profile of users , add them tribe. each user chief of 1 tribe, can villager of many tribes he/she wants be. rating system take account of tribe's members ratings.

after doing research on relational database tables , grouping, not clear how should go setting tables, or php code go along that.

if can me pointed in right direction, it'd appreciated!

edit: 1 of big problems foresee accepting in tribe. i'm not sure how account this.

similar facebook's friends list. each user able view profile of users , add them tribe. each user chief of 1 tribe

okay, have user table, , need tribe table.

then relations you're describing chief-of, one-to-one (one user can chief of 1 tribe; 1 tribe has 1 chief), therefore can either store within user (chief_of: tribe) or within tribe (chief: user).

create table user ...     chief_of integer 

here, chief_of might foreign key if delete tribe, relevant tuple have chief_of set null (a user can't chief of no longer existing tribe).

the membership bit more complicated because 1 user can belong several tribes, , tribe have more 1 member.

this many-to-many relationship , done table holding key pairs:

create table member_of (     user_id integer,     tribe_id integer ); 

both fields natural candidates foreign keys. here can find similar implementation using authors , books.

to indicate bob member of clan of cave bear, retrieve ids of bob , bears, , insert tuple in member_of.

to retrieve members of clan, can use join:

select users.* users     join member_of on (users.user_id = member_of.user_id)     member_of.tribe_id =         (select tribe_id tribes tribe_name = 'clan of cave bear'); 

i think shorter version of on in mysql using(user_id) (meaning both tables have identical column identically named), in opinion on clearer.

you can retrieve virtual "is_chief" column:

select users.*, chief_of = tribe_id is_chief users     join member_of on (users.user_id = member_of.user_id)     member_of.tribe_id =         (select tribe_id tribes tribe_name = 'clan of cave bear'); 

the 1 user chief_of attribute equal tribe id have is_chief set true, equal 1,

select users.*, chief_of = tribe_id is_chief users     join member_of on (users.user_id = member_of.user_id)     member_of.tribe_id =         (select tribe_id tribes tribe_name = 'clan of cave bear')     order (chief_of = tribe_id) desc, user_name; 

will retrieve users in alphabetical order, except chief, who, if present, come first.

as acceptance tribe, identifies 3 states: user not in tribe, user in tribe, user asked be in tribe. first 2 2 faces of same attribute member_of. naturally might create new attribute , call wants_in. map table identical member_of.

a chief retrieve tuples of wants_in tribe_id equal own chief_of (so if it's null, meaning he's not chief, automatically return nothing). might see table of checkboxes user names. when approves join, each approval delete tuple wants_in , put member_of.

or might decide "membership" state in itself, have more complex join table

user_id, tribe_id, status 

where status be, say,

• nothing (there's no (u, t, ?) tuple): user u , tribe t unknown each other
• 100: user u full member of tribe t
• -1 : tribe t has decided u not member and cannot ask be.
• 0: user u wants member of t
• 1-99: user u probationary (apprentice) member.