Posted by Scot McConnaughay on 01/03/06 17:29

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

[Back to original message]