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.