CODEWITHSUNDEEP
phpmysqlsql
user1011713
user1011713

Reputation: 279

Proper table structure for creating groups and inviting users

I have the following tables

users: uid, name_f, name_l...

group_data: id, group_id, admin, invitedusers, subscribers...

When user, 'uid:4' for example creates a group and invites his friends 'uid:7' and uid:9' I insert into the group_data table: 

'admin:4, invitedusers:4,7,9, subscribers:4,7,9'. I

If for example uid:9 doesn't want to receive notifications, I simply remove him/her from the comma-delimited list, subscribers to make it 'subscribers:4,7' I also do the same when an admin removes a user from the group except I alter the invitedusers column.

I understand that this setup is extremely flawed and that I have to create a new table for invitedusers and subscribers. I'm just looking for a way to create that table and also an sql query that selects all users in the new system.

Any help is wonderfully appreciated. Also, this answer somewhat touched on the subject but I don't quite understand the structure reference and couldn't implement it: SQL Array Search

Upvotes: 2

Views: 1321

Answers (2)

Travesty3
Travesty3

Reputation: 14469

You should have one table with one row for each user that exists, like you already do. Call it 'users'. This table will have only the information about the user, not which groups they are in.

You should have another table with one row for each group, which you do. Call it 'groups'. This table will have only the information about the group, not who is in it.

You should have a third table with one row for each user for each group they're in. Call it 'user_groups' or something. Each row in this table will link one user from the 'users' table to one group from the 'groups' table, and possibly give information about their subscription to that group.

A sample setup may look like:

CREATE TABLE users (
    userID INT,
    firstName VARCHAR,
    lastName VARCHAR,
    PRIMARY KEY (userID)
);

CREATE TABLE groups (
    groupID INT,
    groupName VARCHAR,
    adminUserID INT,    /* this is the userID of the one and only administrator of this group */
    PRIMARY KEY (groupID)
);

CREATE TABLE user_groups (
    userID INT,
    groupID INT,
    invited TINYINT,    /* 0 or 1 to indicate true or false */
    subscribed TINYINT  /* 0 or 1 to indicate true or false */
);

EDIT Answering question from comments:

$groupID = 1;
$userIDArray = explode(",", $row["invitedusers"]);    // assuming that $row is a row from the old group_data table
foreach ($userIDArray as $userID)
    mysql_query("INSERT INTO user_groups (userID, groupID, invited) VALUES ('". mysql_real_escape_string($userID) ."', '". mysql_real_escape_string($groupID) ."', 1)");

Upvotes: 4

Wrikken
Wrikken

Reputation: 70460

Terrible performance, but good enough for a once-off:

 -- create table
 CREATE TABLE invitedusers (group_data_id integer,user_id integer, PRIMARY KEY(group_data_id,user_id));
 --  insert existing users
 INSERT IGNORE INTO invitedusers (group_data_id,user_id)
 SELECT g.id, u.id
 FROM users u
 JOIN group_data g
 ON FIND_IN_SET(u.id,g.invitedusers) > 0

Upvotes: 0

Related Questions