Building PollGab.com: Design a Database for Live Session Questions

PollGab
19 Comments

Years ago when we did online presentations or live Office Hours webcasts, attendees would type their questions into the “Questions” section of whatever webcast platform we were using – WebEx, GoToWebinar, Zoom.

This sucked because:

  • People would put questions all over the place (Questions, Chat, email, etc)
  • It was hard to keep track of which questions were still left to be answered
  • People couldn’t write questions in advance
  • People kept copy/pasting the same questions over and over because they’d think we hadn’t seen them
  • We didn’t have a way to tell which questions other folks wanted us to answer

So when I decided to start taking Office Hours more seriously a couple of years ago, Richie and I sketched out a better way.

That way is www.PollGab.com, and it’s what Richie’s been working on for the past year. You might have seen links to it during my Office Hours:

Viewers can post messages, upvote messages that they’d like to see me discuss, and flag inappropriate messages.

To get an idea of how it works, you can see my room, see the list of active rooms, and create your own room for free. If you run into problems or have a question, email Help@PollGab.com.

When you’re clicking around in there, if we’ve done our job right, things are going to seem pretty intuitive and obvious. However, it takes a lot of planning and work to make an app seem effortless, so let’s talk about some of the design goals we came up with.

The data updates automatically. If you sit there watching a popular room, you can see new questions coming in, and see questions move around on the leaderboard, automatically without touching Refresh on your browser. In a a perfect world, you should be able to load a room’s page just once, and watch the action unfold.

Viewers & voters don’t need to log in. In today’s GDPR-sensitive world, we want to store as little data as practical, and delete it as quickly as possible. We do need a way for room owners to be recognized so we can let them see the list of flagged questions, un-flag them, ban problem users, or set a question as “now showing”, plus some other neat tools I’ll mention this week.

The lack of logins does mean people can game the system: if you keep spawning new incognito browser windows, you can repeatedly upvote your own question. For now, we’re not really concerned about that. (Gotta pick your battles when you’re building a new site.)

If anyone flags a message, it disappears from everyone’s screens. Given that we’re talking about the Internet, where people are endlessly creative and foul, we wanted to make it easy for the audience to help us police. On the flip side, if the room owner is looking at the list of flagged questions, and decides to UN-flag a question (because it’s appropriate and safe), then that message reappears automatically. Plus, it can’t be flagged again, because the room owner has already believed it to be appropriate.

The room owner has additional controls. They can stop taking messages, or clear the message queue entirely.

Tag, it’s your turn:
design the database.

Let’s pretend that someone sketched all this out for you and asked you the following questions:

  1. What data needs to be stored to make all this work?
  2. What database or persistence layer should we use?

I’m curious to hear what you think. Let me know in the comments, and then in the next blog post, we’ll discuss what we chose to do.

Previous Post
Big Data Clusters: Out of Aces
Next Post
Building PollGab.com: Designing the Data Model

19 Comments. Leave new

  • I fear that people gaming the system with incognito windows is why my question keeps getting jumped in favor of less interesting* ones (and I’m too honest to cheat). If you’re logging voters’ IP addresses, it might be interesting to see how many have multiple upvotes from the same IP.
    * Less interesting to _me_ anyway… and forgive the slightly off-topic-ness of this reply, but maybe it points to a reason to log user IP addresses, and deal with all the concerns that opens up…

    Reply
  • Maral Guerra-torres
    February 28, 2022 5:30 pm

    Welp this is pretty awesome. Just signed up/bought a link. Thank you Brent and Richie!

    Reply
  • Michael J Swart
    February 28, 2022 5:32 pm

    For a relational design, I’d pick:

    Asker (TenantId, AskerId, Name, Email)
    Questions (TenantId, AskerId, CreatedDate, IsFlagged, DateAsked, Votes)
    AdminConfig(TenantId, Key, Value)

    Persistence layer:
    I’d skip a key value store because this has to scan a bunch.
    But it’s going to be tiny. I know you folks have AWS Amazon Aurora experience. That would be pretty cheap I think.

    But I could probably be convinced otherwise. I am curious to know how close I got.

    Reply
    • I like how you say “for a relational design” – and yeah, that’s true if we did a relational design.

      We didn’t initially want Asker.Email, but we ended up getting it so we could use Gravatar.

      About “this has to scan a bunch” – I’ll raise an eyebrow and ask why?

      Reply
      • Michael J Swart
        March 1, 2022 2:41 pm

        key-value stores like DynamoDb shine when retrieving singular records. I imagine that a query would look like “SELECT * FROM Questions WHERE TenantId = 124 AND IsFlagged = 0 ORDER BY Votes DESC”. Which is several records. (So by “scan” I didn’t mean a full table scan, but a query that returns rows a MB at a time).

        So I guess that’s why I dismissed Dynamodb (We evaluated DynamoDb a while ago and when things get busy – like 10,000 records/second – they get really expensive). But on the other hand, I always like to think of it’s original use case: Storing items in Amazon’s shopping cart. And that returns a tiny amount of records (which sounds like the size of data you’re talking about).

        If you or Richie pulled this off using DynamoDb (or CosmoDb or whatever), I’d love to read a post about your experience.

        Reply
        • Yeah, we don’t actually need to sort in the database. We need ALL of the questions in the room, and then we sort on the client side – both by votes descending in one pane, and creation date descending in the other pane.

          The user browser’s CPU licensing cost is zero. 😉

          Reply
        • And hey, if we get to the point where we’re handling 10,000 questions per second, sustained, that would be AMAZING because we’ll be rich. I think that’s how that works.

          Reply
  • Not what you asked . . . I’m thinking that if anyone can enter any room and flag as inappropriate or game upvote any questions, then eventually with large scale adoption or entry of sensitive topic questions, saboteurs are likely to ruin the experience for room owners and users. And the saboteurs would never be locked out, so I think I would make one small change . . . perhaps give my room users a simple pin or password to open my room. It’d be the same for everyone using that room (and remain until I changed it) so wouldn’t deter an invited saboteur, but should slow down the great unwashed from flooding a page they otherwise know nothing about and doing harm to an otherwise happy group of questioners.

    It’s a nice site – and the instant constant refresh makes it work.

    Reply
  • I’d agree with Michael – though I think you also need some info about the rooms (tenantid, name, isactive(bit)).

    Wouldn’t have guessed the front end would use WordPress – that was a surprise, but I suppose why not…

    Reply
  • On the History page:

    Before PollGab there was SurlyDev, a dedicated, indefatigable Twitch mod. Since there is only one SurlyDev that we know of – the universe is a big place – we needed a sustainable solution.

    Reply
  • Robert Sterbal
    March 1, 2022 2:12 pm

    It would be great to have a version of this on my local intranet.

    Heck, I could even use a version of this for my home internet as a chore chooser 🙂

    Reply
    • Goodness, if your local network doesn’t have access to the internet, your priority one task is nice and clear: fix that first. Then a whole new world of internet services will open up to you. Cheers!

      Reply

Leave a Reply

Your email address will not be published.

Fill out this field
Fill out this field
Please enter a valid email address.