Re: Recursion with database tables

At 14:43 19.02.2003, Jono Bacon said: --------------------[snip]-------------------- >At the moment I have three tables: Topic, Message, Comment. A topic has >messages and each message can have comments. I have set this up with a >table each and a field for the id of the parent (e.g. a comment will >have the id of the message that it refers to). > >My mate said to me that I should really put all of this in one big table >with a parent and child field. He said that my code should then figure >out what is the child of what and how it is displayed. This are one-to-many relationships? If yes, then your mate is wrong, you can't easily implement one-to-many using a single table. A problem like yours is usually laid out like this (pseudocode): TABLE TOPIC id integer primary key; topic varchar; TABLE MESSAGE id integer primary key; id_topic integer foreign key references TOPIC(id); message text; TABLE COMMENT id integer primary key; id_message integer foreign key references MESSAGE(id); comment text; >I have two questions about this: > > 1. First, is this idea of using one big table with parent/child >fields better than my seperate table approach? See above. > 2. Secondly, how would I write the logic for the single table approach. Forget it, use the approach above :) -- >O Ernest E. Vogelsinger (\) ICQ #13394035 ^ http://www.vogelsinger.at/