Building a Personal Messaging System in PHP

[Nick_W]

Okay,

This seems simple but previous discussions have taught me that I somtimes overlook stuff ;-)

If I were to build a PM system much like the 'sticky mail' here, with PHP/MySQL do you guys think I have it covered with a DB Schema like this?

msgid ¦ memberToID ¦ memberFromID ¦ Read ¦ Timestamp ¦ Message

Read = 1 or 0 for read/unread

How's that look? ;-)

Nick

[brotherhood of LAN]

>How's that look? ;-)

Looks good :)

You'll maybe want a title for them.

How are you going to notify people when they have mail? I was thinking maybe you could have a 1 bit field in the user_table to see if they have mail or not, maybe save checking the stickymail table for every pageview of a logged in user!

[BCMG_Scott]

What about a subject line and read time?

Also, what are your column types and contraints (NOT NULL, UNSIGNED, AUTO_INCREMENT, etc)?

Scott

[Nick_W]

>>You'll maybe want a title for them/subject

Yes, just an oversight when I posted ;)

>>1 bit field

BOL, could I get you to explain as to a child? I know there are some/lots of folks that will know what you mean, unfortunaly I'm a bit of a dummy ;)

>>colum types.

Yeah, auto inc for the msgid, INT's for the member id's, SMALLINT for read, and text and timestamp fields for the other two.

Oh, and a VARCHAR(100) fro the subject...

Nick

[BCMG_Scott]

I assume you meant text for Message. Maybe want to add a fulltext index on Message? (for use in a match/against search)? Just a thought.

Otherwise looks good :) - can't think of anything else to add.

Scott

[brotherhood of LAN]

>>>1 bit field

You can use a char(0) NULL field, its either 1 bit or two, I think its one.

Just thinking of the queries that will be going on when people are logged into your system.

If you have the char(0) field in with your users details, i.e. their login status, you can see if the bit is set, and if it is, they have mail.

If they have mail then you can query the stickymail table with the PHP, otherwise don't look up sticky table at all.

if($charfield!= NULL)
{
// User has mail
SELECT * FROM stickymail WHERE memberToID = $user
}
else
{
// No mail, no need to lookup table
}

All depends on the system at large I guess :) Would be interesting to know if WW members stickyboxes, on average, are empty or not.

[dmorison]

Speaking of Personal Messaging Systems, does anybody know Brett's timetable for releasing BestBBS?

[bestbbs.com...] just says "Coming Soon". This is by far the slickest BBS system i've come across on the web, and from what i've heard moderators say about it there's plenty of cool stuff behind the scenes too.

[Nick_W]

Mine is NEVER empty - 62 in there at the moment ;-)

SO, when someone sends someone a PM I would add 1 to the colum of the person the mail is being sent too?

And when they open they're mailbox I
d unset it?

Is that what you mean?

Nick

[brotherhood of LAN]

>>when someone sends someone a PM I would add 1 to the colum of the person the mail is being sent too?

Yep that's pretty much what I was thinking. It would only take a bit per user.

Sorta guessing that this is related to the other thread about DB's and editor access? :)

At least if you have the char(0) field that's one less table to lookup everytime an editor is hovering around pages.

//added
maybe its just as worthwhile to have an INT field in with user_details, with the number of stickies they have. we can see how many stickies are in our stickybox when reading this thread, but only need to know the details of the stickies when we click on the page (i.e. the only time we need to access teh sticky table)

[Nick_W]

Yes, I'll have to give it some thought. It's all very simple in discussion but the coding is not quite as straight forward I'm thinking ;-)

Yes, all my recent PHP threads are for the same system ;)

Thanks for the help guys!

Nick

[lorax]

Well - I'm going to throw a wrench into the works. What I'd like to see is the ability to provide threaded reference. So if I initiate an msg to someone and they respond, I'd personally find it very useful to be able to see the thread of the conversation.

So I would include an additional table

threads

msgID ¦ refMsgID

and use this as a way to track the threads. Initial msg would have NULL in the refThreadID but any reply msgs would be logged with the msg you replied to. Yes - No?

[ShawnR]

"...do you guys think I have it covered with a DB Schema like this? ..."

Looks good to me, and I agree with the additions suggested in previous posts (e.g. title). To see what you're missing, have a think about the requirements/use-cases. Have a look at the fields available on good email clients. Also have a look at the specs for smtp. For example, will you provide features for message severity/importance, message confidentiality/sensitivity, message expiry date (delete message if not read by expiry date...), receipt request, ...? Decide what features you want to provide, and that will guide the database development.

"... 1 bit field in the user_table to see if they have mail or not ..."

Good idea, avoid a look-up... If you go down that path I'd suggest you use a built-in boolean type. (I think mySql provides BOOL). Let the database designers optimise the implementation of it, rather than you.

Shawn

[DrDoc]

int for memberIds is not a good idea... What if the person wants to send the same message to several people? Also, are you using ints for the member names?

[DrDoc]

This is something I've used in the past... Don't know how well that would work:

Table STICKY:
userid: unique, index
accept: int(1)
addressbook: LONGTEXT fulltext
blocked: LONGTEXT fulltext
messages: LONGTEXT fulltext

Addressbook is stored one address per line, same with blocked addresses.
Accept can be NULL, 1, or 2 (NULL=accept all, 1=block anyone not in address book, 2=block all)
Messages are stored one msg per line (\n converted to <br> before stored.
really convenient, you only need to convert back if replying to msg)

Msg example:
MD5_ENCRYPTED_NOW¦To¦Cc¦From¦NOW()¦Folder¦Flag¦Message¦

MD5_ENCRYPTED_NOW is a ... MD5 encrypted NOW() ;) It serves as the msg id
To and Cc are comma separated lists of those fields
From is of course who it's from
NOW() is when it was sent
Folder is a virtual folder, usually stored like "F: foldername" so I can
search for "¦F:" to get folders
Flag is either 0 (New), 1 (Read), 2 (Replied), 3 (Flagged)
Message is the message :)

Now, you can split that column up to separate columns in a new table if you want

userid: index
messages: LONGTEXT fulltext
msgid: the MD5 thingy
To: VARCHAR(255)
Cc: VARCHAR(255)
From: VARCHAR(however long a user id can be)
Sent: INT (for a unix timestamp. However, don't make it TIMESTAMP)
Folder: VARCHAR(32)
Flag: INT(1) (make NULL=new, 1=read, etc)
Message: LONGTEXT fulltext

[ShawnR]

Looks good, Dr Doc. Probably ran very fast.

>>>"...you can split that column up to separate columns in a new table if you want ..."

That would be my preference. Its not that I'm a Normalisation zealot; its just that you get a bunch of advantages from normalisation. For example, it means I can search or sort on any of the fields such as 'show me all messages in my inbox I have not read' or 'show me all messages in my inbox from xxx' or 'sort my inbox by date sent' or 'sort my inbox by sender'. For the same reasoning I'd make the address book entries a separate table (a join table between the user and other users in the user table).

Dr Docs code probably is very fast, and normalisation may compromise performance slightly for some operations (and speed it up for others), but I think it would be more flexible. It probably boils down to personal preference.

Shawn

[daisho]

One extra thing you may want to add is a "Folder Column". Would be easy to add. A NUMBER column linking to a "Folders" table:

id ¦ name

ie:

0 ¦ inbox
1 ¦ saved
2 ¦ outbox
3 ¦ deleted

etc etc etc...

Boy you really have a lot of projects on the go right now eh. (As a canadian I even have to type 'eh') :)

daisho.