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
Mysql forum lay out Need opinion
Moderator: General Moderators
-
pinehead18
- Forum Contributor
- Posts: 329
- Joined: Thu Jul 31, 2003 9:20 pm
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
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..
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..
-
pinehead18
- Forum Contributor
- Posts: 329
- Joined: Thu Jul 31, 2003 9:20 pm
So i should have a forum table. A thread table. and a topic table?
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) ).