making a simple discusion forum part 1 (the database)

**omar_a_k** · 01-12-2009, 02:11 PM

hi every body
we will start with making the forum database
it will have the folowing tables :
1.sections: (for storing the sections of the discussion forum)

Code:

CREATE TABLE  `forum`.`sections` (
  `depID` int(10) NOT NULL AUTO_INCREMENT,
  `depdescription` varchar(255) DEFAULT NULL,
  `depname` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`depID`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

2.topics: for storing the related topics to as specific section

Code:

 CREATE TABLE  `forum`.`topic` (
  `topicID` int(10) NOT NULL AUTO_INCREMENT,
  `topicNAME` varchar(255) DEFAULT NULL,
  `topicDescription` varchar(255) DEFAULT NULL,
  `depid` int(10) DEFAULT NULL,
  `userid` int(10) DEFAULT NULL,
  PRIMARY KEY (`topicID`),
  KEY `depid` (`depid`),
  KEY `userid` (`userid`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

3.posts :for storing the related posts to as specific topics

Code:

CREATE TABLE  `forum`.`posts` (
  `postid` int(10) NOT NULL AUTO_INCREMENT,
  `topic_id` int(10) DEFAULT NULL,
  `post_content` text,
  `user_id` int(10) DEFAULT NULL,
  `notes` varchar(255) DEFAULT NULL,
  `post_html` varchar(255) DEFAULT NULL,
  `post_title` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`postid`),
  KEY `topic_id` (`topic_id`),
  KEY `topic_id1` (`notes`),
  KEY `user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=37 DEFAULT CHARSET=latin1;

4.replies:for storing the replies for a specific post

Code:

CREATE TABLE  `forum`.`replies` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `post_id` int(10) DEFAULT NULL,
  `reply` varchar(255) DEFAULT NULL,
  `user_id` int(10) DEFAULT NULL,
  `reply_html` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `userid` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

5.user_roles: indicating if the user is admin or viewer or poster

Code:

CREATE TABLE  `forum`.`user_roles` (
  `ROLE_ID` int(10) NOT NULL AUTO_INCREMENT,
  `ROLE_type` varchar(12) DEFAULT NULL,
  `ROLE_priveleges` varchar(50) DEFAULT NULL,
  `notes` longtext,
  PRIMARY KEY (`ROLE_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

6.users: stroing user info

Code:

CREATE TABLE  `forum`.`users` (
  `user_id` int(10) NOT NULL AUTO_INCREMENT,
  `user_name` varchar(20) DEFAULT NULL,
  `password` varchar(50) DEFAULT NULL,
  `firstname` varchar(50) DEFAULT NULL,
  `lastname` varchar(50) DEFAULT NULL,
  `bad` tinyint(1) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `role_id` int(10) DEFAULT NULL,
  PRIMARY KEY (`user_id`),
  UNIQUE KEY `user_name` (`user_name`),
  UNIQUE KEY `password` (`password`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=latin1;

7.friends :friends (users) for specific user:

Code:

CREATE TABLE  `forum`.`friends` (
  `userid` int(10) unsigned DEFAULT NULL,
  `frieinid` varchar(45) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

8.images: storing any images used by the forum

Code:

CREATE TABLE  `forum`.`images` (
  `image_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `image_path` varchar(200) DEFAULT NULL,
  `image_url` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`image_id`),
  UNIQUE KEY `image_path` (`image_path`),
  UNIQUE KEY `image_url` (`image_url`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=latin1;

9.user_images :for storing user images when registering

Code:

CREATE TABLE  `forum`.`user_images` (
  `image_id` int(10) unsigned DEFAULT NULL,
  `userid` int(10) unsigned DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

and the following stored procedure (register) for using in the register form :

Code:

DELIMITER $$

DROP PROCEDURE IF EXISTS `forum`.`register`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE  `forum`.`regmee2`(img2 varchar(200), usrnme varchar(20), psswrd varchar(50), frstnme varchar(50),lstnme varchar(50),email varchar(255))
BEGIN


insert into IMAGES (image_url) values (img2);
insert into dbo_users (user_name,password,firstname,lastname,email) values
(usrnme,psswrd,frstnme,lstnme,email);
insert into user_images (userid ,image_id ) select u.user_id ,i.image_id from dbo_users u ,images i where user_id=(select user_id from dbo_users where user_name=usrnme ) and image_id=(select image_id from images where image_url=img2);
END $$

DELIMITER ;

soon i will post the php code for implementing the discussuion forum.