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

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)

[Back to original message]