Group Module Database Design

In this quick tutorial i will let you know how to create full flashed group functionality in PHP, here i am creating database structure for group functionality, its very common functionality and Each CMS normally provided group functionality but if you want your own customized then need this SQL script to design database.

groups_designer_view

The following target you will achieve using this database structure:
1- You can create group.
2- Created group can be public/Private type.
3- You can set slug of your group.
4- You can assign user into this group if this is private type group.
5- You can assigned User/Team into created group.
6- If you have assigned group then all member will get group activities.
7- You can upload logo for group as well.

I hope this tutorial will help you create to create group module for website.

Step 1: You need to create user table.

Select Code
1
2
3
4
5
6
7
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`));

Step 2: Now we will create group table.

Select Code
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
DROP TABLE IF EXISTS `groups`;
CREATE TABLE IF NOT EXISTS `groups` (
  `GID` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `slug` varchar(200) NOT NULL,
  `description` text NOT NULL,
  `owner_id` int(11) NOT NULL,
  `status` varchar(10) NOT NULL DEFAULT 'public',
  `logoPath` varchar(1000) NOT NULL,
  `updatedDate` datetime DEFAULT NULL,
  `updatedBy` varchar(255) DEFAULT NULL,
  `existing_record` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`GID`),
  FOREIGN KEY (`owner_id`) REFERENCES users(`owner_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

This table will contains all group information’s.

Step 3: Now we will create group recipients user/team .

Select Code
1
2
3
4
5
6
7
8
9
10
11
DROP TABLE IF EXISTS `group_recipients_to`;
CREATE TABLE IF NOT EXISTS `group_recipients_to` (
  `GRT` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `GID` int(11) NOT NULL,
  `recipient_id` varchar(20) NOT NULL DEFAULT '0',
  `type` varchar(200) NOT NULL,
  `type_id` int(11) NOT NULL DEFAULT '0',
  `existing_record` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`GRT`)
  FOREIGN KEY (`GID`) REFERENCES groups(`GID`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Above table will contains recipients information of user at the time of create group. If you have assigned team then it will store in type column ‘Team’ otherwise ‘User’, this table identify recipients is USER or TEAM.

Step 4: Now we will create group members. You are thinking about why need this table, that table identify recipients id and type at time of edit group functionality and this table identify user who are following and send activities information of that user.

You can remove this table if you want each time fetch team users data from database.

Select Code
1
2
3
4
5
6
7
8
9
10
11
12
13
DROP TABLE IF EXISTS `group_members`;
CREATE TABLE IF NOT EXISTS `group_members` (
  `GMID` int(11) NOT NULL AUTO_INCREMENT,
  `GID` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  `status` tinyint(4) NOT NULL,
  `updatedDate` datetime DEFAULT NULL,
  `updatedBy` varchar(255) DEFAULT NULL,
  `existing_record` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`GMID`),
  FOREIGN KEY (`user_id`) REFERENCES users(`user_id`),
  FOREIGN KEY (`GID`) REFERENCES groups(`GID`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

  • Bhumi

    Great post. Thanks