3
\$\begingroup\$

First off, I'm asking this question here because gaming and messaging are intimately connected. Why win if you can't gloat? Nevertheless, I won't be offended if this needs to be moved to overflow.

I need a PM system for my browser game, which already has a user and authentication system (To clarify: the game already has a user system). Specifically, I'm wondering if a PHP library already exists that I can use to save some time. Better yet, if anyone knows of a nice Codeigniter library for messaging, that would be perfect.

I already have a good idea on how I would approach this if I have to write my own, but it seems like something that would already exist in a robust form (despite my failed Google searches).

Requirements:

  1. Messages can be sent to individuals, multiple users, and guild mates (which is essentially just multiple users with server-side selecting who to target).
  2. Users can reply to individual or all recipients
  3. Messages appear in sender's outbox and receiver's inbox.
  4. Utilizes some 2-table design to handle marking read / deleting messages on a user-by-user basis
  5. Messages are semi-threaded. I.e. I won't necessarily have them appear in the GUI as threaded, but previous messages should be included at the bottom of a reply. Maybe better to say, previous messages are identifiable and accessible. (This, I'm not yet 100% sure how I would design if I write my own. I.E. whether to include the text of the previous message into the new message or somehow remember the previous message's database ID to link later)

Technology in Use: PHP, Codeigniter, Javascript, JQuery, Ajax, Sqlite

Note: I have seen that there are some similar questions on overflow, but they were very little help in the answer department. Otherwise, people were looking for either chat boxes or more umm.. like social media site threads, which I find a bit different than something you would find in a game.

\$\endgroup\$
1
  • \$\begingroup\$ AJAX IM is worth investigating but I am not sure if it does everything on your list - it should not be that hard to extend it to your liking. ajaxim.com \$\endgroup\$
    – John
    Jan 30, 2013 at 21:12

2 Answers 2

2
\$\begingroup\$

This sounds like a pretty simple task. First of all, you need a database to store your messages; presumably you already have one for your user accounts, but if you want recommendations, MySQL, PostgreSQL and SQLite are all common choices and should do fine.

Next, you'll need a table to store the messages. A typical message table should probably contain at least the following columns:

  • Message ID, the primary key to the table. This can be just an auto-incrementing number.
  • A timestamp of when the message was sent; useful for chronological sorting. Create an index on this column. (In fact, if you frequently sort messages by this columns, you may also want to include it as the last column of any other indexes.)
  • The sender of the message; a user ID. Also index this column (or mark it as a foreign key into your user table, if your DB supports those).
  • A parent message ID, for threading. May be NULL. Should be indexed as a foreign key to this table itself.
  • The title / subject of the message; a VARCHAR type should be good.
  • The body of the message, whatever variable character type your DB has that lets you store at least several kilobytes of text.

Obviously, that's just a bare-bones sketch of a message table. You might want to add some other fields, like a last edit timestamp if messages can be edited after sending. Also, some of the fields above could be left out if the information is stored in some other way; for example, the sender could instead be stored in the same table as the recipients (see below), and it could be more efficient to store the message body in a separate table as a form of vertical partitioning.

Now we also need to store the recipients. Since a single message might have several of those, we need a separate link table, with columns like this:

  • Message ID: a foreign key to the message table.
  • Recipient ID: a foreign key to the user table.

Make sure both of these columns are indexed, so that finding both the recipients of a given message and the messages sent to a given recipient will be efficient.

That's basically enough, but you might also want to add a few more columns. For example, this table could conveniently store a flag indicating whether or not the recipient has seen the message. Also, if you added a "role" column, you could use it to indicate different types of recipients (maybe some only got "carbon copies"?), or even use it to store the sender of the message, and any other possibly associated users, in this same table.

Note that you don't really need a separate ID column for this table: the first two columns above (and possibly the role column, if any) already make up a natural primary key together.

Here's what the definitions of these tables might look like in SQL. I've written these for MySQL since it's what I'm most familiar with, but they shouldn't be hard to adapt for other DBs:

-- here's our basic message table
CREATE TABLE message (
  message_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
  sent TIMESTAMP NOT NULL,
  sender INTEGER NOT NULL, -- foreign key to user table (not shown)
  parent_id INTEGER NULL, -- optional foreign key to this table
  title VARCHAR(255) NOT NULL
);

-- timestamp index for chronological sorting, also appended to other indices
CREATE INDEX i_sent ON message (sent);

-- foreign key indices (because MySQL doesn't really have true foreign keys)
CREATE INDEX i_sender_sent ON message (sender, sent);
CREATE INDEX i_parent_sent ON message (parent, sent);

-- optional, for sorting messages by title if that's a common use case
CREATE INDEX i_title ON message (title);

-- let's make a separate table for the body, just to show how it's done
CREATE TABLE message_body (
  message_id INTEGER NOT NULL PRIMARY KEY, -- also foreign key to msg table
  body TEXT NOT NULL
);

-- and finally, the recipient table
CREATE TABLE recipient (
  message_id INTEGER NOT NULL, -- foreign key to message table
  rcpt INTEGER NOT NULL, -- foreign key to user table (not shown)
  seen TINYINT NOT NULL DEFAULT 0, -- MySQL doesn't have a real boolean type
  PRIMARY KEY (message_id, user_id)
);

-- an optional index for quickly finding unread messages
CREATE INDEX i_rcpt_seen_msg ON recipient (rcpt, seen, message_id);

Using these tables, here are some examples of common queries:

  • A user's outbox, newest first:

    SELECT * FROM message
    WHERE sender = :sender
    ORDER BY sent DESC
    
  • A user's inbox, newest first:

    SELECT * FROM message
      JOIN recipient USING (message_id)
    WHERE rcpt = :recipient
    ORDER BY sent DESC
    
  • The number of unread messages to a user:

    SELECT COUNT(*) FROM recipient
    WHERE rcpt = :recipient
      AND seen = 0
    
  • One or more full messages with body and comma-separated recipient list:

    SELECT
      message.*,
      message_body.body AS body,
      GROUP_CONCAT(recipient.rcpt) AS recipients
    FROM message
      LEFT JOIN message_body USING (message_id)
      LEFT JOIN recipient USING (message_id)
    WHERE message_id IN (:message1, :message2, :message3)
    GROUP BY message_id
    
  • Replies to a message (iterate to find replies to replies, etc.):

    SELECT * FROM message
    WHERE parent = :message_id
    ORDER BY sent ASC
    

(In all the examples above, identifiers beginning with : denote placeholders to be substituted with (or bound to) values from the client.)

Also note that there are several other ways of storing hierarchical data like threaded messages in a database which avoid the need for iterated queries at the cost of some complexity. (Also, some database engines provide built-in support for recursive queries.) However, if you don't mind occasionally doing multiple queries in a loop to fetch a set of results, a simple parent message ID column like I used above will do the job just fine.

\$\endgroup\$
0
\$\begingroup\$

Ok to break this down.

1,2 and 3 would simply be a UI to send message and view the message from different "links/pages". When message sent it is just insert into the table which sends the message and reply to one... standard PHP/MYSQL insert.

4 is just a method of providing message state and changing it.

5 Threading in regards to web browsers is non-existant. However taking it to mean run Async and providing the user news they have a message without refreshing page, you could do it either by:

  1. Poll the table of user messages and count how many new there are for that user if > 0 show how many new ones there are.

  2. Use a pushing mechanism in regards to ui one way that works down to ie 6 is opening a 'long' connection to the server which as soon as the server has anything to give the user it puts it on that connection at which point the UI processes the response and request a new long connection. One such library for that is ape-project

In short.. I don't know of a library that will implement this for you and provide you the UI too which is majority of the work involved to add this to your game.

Hope this helps

\$\endgroup\$
1
  • \$\begingroup\$ websock for browser push notifications may come in handy \$\endgroup\$
    – TeAmEr
    Jul 30, 2013 at 2:38

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .