Page 1 of 1

MYsql + creating Blog + problem in primay key. foriegn key

Posted: Wed Jul 13, 2011 10:58 pm
by menukadevinda
Hi all,

i have post this problem in database forum and php forum i stil didn't get single reply.
as this problem is about a blog i put the problem here.

please some one help me. i am waiting .
.....................................................
Currently i am developing a blog.

i have some problem in database layer . I use mysql.

here is the description.
My Blog facilitates administrator to put new threads and users to put comment to the threads.
I have planned to put administrators post in thread table where id is the primay key.

I tried to get thread table id as foriegn key in usercomment table where users can put their replies to administrartor thread.
problem is when i use primay key as usercomments id since i am not able to duplicate it.

it is obvious we want to give same number for usercomments in seperate threads but we can not if usercomments id is primary.

then what should be the primay key in the usercomments table . thread id also can not be use as primay since we may have may replies for one thread of administrator.


here is the code for administrator's thread table

Code: Select all

CREATE TABLE IF NOT EXISTS `thread` (
  `id` int(5) NOT NULL AUTO_INCREMENT,
  `title` varchar(50) NOT NULL,
  `post` text NOT NULL,
  `datePosted` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=48 ;



here is the code for user's usercomment table. actually this should be corrected as solve my problem but i put it here.
CREATE TABLE IF NOT EXISTS `usercomments` (
`postId` int(5) NOT NULL,
`commentId` int(5) NOT NULL,
`name_cm` varchar(20) NOT NULL,
`email_cm` varchar(100) NOT NULL,
`website_cm` varchar(100) NOT NULL,
`comment_cm` text NOT NULL,
`dateComment_cm` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`allow_cm` tinyint(1) DEFAULT NULL,
FOREIGN KEY (`postId`) REFERENCES thread(id),
PRIMARY KEY (`postId`,`commentId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



again another thing i want to do in usercomment's table is starting id from 1 in usercomment table.
because it is clear seperate threads can have same usercommentid.

please help me solve this problem .
At least tell me to handle this via programming side .
then i would ...........
thx in advance,
menuka.