CODEWITHSUNDEEP
phpmysql
George Pazdral
George Pazdral

Reputation: 90

adding users to different groups

I have a mySQL table entitled users. It has a UID, rating, username, password, etcetera.

My goal is to make a system (tribes) similar to Facebook's friends list. Each user will be able to view the profile of users and add them to their tribe. Each user is the chief of only one tribe, but can be the villager of as many tribes as he/she wants to be. The rating system would take into account all of the tribe's members ratings.

After doing some research on relational database tables and grouping, I am not clear about how I should go about setting up the tables, or the PHP code that would go along with that.

If someone can get me pointed in the right direction, it'd be much appreciated!

EDIT: One of the big problems I foresee is accepting to be in a tribe. I'm not sure how you account for this.

Upvotes: 2

Views: 116

Answers (2)

LSerni
LSerni

Reputation: 57388

similar to Facebook's friends list. Each user will be able to view the profile of users and add them to their tribe. Each user is the chief of only one tribe

Okay, so you have a User table, and also will need a Tribe table.

Then the relations you're describing are a chief-of, which is one-to-one (one user can be chief of one tribe; one tribe has only one chief), therefore you can either store this within User (chief_of: Tribe) or within Tribe (chief: User).

CREATE TABLE User ...
    chief_of integer

Here, chief_of might be a foreign key so that if you delete a tribe, the relevant tuple will have its chief_of set to NULL (a user can't be chief of a no longer existing tribe).

The membership is a bit more complicated because one user can belong to several tribes, and a tribe will have more than one member.

This is a many-to-many relationship and is usually done with a table holding key pairs:

CREATE TABLE member_of (
    user_id integer,
    tribe_id integer
);

Both fields are natural candidates for foreign keys. Here you can find a similar implementation using Authors and Books.

To indicate that Bob is a member of the Clan of the Cave Bear, you retrieve the ids of Bob and Bears, and insert a tuple in member_of.

To retrieve all members of the clan, you can use a JOIN:

SELECT Users.* FROM Users
    JOIN member_of ON (Users.user_id = member_of.user_id)
    WHERE member_of.tribe_id =
        (SELECT tribe_id FROM Tribes WHERE tribe_name = 'Clan of the Cave Bear');

I think that a shorter version of that ON in MySQL is USING(user_id) (meaning that both tables have an identical column identically named), but in my opinion the ON is clearer.

You can also retrieve a virtual "is_chief" column:

SELECT Users.*, chief_of = tribe_id AS is_chief FROM Users
    JOIN member_of ON (Users.user_id = member_of.user_id)
    WHERE member_of.tribe_id =
        (SELECT tribe_id FROM Tribes WHERE tribe_name = 'Clan of the Cave Bear');

The one user whose chief_of attribute is equal to the tribe id will have is_chief set to TRUE, which is equal to 1, so 

SELECT Users.*, chief_of = tribe_id AS is_chief FROM Users
    JOIN member_of ON (Users.user_id = member_of.user_id)
    WHERE member_of.tribe_id =
        (SELECT tribe_id FROM Tribes WHERE tribe_name = 'Clan of the Cave Bear')
    ORDER BY (chief_of = tribe_id) DESC, user_name;

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

As for the acceptance into the tribe, this identifies three states: a user is not in a tribe, a user is in the tribe, a user asked to be in a tribe. The first two are actually two faces of the same attribute member_of. So naturally we might create a new attribute and call it wants_in. It would map to a table identical to member_of.

A chief could retrieve all tuples of wants_in whose tribe_id is equal to his own chief_of (so if it's NULL, meaning he's not a chief, this will automatically return nothing). Then he might see this as a table of checkboxes with user names. When he approves the join, for each approval you delete the tuple from wants_in and put it into member_of.

Or you might decide that "membership" is a state in itself, so that you have a more complex join table

user_id,
tribe_id,
status

where status could be, say,

  • nothing (there's no (U, T, ?) tuple): user U and tribe T are unknown to each other
  • 100: user U is full member of tribe T
  • -1 : tribe T has decided that U is not a member and cannot even ask to be.
  • 0: user U wants to be member of T
  • 1-99: user U is a probationary (apprentice) member.

Upvotes: 2

Ben
Ben

Reputation: 57217

It sounds like you'll need two classes: Villager and Tribe.

Then, maybe a database field called chief_of or something that is null if the person is not a chief, and contains the name of their tribe that they are the chief.

In the classes you can have a getChiefOf() method that can be tested to see if the user is a chief or not.

Alternatively, you could have a table of chiefs, indexed by UID, with the same column that says which tribe they're chief of. A little less efficient but a better structure. A drawback that jumps to mind is if the UID for some reason is changed, two tables would have to be updated, so maybe the first one's better.

Upvotes: 2

Related Questions