Posted by frizzle on 01/03/06 18:01

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.

[Back to original message]