Hello all,
I am using MySQL version: 5.0.37
I am creating a forum. I am designing the tables for the threads and their replies and the relationship between them. Please have a look at these images and give me your thoughts. I have also included the schema.
http://i284.photobucket.com/albums/ll40/JavaPHPNinja/threadsTableDesign.jpg
http://i284.photobucket.com/albums/ll40/JavaPHPNinja/threadsLogicDesign.jpg
CREATE TABLE Threads (
threadID INT NOT NULL AUTO_INCREMENT
, threadSubject VARCHAR(100) NOT NULL
, threadContent TEXT NOT NULL
, link VARCHAR(200)
, poster VARCHAR(100) NOT NULL
, PRIMARY KEY (threadID)
);
CREATE TABLE ThreadReplies (
replyID INT NOT NULL AUTO_INCREMENT
, replySubject VARCHAR(100)
, replyContent TEXT NOT NULL
, poster VARCHAR(100) NOT NULL
, PRIMARY KEY (replyID)
);
CREATE TABLE ThreadsRepliesRelationship (
threadID INT NOT NULL
, replyID INT NOT NULL
, INDEX (threadID)
, CONSTRAINT ThreadsFK FOREIGN KEY (threadID)
REFERENCES Threads (threadID) ON DELETE CASCADE
, INDEX (replyID)
, CONSTRAINT RepliesFK FOREIGN KEY (replyID)
REFERENCES ThreadReplies (replyID) ON DELETE CASCADE
);
1) Is the on cascade correct, i have them in both tables? If a thread reply was deleted would it delete the actual thread - the first post?
Thanks all, :)