Page 1 of 1

Mysql forum lay out Need opinion

Posted: Fri Jul 23, 2004 4:16 pm
by pinehead18
Ok, i'm writing a mini forum for my site and quite frankly i don't know if my sql design would work or not and i really need opinions from the community. Stick with me as i describe whats going on.

First i have my main forum sql. Which is fine i am sure. But this houses the forum name's and description.

forum_id bigint(5) No auto_increment
name varchar(155)
description text

I dont' seem to have any problems with this. But here comes my delema.

I have one more table called threads. Now this table houses all the topics in the viewforum.php and all the threads period. I identify them together by id.

id bigint(5) auto_increment
tid bigint(5)
topic_id varchar(155)
topic varchar(155)
uname varchar(155)
body text
forum_id bigint(5)
date varchar(25)


Now id is auto_increment and that is just to id the rows.
Then i have tid (thread id) This links together the topic and the thread. So my view thread statement views all threads with the tid of whichever.

topic_id if is set to 1 tells the first post with the tid number to display in the view forum topics. if set to anythign else it is just a basic thread reply.

Will this work ok or does anybody have any suggestions?

Thank you
Anthony

Posted: Fri Jul 23, 2004 4:32 pm
by feyd
It seems odd that you'd use a 64-bit signed integer to only store 4 digits plus a sign (forum_id, id, tid). It appears you wanted to use the maximum size for varchar fields, however the maximum size is 255.

It sounds like it'd basically work. However I would suggest upgrading the id's to a slightly larger number of digits, and change them to unsigned.

If you look at phpbb, it holds the actual post text in a seperate table, with a few added attributes like bbcode tags on/off, html tags on/off, and so on.. Depending on the compatibilty you wish for with other databases, you may want date to be a unix timestamp ( INT(11) ).

Forum descriptions will likley not go beyond 255 characters, this could create some overhead that would require optimization of the forum's table at regular intervals..

Posted: Fri Jul 23, 2004 5:02 pm
by pinehead18


If you look at phpbb, it holds the actual post text in a seperate table, with a few added attributes like bbcode tags on/off, html tags on/off, and so on.. Depending on the compatibilty you wish for with other databases, you may want date to be a unix timestamp ( INT(11) ).
So i should have a forum table. A thread table. and a topic table?