Simple Messaging system for a website/app

[TechBoyJK]

Hi Guys,

I'm setting up the messaging system for my webapp, and I pretty much have it working but I want to bounce my database logic off a few people to see if I'm doing things right. I'm using mySQL.

The messaging system will be pretty simplified. I'm not trying to recreate gmail, but just a lightweight intrasite messaging component that will allow users to send basic messages to and from.

Requirements

*messages can be saved/archived
*messages can be deleted
*messages can be flagged as spam
*messages can be sent to multiple users
*users can create categories for their messages
*users can save messages as drafts
*users can decide to keep the draft after sending
*new messages will be flagged as new
*messages can be flagged (no reason, just generic flag)
*messages will be flagged as 'replied' if the user replied


I've seperated the data into 4 tables

messages
message content
message categories
drafts

I've seperated the actual message content from the primary message table because if a user sends the message to multipe users, i'd have to duplicate the content. So what I did is make it so that the contentID is included in the primary message record. So if userA sends the message to both userB and userC, 2 records are inserted in the primary message database. However, both records would reference the same content ID since it was the same message.

Obviously, a seperate table is needed for categories. And I created a seperate table for drafts because not nearly as much info is needed and there's no reason to lump it into the same message table (because there's no recipient and I can put the content and draft info in the same record).

One thing I want to explain is why there is a 'sender' and 'recipient' for each flag type. (spam, deleted, archived).

If the recipient of a message wants to delete it, the message would be flagged as 'recipientDeleted' and when the users messages are fetched, any 'recipientDeleted' messages would be skipped. This way it would appear to be deleted. However, this doesn't mean the sender wanted to delete it, so the 'senderDeleted' flag would remain unflagged, so the sender would still be able to see it. Same for spam, archived, etc.

So everything looks good to me. I've simplified the tables as much as possible. I've created them in a way to prevent duplicate data and so far it's worked fine in the app for my testing.

Based on all of this, do these tables look ok? Anything I'm missing? I know some people don't like my table naming convention, but that's how I roll. =)

CREATE SCHEMA `dbMessages`;

USE dbMessages;




CREATE TABLE `tbl_userMessages`(
`messageID` int NOT NULL AUTO_INCREMENT,
`contentID` int NOT NULL,
`senderID` int NOT NULL,
`recipientID` int NULL,
`new` bit NOT NULL DEFAULT 0,
`replied` bit NOT NULL DEFAULT 0,
`flagged` bit NOT NULL DEFAULT 0,
`senderArchived` bit NOT NULL DEFAULT 0,
`recipientArchived` bit NOT NULL DEFAULT 0,
`senderDeleted` bit NOT NULL DEFAULT 0,
`recipientDeleted` bit NOT NULL DEFAULT 0,
`senderSpammed` bit NOT NULL DEFAULT 0,
`recipientSpammed` bit NOT NULL DEFAULT 0,
`sentDate` datetime NOT NULL,
`viewDate` datetime NULL,
`referrerMessageID` int NULL,
`senderIP` varchar(15) NOT NULL,
PRIMARY KEY (`messageID`),
UNIQUE KEY `messageID_UNIQUE` (`messageID`))
ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ;


CREATE TABLE `tbl_userMessagesCategories`(
`messageCategoryID` int NOT NULL AUTO_INCREMENT,
`userID` int NOT NULL,
`category` nvarchar(30) NOT NULL,
PRIMARY KEY (`messageCategoryID`),
UNIQUE KEY `messageCategoryID_UNIQUE` (`messageCategoryID`))
ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ;


CREATE TABLE `tbl_userMessagesContent`(
`contentID` int NOT NULL AUTO_INCREMENT,
`category` varchar(30) NULL,
`body` text NULL,
PRIMARY KEY (`contentID`),
UNIQUE KEY `contentID_UNIQUE` (`contentID`))
ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ;


CREATE TABLE `tbl_userMessagesDrafts`(
`messageID` int NOT NULL AUTO_INCREMENT,
`senderID` int NOT NULL,
`recipientID` int NULL,
`body` varchar(1000) NULL,
`createdDate` datetime NOT NULL,
`createdIP` varchar(15) NOT NULL,
`lastModifiedDate` datetime NULL,
`lastModifiedIP` varchar(15) NULL,
`saved` bit NULL,
PRIMARY KEY (`messageID`),
UNIQUE KEY `messageID_UNIQUE` (`messageID`))
ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ;

 

[Hmongkeysauce]

I'm not sure if you need the 'new' attribute in the first table. Shouldn't you be able to determine that from the date it was sent? Basically, if sentdate > user_lastlogindate then new.

Going with your logic of "I've seperated the actual message content from the primary message table because if a user sends the message to multipe users, i'd have to duplicate the content. So what I did is make it so that the contentID is included in the primary message record. So if userA sends the message to both userB and userC, 2 records are inserted in the primary message database. However, both records would reference the same content ID since it was the same message. ", I might even put the attributes senderId and referrerMessageId (I think this refers back to the parent message?) in the userMessagesContent table. A message can only be sent by one person. And if that message is sent again by a recipient, that would technically make it a new message, no?

 

[TechBoyJK]

Thanks for the reply.

With the 'new' setting.. it's really just whether or not it's been read. So messages will appear as new until they are read.