The Schema

Let’s assume Courselore has a users table, a courses table, and a course_participations junction table that records which users are in which course, and each user’s role in that course: instructor or student.

Here are chat’s tables and the one index chat adds:

chat_rooms
  room_id      integer    primary key
  course_id    integer    foreign key -> courses, not null
  title        text       not null
  visibility   text       not null, 'public' or 'private'
  created_at   timestamp  not null

chat_room_members                   (members of private rooms only)
  room_id      integer    foreign key -> chat_rooms, not null
  user_id      integer    foreign key -> users, not null
  primary key (room_id, user_id)

chat_messages
  message_id   integer    primary key
  room_id      integer    foreign key -> chat_rooms, not null
  author_id    integer    foreign key -> users, not null
  body         text       not null
  created_at   timestamp  not null

index on chat_messages (room_id, message_id)

chat_room_members is a junction table. Its primary key is both columns, so a person is a member of a room at most once.

The author’s name is not copied into each message. It is read from users through author_id. So if a user changes their name, the new name shows on every message they posted.

Members of public rooms

chat_room_members holds only the members of private rooms. Public rooms have no member rows. Whether a user may read a room depends on its visibility:

• For a public room, the user must have a row in course_participations for the room’s course.
• For a private room, the user must have a row in chat_room_members for the room.

Why not give every member of every room a row, public rooms included? Then one check would work for both kinds of room. But a member row for a public room records only that the user is in the course, and course_participations already records that.

The order of messages

A room shows its messages in the order they were posted. We order them by message_id, not by created_at. Two messages posted close together can have the same created_at, and then two reads of the room could show them in different orders. Two messages never have the same message_id. Courselore has one database, and it gives each new message an identifier larger than every identifier before it.

All rooms share this one sequence of identifiers. So a room’s identifiers have gaps, but within a room they still increase in the order the messages were posted.

The index

A member reads a room’s messages in order: the newest first, then older ones as the member scrolls up. Each read takes a range of one room’s messages, ordered by message_id.

The index on (room_id, message_id) keeps one room’s entries together and sorted by message_id. So the database finds the room’s messages without a full table scan, and takes a range of them already in order. An index on room_id alone would find the room’s messages, but the database would then have to sort all of them.

Recall that an index makes writes a little slower: every new message adds one more index entry. At about one message a second, that cost is small. The reads happen much more often: every time a member opens a room or scrolls. So the index is worth the cost.