An index of indexes - help with table design

Gandalf · September 20, 2022, 8:52am

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 · September 20, 2022, 9:50am

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 · September 20, 2022, 10:04am

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 · September 20, 2022, 11:56am

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 · September 20, 2022, 2:36pm

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 · September 20, 2022, 3:05pm

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 · September 20, 2022, 3:13pm

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

r937 · September 20, 2022, 7:35pm

i love this post

DaveMaxwell · September 20, 2022, 7:41pm

Too many knots…

Gandalf · September 21, 2022, 10:36am

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

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 · September 21, 2022, 10:43am

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 · September 21, 2022, 10:48am

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 · September 21, 2022, 11:40am

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 · September 21, 2022, 2:46pm

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 · September 21, 2022, 2:55pm

  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…

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

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

Gandalf · September 21, 2022, 3:07pm

Bingo! Thanks, Dave.

Gandalf · September 29, 2022, 3:56pm

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.