An index of indexes - help with table design

[Gandalf]

I have a simple guestbook script which just has a chronological list of comments, but I would like to add the ability to reply to a comment so I will have threaded comments.

The following is my schema and I’m struggling to work out what to add to get my threaded comments. I get that I need an index of indexes but I can’t get my head around it.

CREATE TABLE IF NOT EXISTS "guestbook" (
  "id"  INTEGER,
  "name"  TEXT,
  "email" TEXT,
  "comment" TEXT,
  "timedate"  TEXT,
  "ipaddress" TEXT,
  "approved"  INTEGER,
  PRIMARY KEY("id")
);

(I’m using SQLite which is why the dates are stored as text.)

[m_hutley]

when you say “threaded”:… do you want reply-to-replies, or just… reply-to-parent “threading”?

if the latter, just stick a parent in there with type integer - if parent is null, it was a root guestbook entry. If parent is not null, it was a response to a root guestbook entry.

if the former…bleh. (There are ways to do it, but… bleh.)

[Gandalf]

That’s a good question. I had kinda thought - nah, I hadn’t thought! I think the reply to parent would do the job, and be easier to implement.

Thanks, squire.

[DaveMaxwell]

If you want threaded either way, all you really need is a secondary id in the structure which will point to another id. Something like

CREATE TABLE IF NOT EXISTS "guestbook" (
  "id"  INTEGER,
  "name"  TEXT,
  "email" TEXT,
  "comment" TEXT,
  "timedate"  TEXT,
  "ipaddress" TEXT,
  "approved"  INTEGER,
  "parentRecordId" INTEGER
  PRIMARY KEY("id")
);

Then you build a recursive function in your code which pulls a post and then calls itself to find any posts that have it as the parentRecordId. You CAN do it in SQL if you really want to, but in code is easier and faster.

[Gandalf]

I now realise why I was thinking it was more complicated.

A parent entry will always occur before a child entry, so when listing all entries, having output a parent entry I will need to search the entire table to find any children to output. Isn’t that so?

I was therefore thinking I needed an additional table giving child posts.

[DaveMaxwell]

I’m looking at it like a post here.

• An initial guestbook entry will be placed here with no parent id (NULL or something to that effect)
• Any comments to a guestbook entry will have a parent id of the initial guest book entry.
• Any comment to a comment will have a parent id to the comment being commented on
• And so on ()

Pseudo logic

// an initial entry will have a value of -1 since it's the first post
// I'd probably use NULL instead but using -1 for simplicity
initialEntry = -1;   
ShowGuestBook(initialEntry)

ShowGuestBook(int parent) {
   // pull the entries from the guestbook
   SELECT id, name, email, comment FROM guestbook where parentRecordId = parent

   for each entry found {
       // Guestbook Entry Display Code goes here...
       : 
       // now show all entries which are responses to this particular entry
      ShowGuestBook(id);
   }
}

So this is going to show something like:

• Entry
  • Comment
  • Comment
    • Comment
  • Comment
• Entry
  • Comment
    • Comment
  • Comment

I’d probably have ShowGuestbook() return a string, so the sub-portions can be wrapped to help with display issues (inner list/div/whatever), etc.

[Gandalf]

Thanks, guv. I shall need to put my thinking cap on!

[r937]

@DaveMaxwell

I’m looking at it like a post here.

i love this post

[DaveMaxwell]

@r937

i love this post

Too many knots…

[Gandalf]

Much to my relief I have turned your pseudo-code into real code, using an empty string to mean ‘no parent’.

@DaveMaxwell

I’d probably have ShowGuestbook() return a string, so the sub-portions can be wrapped to help with display issues (inner list/div/whatever), etc.

Yes, I think I need to know how many levels in I am so I can apply the right class to the comment. I was thinking just a number, but perhaps your suggested string has more to it than just the level…

[m_hutley]

@DaveMaxwell

And so on ()

and thats the part where it gets iffy.

If you have a Re: Re: Re: Re: Re: Re: Re: Re: Re: Re: Re: Re: Re: Re: Re: Re: Your Guestbook post…

#1: You’re making a call to the database for every one of those Re’s.
#2: You’re trying to style an element that many layers deep on your webpage.

Unless your guestbook is going to be… rediculously large, you may want to consider dumping the entire table into memory and having javascript or whatever backend you’re using reform it into a nested object that you can more easily navigate.

[m_hutley]

Note that to create an output like you get here, where the display is flat, but it allows responding to posts, you actually only need 1 call, with 2 pieces of information:

Instead of just storing a parent, store a parent and a thread.
When you need to display the thread, pull all entries with the thread ID.
Any post with a parent can be referenced in-memory, because the parent must be in the thread.

When creating a response, you need the parent id; you can pull the thread from the parent’s entry and duplicate it (or pass it as well. either way).

[Gandalf]

Yes, too many levels would be silly, but it should be easy enough to limit the number of levels - once I can figure out where to increment and reset the level count.

[Gandalf]

So… I’m trying to work out what level I’m at for any given entry. I tried the following but it didn’t give the right answer.

function ShowGuestBook($db, $parent, $level) {
  $query = 'SELECT id, name, comment, timedate
    FROM guestbook
    WHERE approved = 1 AND parentId = ?';
  $stmt = $db->prepare($query);
  $stmt->execute([$parent]);
  $rows = $stmt->fetchAll();

  foreach ($rows as $row) {
    // display gb entry

    ShowGuestBook($db, $row['id'], ++$level);
  }
}

ShowGuestBook($db, $initialEntry = '', $level = 0);

[DaveMaxwell]

  foreach ($rows as $row) {
    // display gb entry

    ShowGuestBook($db, $row['id'], ++$level);
  }

Your issue is here, especially if you have more than one comment to an entry…

1. Comment 1 will pass in level + 1 (which would be right)
2. Comment 2 will pass in level + 2 (ruh roh…)

Increment the level once outside the loop, and it should be correct.

[Gandalf]

Bingo! Thanks, Dave.

[Gandalf]

This works a treat.

I should like to add infinite scrolling so the initial page displays quickly, but I’ve not found a tutorial for infinite scrolling when extracting information from a database, and I’m not sure how the LIMIT clause would work practically with nested queries.