Groups or Pages Database Design.

Last few days I have been working with an important release from 9lessons.info, I am introducing many exciting features hope everybody will love. This post is about how to implement Group of Fan Page system using MySQL and PHP. Here I have discussed how to design a database and implementation of SQL queries, hope this post will help you to understand the Group/Page system.

Database Design
To build the group updates system, you have to create three tables such as Users, Updates, Groups and Group_Users.

Users Table

User table contains all the users registration details.

CREATE TABLE `users` (
`user_id` INT(11) NOT NULL AUTO_INCREMENT ,
`username` VARCHAR(45) ,
`password` VARCHAR(100) ,
`email` VARCHAR(45) ,
`status` ENUM('0','1') ,
PRIMARY KEY (`user_id`));

Updates Table

This table contains user status updates data. Here user_id_fk is the FOREIGN KEY to REFERENCES users.user_id

CREATE TABLE `updates` (
`update_id` INT(11) AUTO_INCREMENT ,
`update` VARCHAR(45),
`user_id_fk` VARCHAR(45),
`created` INT(11) ,
`group_id_fk` INT(11) DEFAULT '0' ,
`ip` VARCHAR(45),
PRIMARY KEY (`update_id`),
FOREIGN KEY (user_id_fk) REFERENCES users(user_id),
FOREIGN KEY (group_id_fk) REFERENCES groups(group_id)
);

Groups Table

This table contains groups data. Here user_id_fk (group owner) is the FOREIGN KEY to REFERENCES users.user_id

CREATE TABLE `groups` (
`group_id` INT(11) AUTO_INCREMENT ,
`group_name` VARCHAR(100),
`group_desc` VARCHAR(200),
`user_id_fk` INT(11) ,
`created` INT(11) ,
`status` ENUM('0','1') DEFAULT '1',
PRIMARY KEY (`group_id`),
FOREIGN KEY (user_id_fk) REFERENCES users(user_id));

Group Users Table

This table contains user status updates data. Here user_id_fk is the FOREIGN KEY to REFERENCES users.user_id

CREATE TABLE `group_users` (
`group_user_id` INT(11) AUTO_INCREMENT ,
`group_id_fk` INT(11),
`user_id_fk` INT(11),
`status` ENUM('0','1') DEFAULT '1',
PRIMARY KEY (`user_id_fk`,`group_id_fk`),
FOREIGN KEY (user_id_fk) REFERENCES users(user_id),
FOREIGN KEY (group_id_fk) REFERENCES groups(group_id)
);

Create a Group or Fan Page
This statement is required for group/page registration, here user_id_fk is group/page owner.

INSERT INTO groups
(group_name,group_desc,user_id_fk)
VALUES
('Jquery Page','The Write Less, Do More','1');

Group/Page Members
This statement is required for group/page registration, here user_id_fk is group/page owner.

INSERT INTO group_users
(group_id_fk,user_id_fk)
VALUES
('1','1');

Group User Updates
This statement is required for group/page registration, here user_id_fk is group/page owner.

INSERT INTO updates
(update,user_id_fk,group_id_fk)
VALUES
('The Write Less, Do More, JavaScript Library','1','1');

Group Members List
Data relations between users and group_users tables for displaying group memebers. Take a look at the following SQL statement users table object as U and group_users table object as F. Here user_id is the login user session value and $group_id is group row number.

SELECT U.username, U.user_id
FROM
users U, group_users G
WHERE
U.status='1'
AND
U.user_id=G.user_id_fk
AND
G.group_id_fk='$group_id' ORDER BY G.group_user_id DESC

Group Updates
Data relations between users,updates and group_users tables for displaying following group updates . Take a look at the following SQL statement users table object as U, updates tables object as M and friends table object as F . Here user_id is the login user session value.

SELECT DISTINCT
M.update_id, M.user_id_fk, M.group_id_fk, M.updates
FROM
updates M, users U, group_users G
WHERE
G.user_id_fk='$user_id'
AND
U.status='1'
AND
M.user_id_fk=G.user_id_fk
AND
M.group_id_fk = G.group_id_fk ORDER BY M.update_id