Forum Replies Created
-
AuthorPosts
-
steve g
Memberhave re-done it all again…..was getting confused on the moving of foeign keys into becoming partial primary keys….didn't really want to use autonums but have changed it to that now and think its a lot more simplified and will hopefully work when i add the data….fingers crossed
Users CREATE TABLE `Users` (
`username` varchar(20) NOT NULL default '',
`email` varchar(50) NOT NULL default '',
`password` varchar(8) NOT NULL default '',
PRIMARY KEY (`username`)
) TYPE=InnoDB
Forums CREATE TABLE `Forums` (
`forum_Title` varchar(100) NOT NULL default '',
`forum_description` varchar(150) default NULL,
PRIMARY KEY (`forum_Title`)
) TYPE=InnoDB
Topics CREATE TABLE `Topics` (
`topic_Id` mediumint(9) NOT NULL auto_increment,
`username` varchar(20) NOT NULL default '',
`forum_Title` varchar(100) NOT NULL default '',
`topic_Title` varchar(100) NOT NULL default '',
`topic_Started` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`topic_Id`),
KEY `username` (`username`),
KEY `forum_Title` (`forum_Title`),
CONSTRAINT `Topics_ibfk_2` FOREIGN KEY (`forum_Title`) REFERENCES forums“forum_Title`),
CONSTRAINT `Topics_ibfk_1` FOREIGN KEY (`username`) REFERENCES `Users` (`username`)
) TYPE=InnoDB
posts CREATE TABLE posts (
`post_Id` int(11) NOT NULL auto_increment,
`topic_Id` mediumint(9) NOT NULL default '0',
`post_Message` tinyblob NOT NULL,
`creation` datetime NOT NULL default '0000-00-00 00:00:00',
`username` varchar(20) NOT NULL default '',
PRIMARY KEY (`post_Id`),
KEY `username` (`username`),
KEY `topic_Id` (`topic_Id`),
CONSTRAINT `Posts_ibfk_2` FOREIGN KEY (`username`) REFERENCES `Users` (`username`),
CONSTRAINT `Posts_ibfk_1` FOREIGN KEY (`topic_Id`) REFERENCES `Topics` (`topic_Id`)
) TYPE=InnoDB
Memberyeh but the othe bits that are just “KEY”'s bit i have a KEY for topic_id iin the posts table(which is foreign yet not usrename which is also foreign.
and “KEY”'s in topics which is username and forum title which are both foreign….so do i also need to make another key for the posts table with the username????
wasn't sure on the using of the indexing but it said you have to index feriegn keys in this program in the help so this is what i have now…….just wondering if i still need to add more….so i know whether to move and and start doing the rest as will need to link it to dreaweaver now and start using it.
thanks for your input. 🙂
Memberright…..have recreated the database again and this is what i've cut and pasted out of the gui result window…
CREATE TABLE `forums` (
`forum_Title` varchar(100) NOT NULL default '',
`forum_Description` varchar(150) default NULL,
PRIMARY KEY (`forum_Title`)
) TYPE=InnoDB
CREATE TABLE `posts` (
`username` varchar(20) NOT NULL default '',
`topic_Id` mediumint(9) NOT NULL default '0',
`post_Message` tinyblob NOT NULL,
`creation` datetime NOT NULL default '0000-00-00 00:00:00',
PRIMARY KEY (`username`,`topic_Id`,`creation`),
KEY `topic_Id` (`topic_Id`),
CONSTRAINT `posts_ibfk_1` FOREIGN KEY (`topic_Id`) REFERENCES `topics` (`topic_Id`),
CONSTRAINT `posts_ibfk_2` FOREIGN KEY (`username`) REFERENCES `topics` (`Username`)
) TYPE=InnoDB
CREATE TABLE `topics` (
`topic_Id` mediumint(9) NOT NULL auto_increment,
`forum_Title` varchar(100) NOT NULL default '',
`topic_Title` varchar(100) NOT NULL default '',
`topic_Started` datetime NOT NULL default '0000-00-00 00:00:00',
`Username` varchar(20) NOT NULL default '',
PRIMARY KEY (`topic_Id`,`forum_Title`),
KEY `Username` (`Username`),
KEY `forum_Title` (`forum_Title`),
CONSTRAINT `topics_ibfk_1` FOREIGN KEY (`forum_Title`) REFERENCES `forums` (`forum_Title`),
CONSTRAINT `topics_ibfk_2` FOREIGN KEY (`Username`) REFERENCES `users` (`username`)
) TYPE=InnoDB COMMENT='InnoDB free: 4096 kB'
CREATE TABLE `users` (
`username` varchar(20) NOT NULL default '',
`password` varchar(8) NOT NULL default '',
`email` varchar(50) NOT NULL default '',
PRIMARY KEY (`username`)
) TYPE=InnoDB COMMENT='InnoDB free: 4096 kB'
this is what i now have, and have been reading over indexing and relationships and am still a bit confused about it all…..the keys, the foreign keys and the actual indexing of them.
does this look right and make sense???
thanks
🙂
Memberthere isn't any data in the tables am just trying to create from scratch…..it well let me create what i have outputted in the batch script but it only has the one foreign key relationship… i need to hook up the other ones too which is when i hit the problems……
i have just tried to re create the data base again in the gui and it lists the tables alphabetically so i can't have users at the top at it auto goes to the bottom….
this is the third time i've recreated to try and get round things…..and to be honest i don't think the relationships are wrong…so dunno why it won't let me create any others…
thanks for any help that's offered
Memberand what is ibfk 1, as i might be able to edit it myself…….was gonna use postgres instead but need to use dreamweaver too which doesn't support it although i think there's a plug in….but just want to get these tables linked so i can actuall code up the actual forum in php etc..
this is all new to me and have never tried to write anything like this before…so sorry if i sound stupid or i'm making blatent errors??
😉
-
AuthorPosts
