Advice on forum to built

frizzle wrote:

Hi there,

I'm going to build a simple forum in mySQL.
I've thought about it for a while now, but still can't figure it
out completely:

If i have say 5 main categories,
One has 5 sub-categories.
Each sub category consists of individual threads.
Wich each has the possibility to be commented.

The question concerns the thread & comments part.
My first idea was to create one table Threads, and one
Comments.
The Threads-table would have the original post, starting a thread,
and the Comments table would have the comment, and the id of
the thread it belongs to. (so all comments there are in the whole
forum)

Now i thought of the following:
I could create a new table for each thread to save it's comments in.
And call it e.g. 'Comments_4576', (belonging to thread 4576)

Pro is a lot faster to load, delete etc. but contra is more difficult
to
count cross-tables and everything.

What could i do best?

As you said in the beginning :)

TABLE Category
ID
...
ParentID ... REFERENCES Table(ID)

TABLE Thread
ID
...

TABLE Message
ID
...
ThreadID ... REFERENCES Thread(ID)
ReplyTo ... REFERENCES Message(ID)

is one possibility coming from

1) Category may have subcategories (0..*)
2) Subcategory must have parent category (1)
3) Category consists of threads (0..*)
4) Thread consists of messages (1..*)
5) Message could either be top-level or a reply to one of the messages
in thread

Another possibility is a "linear" thread, i.e. posts are simply listed
one after another, in which case there is no ReplyTo field.
I wouldn't say that having a table for each thread is a good design.

Read some on relational database design :)
Btw, I think this is rather a topic for design or sql group :) Has
nothing to do with PHP :)
--

- lüpher
---------------------------------------------
"Man sieht nur das, was man weiß" (Goethe)

I agree, this is probably a good topic for a database forum, more than
for the PHP forum... but, since it is here:

What would be the advantage of having a table for each thread, does that
not go against the idea of database normalization? I would think that it
would cause quite a few duplicate fields, at least in the fact that each
'thread' table would be essentially the same.

Also, it seems to me that SQL / MySQL is very fast. If the database is
on a decent server what would be the speed advantage of each thread
having its own table?

Just curious, not bashing your idea Frizzle :)

Scot

Lüpher Cypher wrote:

frizzle wrote:

Hi there,

I'm going to build a simple forum in mySQL.
I've thought about it for a while now, but still can't figure it
out completely:

If i have say 5 main categories,
One has 5 sub-categories.
Each sub category consists of individual threads.
Wich each has the possibility to be commented.

The question concerns the thread & comments part.
My first idea was to create one table Threads, and one
Comments.
The Threads-table would have the original post, starting a thread,
and the Comments table would have the comment, and the id of
the thread it belongs to. (so all comments there are in the whole
forum)

Now i thought of the following:
I could create a new table for each thread to save it's comments in.
And call it e.g. 'Comments_4576', (belonging to thread 4576)

Pro is a lot faster to load, delete etc. but contra is more difficult
to
count cross-tables and everything.

What could i do best?

As you said in the beginning :)

TABLE Category
ID
...
ParentID ... REFERENCES Table(ID)

TABLE Thread
ID
...

TABLE Message
ID
...
ThreadID ... REFERENCES Thread(ID)
ReplyTo ... REFERENCES Message(ID)

is one possibility coming from

1) Category may have subcategories (0..*)
2) Subcategory must have parent category (1)
3) Category consists of threads (0..*)
4) Thread consists of messages (1..*)
5) Message could either be top-level or a reply to one of the messages
in thread

Another possibility is a "linear" thread, i.e. posts are simply listed
one after another, in which case there is no ReplyTo field.
I wouldn't say that having a table for each thread is a good design.

Read some on relational database design :)
Btw, I think this is rather a topic for design or sql group :) Has
nothing to do with PHP :)

--
Scot McConnaughay

Scot McConnaughay wrote:

I agree, this is probably a good topic for a database forum, more than
for the PHP forum... but, since it is here:

What would be the advantage of having a table for each thread, does that
not go against the idea of database normalization? I would think that it
would cause quite a few duplicate fields, at least in the fact that each
'thread' table would be essentially the same.

Also, it seems to me that SQL / MySQL is very fast. If the database is
on a decent server what would be the speed advantage of each thread
having its own table?

Just curious, not bashing your idea Frizzle :)

Scot

Lüpher Cypher wrote:

frizzle wrote:

Hi there,

I'm going to build a simple forum in mySQL.
I've thought about it for a while now, but still can't figure it
out completely:

If i have say 5 main categories,
One has 5 sub-categories.
Each sub category consists of individual threads.
Wich each has the possibility to be commented.

The question concerns the thread & comments part.
My first idea was to create one table Threads, and one
Comments.
The Threads-table would have the original post, starting a thread,
and the Comments table would have the comment, and the id of
the thread it belongs to. (so all comments there are in the whole
forum)

Now i thought of the following:
I could create a new table for each thread to save it's comments in.
And call it e.g. 'Comments_4576', (belonging to thread 4576)

Pro is a lot faster to load, delete etc. but contra is more difficult
to
count cross-tables and everything.

What could i do best?

As you said in the beginning :)

TABLE Category
ID
...
ParentID ... REFERENCES Table(ID)

TABLE Thread
ID
...

TABLE Message
ID
...
ThreadID ... REFERENCES Thread(ID)
ReplyTo ... REFERENCES Message(ID)

is one possibility coming from

1) Category may have subcategories (0..*)
2) Subcategory must have parent category (1)
3) Category consists of threads (0..*)
4) Thread consists of messages (1..*)
5) Message could either be top-level or a reply to one of the messages
in thread

Another possibility is a "linear" thread, i.e. posts are simply listed
one after another, in which case there is no ReplyTo field.
I wouldn't say that having a table for each thread is a good design.

Read some on relational database design :)
Btw, I think this is rather a topic for design or sql group :) Has
nothing to do with PHP :)

--
Scot McConnaughay

Well, thanks for the replies, and continuing the discussion here ...
It would certainly be a big bunch of duplicate fields, with exact the
same structure.

I know it would go against normalization, but on the other hand, i
thought it'd get a big deal faster if it knew in what table to search
immediately,
without skipping 1000-nds of comments not concerning the current
thread.
Maybe i'm just underestimating the power of PlayStat ... erm, mySQL.

I don't know beforehand how popular this forum will be, and how many
threads, and comments will be in it. I'm just affraid that it'll get
slow.
On the other hand, it'll be easier to count different things, combine
queries etc. when everything's in 1 table....

Frizzle.

Scot McConnaughay wrote:

I agree, this is probably a good topic for a database forum, more than
for the PHP forum... but, since it is here:

What would be the advantage of having a table for each thread, does that
not go against the idea of database normalization? I would think that it
would cause quite a few duplicate fields, at least in the fact that each
'thread' table would be essentially the same.
It does go against it :)
And that's just it - think of it this way:
Database is used to store information about the world (that has some
known objects)
Table is used to store information about different objects and/or how
they relate to each other
Fields are used to describe objects

When designing a database, it is best to first think of which objects
the world consists of. In this case, the world is the forum, the objects
are threads and messages. Then, one thinks about what attributes
describe objects (for example, a thread has a unique id, topic, etc., a
message has a unique id, date, maybe a name of the poster, etc.) Then
objects become tables and attributes become fields. Finally, relations
between them are made.

Putting each thread in a separate table is basically saying that each
thread is a different object..


Also, it seems to me that SQL / MySQL is very fast. If the database is
on a decent server what would be the speed advantage of each thread
having its own table?

Yes, it is :) You'll probably not see sufficient change in speed, unless
there is millions of records. So, I don't see any advantage in using
separate tables. I see a disadvantage, though :) First, one has to
remember that each table is stored in a separate file, plus, there are
index files. Then, suppose I want to add another attribute to thread.
Having one table, it's easy. Having thousands of tables - not so easy :)
--

- lüpher
---------------------------------------------
"Man sieht nur das, was man weiß" (Goethe)

frizzle wrote:

As you said in the beginning :)

TABLE Category
ID
...
ParentID ... REFERENCES Table(ID)

TABLE Thread
ID
...

TABLE Message
ID
...
ThreadID ... REFERENCES Thread(ID)
ReplyTo ... REFERENCES Message(ID)

is one possibility coming from

1) Category may have subcategories (0..*)
2) Subcategory must have parent category (1)
3) Category consists of threads (0..*)
4) Thread consists of messages (1..*)
5) Message could either be top-level or a reply to one of the messages
in thread

Another possibility is a "linear" thread, i.e. posts are simply listed
one after another, in which case there is no ReplyTo field.
I wouldn't say that having a table for each thread is a good design.

Read some on relational database design :)
Btw, I think this is rather a topic for design or sql group :) Has
nothing to do with PHP :)

--
Scot McConnaughay

Well, thanks for the replies, and continuing the discussion here ...
It would certainly be a big bunch of duplicate fields, with exact the
same structure.

I know it would go against normalization, but on the other hand, i
thought it'd get a big deal faster if it knew in what table to search
immediately,

I doubt it greatly :)
without skipping 1000-nds of comments not concerning the current
thread.
Maybe i'm just underestimating the power of PlayStat ... erm, mySQL.
Don't forget about indexes :) The primary key is always indexed so,
there is no "skipping" records. Event a binary tree index would get
there fast :) Say, if you have 16 million threads with 16 millions
consequent ids, and you want to retrieve 15.999.999-th record, you won't
have to skip 16 million records to get there :) With binary tree, it'll
only take 32 steps :)

I don't know beforehand how popular this forum will be, and how many
threads, and comments will be in it. I'm just affraid that it'll get
slow.

It is more likely that it'll be slow if the server won't be able to
handle the load :)

--

- lüpher
---------------------------------------------
"Man sieht nur das, was man weiß" (Goethe)

Lüpher Cypher wrote:

frizzle wrote:

As you said in the beginning :)

TABLE Category
ID
...
ParentID ... REFERENCES Table(ID)

TABLE Thread
ID
...

TABLE Message
ID
...
ThreadID ... REFERENCES Thread(ID)
ReplyTo ... REFERENCES Message(ID)

is one possibility coming from

1) Category may have subcategories (0..*)
2) Subcategory must have parent category (1)
3) Category consists of threads (0..*)
4) Thread consists of messages (1..*)
5) Message could either be top-level or a reply to one of the messages
in thread

Another possibility is a "linear" thread, i.e. posts are simply listed
one after another, in which case there is no ReplyTo field.
I wouldn't say that having a table for each thread is a good design.

Read some on relational database design :)
Btw, I think this is rather a topic for design or sql group :) Has
nothing to do with PHP :)
--
Scot McConnaughay

Well, thanks for the replies, and continuing the discussion here ...
It would certainly be a big bunch of duplicate fields, with exact the
same structure.

I know it would go against normalization, but on the other hand, i
thought it'd get a big deal faster if it knew in what table to search
immediately,

I doubt it greatly :)

without skipping 1000-nds of comments not concerning the current
thread.
Maybe i'm just underestimating the power of PlayStat ... erm, mySQL.

Don't forget about indexes :) The primary key is always indexed so,
there is no "skipping" records. Event a binary tree index would get
there fast :) Say, if you have 16 million threads with 16 millions
consequent ids, and you want to retrieve 15.999.999-th record, you won't
have to skip 16 million records to get there :) With binary tree, it'll
only take 32 steps :)

I don't know beforehand how popular this forum will be, and how many
threads, and comments will be in it. I'm just affraid that it'll get
slow.

It is more likely that it'll be slow if the server won't be able to
handle the load :)

--

- lüpher
---------------------------------------------
"Man sieht nur das, was man weiß" (Goethe)

Thanks Lüpher,

One question though; what i mean with skipping,
is that there are comments of multiple threads in 1 table.
This way, mysql has to skip the comments that do not concern
the current thread.

Would it be wise to make the related ID's indexes? Like:
Table 'Comments'
Fields - id (unique, primary, auto incr)
- thread_id (index)
- title
- etc.

Or am i getting confused?

Frizzle.

(ow yeah, your remark on changing the tables structure convinced
me of doing it all in 1 table)