February 16th, 2004

I'm back from the land where people are aggressive and stressed to the land where they are resigned and depressed.

This time round, Greeks in general were horrible! They were often very rude, smoked too much, and were building far too many roads and sporting contraptions. They made a mockery of democracy by nominating Mr. Papandreou III as leader of the "socialist" party by "popular vote" (everyone could go and vote for Mr Papandreou if they wanted). His (actually neoliberal) party prompty admitted a well-known conservative and a well-known former communist.

Greeks were also unable to deal with snow, as they are every year, claiming that snow happens too rarely for them to be able to deal with it. The civil aviation authority fined the Athens airport operator €3M for failing to clear the snow. They made a half-hearted effort and so a plane had a minor accident running into a pile of snow a few minutes after mine took off!

Over in Edinburgh, things are looking good, and I think they could turn much better if I slept normal hours.

This sign is overused! Can it go back to meaning gay/bi/alt-friendly please?

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 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.