Dragon Ball Z Uncensored

[johnbuisthegreat]

CREATE TABLE users
(
userID VARCHAR(24) NOT NULL,
password BLOB NOT NULL,
lastname VARCHAR(15) NOT NULL,
firstname VARCHAR(10) NOT NULL,
email VARCHAR(32) NOT NULL,
datejoined TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
avatar VARCHAR(32) NOT NULL,
PRIMARY KEY(userID)
)
;

CREATE TABLE topics
(
topicID MEDIUMINT AUTO_INCREMENT NOT NULL,
userID VARCHAR(24) NOT NULL,
ptime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
ptitle VARCHAR(64) NOT NULL,
topicbody TEXT NOT NULL,
PRIMARY KEY(topicID)
)
;

CREATE TABLE comments
(
replyID MEDIUMINT AUTO_INCREMENT NOT NULL,
topicID ,
userID VARCHAR(24) NOT NULL,
ptime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
retitle VARCHAR(64) NOT NULL,
rebody TEXT NOT NULL,
PRIMARY KEY(replyID)
)
;

[Theldorrin]

I would have a userid based on an autoincrement field, and have another field for username.

And many of your varchar fields are too short.

Also, you the usefulness of a topic table is questionable, and depends on what the message board has to support. If first posts in threads are identical to all the others, then you could just use a user and post table.
_________________
@}-,-'- *~*~* Member of the FTU Elegant Tea Party Society *~*~* -'-,-{@

[johnbuisthegreat]

Yeah but the final needs 3 tables and I am at loss for what a 3rd would be I am trying to break it up in my head how it would be needed.

Users (store all there information and links them to posts)
Posts (holds posts and also replies? (because they are in the same post))
What would a useful 3rd table be used for?

I also have to have search functions also.

about the VARCHARs yeah I know most are short I just had to check on them but the actual body of the post (IE what I am typing now) that is also a VARCHAR or that TEXT thing I have already written?

[Theldorrin]

Well, it depends on what the requirements for the message board are.

If it's very basic, then the third can be topics, but it's not absolutely necessary to do it that way (although it'd possibly be faster).

User
Topic (this table has the thread name, and any other information that is listed when you see a listing of threads in the forum index)
Post (this contains the message body, as well as the topic it's in)

Note that creating a thread would result in a topic entry AND a post entry, since they don't contain the same information (although the topic may summarize information about the posts, like a last posted date or something).
_________________
@}-,-'- *~*~* Member of the FTU Elegant Tea Party Society *~*~* -'-,-{@

[johnbuisthegreat]

ok so user table and I would have a topics with a topicID pk then a post table that would just contain all posts on the board but it would be related to a topicID so they would be sorted upon output

I may not be saying it right but I believe I have a better grasp, thanks.

[Theldorrin]

Yes, you get all of the posts that have a given topic id, and then you can sort based on post id or post date (if you store that).
_________________
@}-,-'- *~*~* Member of the FTU Elegant Tea Party Society *~*~* -'-,-{@

[johnbuisthegreat]

John E Buis II
CIS 231
Milestone 2

CREATE TABLE users
(
userID MEDIUMINT AUTO_INCREMENT NOT NULL,
username VARCHAR(32) NOT NULL,
password BLOB NOT NULL,
lastname VARCHAR(20) NOT NULL,
firstname VARCHAR(20) NOT NULL,
email VARCHAR(40) NOT NULL,
datejoined TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
avatar VARCHAR(32) NOT NULL,
PRIMARY KEY(userID)
)
;

CREATE TABLE thread
(
threadID MEDIUMINT AUTO_INCREMENT NOT NULL,
userID MEDIUMINT NOT NULL,
topicID MEDIUMINT NOT NULL,
threadtime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
threadtitle VARCHAR(96) NOT NULL,
threadbody TEXT NOT NULL,
PRIMARY KEY(threadID)
)
;

CREATE TABLE replies
(
replyID MEDIUMINT AUTO_INCREMENT NOT NULL,
threadID MEDIUMINT NOT NULL,
userID MEDIUMINT NOT NULL,
retime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
retitle VARCHAR(96) NOT NULL,
rebody TEXT NOT NULL,
PRIMARY KEY(replyID)
)
;

CREATE TABLE topics
(
topicsID MEDIUMINT AUTO_INCREMENT NOT NULL,
topicName VARCHAR(32) NOT NULL,
totime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
userID MEDIUMINT NOT NULL,
PRIMARY KEY(topicsID)
)
;


Ok my Teacher looked it up and got some changes.

This is a cheese message board so it will be
Topics > Threads > Replies