Having two related tables - Im confused

Results 1 to 4 of 4

Thread: Having two related tables - Im confused

  1. #1
    Tom Levesque Guest

    Default Having two related tables - Im confused

    OK. Lets say I want to design a forum using SQL and ASP. So, first I have my table which holds all forum topics lets say it looks like this:<BR><BR>id &#124 topicTitle &#124 date &#124<BR>---&#124------------&#124------&#124<BR> &#124 &#124 &#124<BR><BR>Then I have a related table that contains individual posts within each topic:<BR><BR>id &#124 nameOfPoster &#124 date &#124 content &#124<BR>---&#124--------------&#124------&#124---------&#124<BR> &#124 &#124 &#124 &#124<BR><BR>Now, the "id" is the primary key in each meaning it is unique for each row. From what I understand there is a way to somehow link these tables together in SQL such that the ID in one table corresponds to the ID in the other. So if John Smith starts topic "I&#039m Confused" and it ends up being number 598 then I can check the other table, look at the row with id 598 and see all the posts that have been put in the topic "I&#039m Confused". This makes very logical sense to me, I just have no clue how to implement it using SQL. I understand the basic stuff: Select From ... Where, Create Table, Drop Table, Insert Into...Where, Update... But I&#039m confused about how two tables like this related - not in theory, but in terms of actual SQL code. Please help!<BR><BR>Tom Levesque

  2. #2
    Tom Levesque Guest

    Default RE: Having two related tables - Im confused

    OK As I read my last post (suppose I should do that before I hit "post") I notice I wasn&#039t quite clear.<BR><BR>I want ONE table which lists all the topics. Then I want SEVERAL TABLES one table for EACH ROW in the first table I created which lists all the posts within a topic and their related information. <BR><BR>SO here&#039s what I&#039m thinking:<BR><BR>SQL = "Create Table topics (id integer primary key, title varchar, postdate varchar, posterId varchar);"<BR><BR>conn.Execute(SQL) &#039or whatever<BR><BR>Whenever someone creates a topic, it is added to the first table and a secondary table is created to store posts within the topic:<BR><BR>SQL = "Create Table " & id & " (etc....);"<BR><BR>Now... I&#039m thinking this will get complicated because when someone looks at the topics list, the ID of the item they click on has to be used to find the correct secondary table that it will load to display all the posts within the topic. This is what I&#039m confused about -any ideas?

  3. #3
    Join Date
    Dec 1969

    Default RE: Having two related tables - Im confused

    Big mistake in design.<BR><BR>You do *not* want a table per topic. Honest.<BR><BR>You just want a "postings" table that uses one field as a "foreign key" into the "topics" table.<BR><BR>Really. Honest.<BR><BR>

  4. #4
    peterjl@austec.net.au Guest

    Default RE: Having two related tables - Im confused

    This idea of creating a new table each time someone starts a new topic is not a good idea. I think you only need one table (Forum)with fields:<BR>&nbsp;&nbsp;&nbsp;&nbsp;PostID - AutoIncrement - Unique id for a post<BR>&nbsp;&nbsp;&nbsp;&nbsp;Topic - The topic of the post<BR>&nbsp;&nbsp;&nbsp;&nbsp;Message - The text of the post - memo field<BR>&nbsp;&nbsp;&nbsp;&nbsp;ReplyID - The Post Id of the message to which this post replies<BR>&nbsp;&nbsp;&nbsp;&nbsp;DatePosted - Obvious<BR>&nbsp;&nbsp;&nbsp;&nbsp;VisitorName - Name supplied by person who posted this.<BR><BR>ReplyID is left blank (null) if the post is starting a new topic or thread. If the post is a reply to an existing post, then ReplyID = PostID of original post.<BR><BR>Now to list your topics, you do query:<BR> Select * from Forum where ReplyID is Null<BR>(i.e. you list the posts which are not replies)<BR><BR>Then if a topic is selected you can list out the thread by following the linked list provided by the PostID/ReplyID fields.<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts