Pavlos (pavlos) wrote,

  • Mood:
  • Music:

Geekworks: One-to-many messaging DB structure


As you couldn't have helped noticing, I play the online game of BattleMaster, and sometimes contribute to its development. There is an in-game message system, sort of like email. Sort of unlike email, it has the following properties:

  • Messages typically have many recipients. A few sets of recipients (e.g. "the council", "the north army") typically reccur.
  • It's desirable for players to manage and exchange such lists of recipients, for easy messaging.
  • It's desirable to reply to all or some of the recipients of a message
  • Players don't manage their messages (file, delete, etc) manually. They get deleted after a set time.
  • It's desirable to automatically filter messages by read/unread, time, recipient list, and other attributes.
  • It's desirable to track threads and organize message display accordingly.
  • It's desirable to define and forward an arbitrary subset of someone's message stream (for game situations where there is limited knowledge).
  • The most common operations are to view ones's message list (after applying a filter), followed by reply-all, reply, and compose.
  • Message traffic a few tens a day per player, and there are 1000 players, growing fast.
The messaging system, like the rest of the game, is implemented in MySQL (a standard relational database) and PHP (a C-like language with embedded SQL).

The existing DB structure doesn't meet many of these requirements and so is in need of a redesign. In very high-level terms, how would one design the DB structure of this message system?

As a starting point, one would probably want a relation of the form (messageID, playerID) to store who has received which message. This would work, but may involve slow queries, especially when wishing to filter by "messages sent to this particular group".

An alternative might be to define a relation of the form (playerID, groupID) to record unique message groups and then record message traffic as (messageID, groupID). This may have better performance in the common case, but poor performance if the number of unique message groups gets large.

A third way might be to somehow merge these, for example using a single relation (messageID, recipientID, RecipientType) where recipient type is "player" or "group". This may get the best of both worlds, but seems complicated and may not fit into SQL properly.

I wonder if there are any radically superior approaches. I confess my knowledge of databases, especially practical aspects, is sketchy. No-one in the (small) development team does this as a day job.



  • On Myth

    Hey! I'm mostly living in my shell these days, but here's a long-overdue essay from my other blog. Paul and Alison in particular have been formative…

  • Chomsky on Gaza 2009

    Everyone should read Chomsky's excellent article on Gaza 2009. Usually, Chomsky on the…

  • New blog

    Hi! This is just to let you know that I exist, although I don't really feel that blogging about my life is very interesting any more. I still live…

  • Post a new comment


    default userpic

    Your reply will be screened

    When you submit the form an invisible reCAPTCHA check will be performed.
    You must follow the Privacy Policy and Google Terms of use.