Groups or Pages Database Design.
Wall Script
Follow Me:
Wednesday, October 08, 2014

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.

Friend Request System Database Design.


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`));

Friend Request System Database Design.


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 Database Design.


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));

Groups Database Design.


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)
);

Friend Request System Database Design.


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
Was this article helpful?
Thanks! Your feedback helps us to improve 9lessons.info


20 comments:

  1. Hi 9lessons team, I follow and read your articles in regular basis,can you please write a simple tutorial on object relation mapping in php,for example doctrine orm .Thank you.

    ReplyDelete
    Replies
    1. YOu have the diagrams so you can build your doctrine schema based on the diagram design.

      Delete
  2. Great Post. I am looking for user access level tutorial.

    ReplyDelete
  3. Thanks for this page presenting database "good" ideas.
    PLEASE explain, justify and/or describe!
    A scheme and code isn't teaching !
    I want to learn more, but I have no idea why your way is better...

    ReplyDelete
  4. hey how to loads in combined for i mean from extracting updates together from pages , groups and users

    ReplyDelete
  5. Srinivas, I'm not sure or not clear what "Groups or Pages" is, where will I use this or it's purpose. Could you clarify please?

    ReplyDelete
  6. sir can u tell me about some PHP and MYSQL Book ... i am just beginner

    ReplyDelete
  7. its very helping ..nice post Sri.. thumbsUp from Mirchu Team

    ReplyDelete
  8. I have always been a bit confused in joint queries, seems to have solved the problem. :)

    ReplyDelete
  9. Structure maybe is good, but how about a code? How we have to use the code and when. Thats a big minus from your side...

    ReplyDelete
    Replies
    1. Here I have specified SQL queries, just display the data with your favorite language.

      Delete

Make in India