[SOLVED] Creating a inbox/messaging system

[paschim]

I am trying to make a messaging system for my site members, kinda like the forum inbox! But i really have no clue how the logic structure of this thing will be like! I mean do i store all messages in a database and give them a userid, and then when required, search through the entire table to locate them? Also for this, how will i enforce a inbox limit of say 50 messages?

 

All ideas and suggestions are welcome.

[corbin]

Pretty simple concept.

 

"But i really have no clue how the logic structure of this thing will be like!"

 

Basic SQL skeleton:

 

CREATE TABLE users (
    user_id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(32) NOT NULL,
    password char(32) NOT NULL
);

CREATE TABLE messages (
    message_id INT PRIMARY KEY AUTO_INCREMENT,
    sender INT NOT NULL,
    receiver INT NOT NULL,
    subject VARCHAR(255),
    content TEXT,
    date_sent INT(10) NOT NULL,
    date_read INT(10) DEFAULT 0
);

CREATE INDEX idx_sender ON messages(sender);
CREATE INDEX idx_receiver ON messages(receiver);

 

 

Then, to enforce a limit, when ever someone went to send someone a message, you would check if he had 50 messages.

 

SELECT COUNT(*) FROM messages WHERE receiver = 1;

 

 

Anyway, if you have further questions, I'll try to answer them.

[Slip]

I'm doing a project very similar to that right now. I have approached it with a one-per-folder method i.e. There are three tables set up in MySQL (inbox, sent and trash). There are basic column fields to capture message details plus an 'unread' column where it can store an INT to  show if it is read/unread.

 

CREATE TABLE  `emuzo`.`inbox` (
  `mid` int(11) NOT NULL auto_increment,
  `created` datetime default NULL,
  `from` int(11) default NULL,
  `to` int(11) default NULL,
  `subject` varchar(200) default NULL,
  `message` text,
  `unread` int(11) default NULL,
  PRIMARY KEY  (`mid`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;


CREATE TABLE  `emuzo`.`msgs_sent` (
  `mid` int(11) NOT NULL auto_increment,
  `created` datetime default NULL,
  `from` int(11) default NULL,
  `to` int(11) default NULL,
  `subject` varchar(200) default NULL,
  `message` text,
  PRIMARY KEY  (`mid`)
) ENGINE=MyISAM AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;


CREATE TABLE  `emuzo`.`msgs_trash` (
  `mid` int(11) NOT NULL auto_increment,
  `created` datetime default NULL,
  `from` int(11) default NULL,
  `to` int(11) default NULL,
  `subject` varchar(200) default NULL,
  `message` text,
  `unread` int(11) default NULL,
  PRIMARY KEY  (`mid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

 

The method suits my application as there won't be a major usage of messages being sent so I don't mind the design not being the most efficient way of approaching it.

 

I've been designing this whole site in an ad-hoc kind of way so it may change slightly as I work through the project.

[corbin]

Don't take this the wrong way, but errr...  That's a kinda weird schema.

 

I guess it has it's advantages since you know where everything is, but you could centralize everything and have to move rows around much less frequently if you did something like:

 

CREATE TABLE  emuzo.msgs (
  `mid` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `created` datetime default NULL,
  `from` int(11) NOT NULL,
  `to` int(11) NOT NULL,
  `subject` varchar(255) default NULL,
  `message` text,
  `unread` bit default 1,
   deleted bit default 0
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE INDEX idx_to ON emuzo.msgs (to);
CREATE INDEX idx_from ON emuzo.msgs (from);
--you might want to put an index on unread if you ever wanted to show only read or unread

 

 

(P.S.  Why `unread` int(11) default NULL?  Why not tinyint or bit or something?

[Slip]

Don't take this the wrong way, but errr...  That's a kinda weird schema.

 

I guess it has it's advantages since you know where everything is, but you could centralize everything and have to move rows around much less frequently if you did something like:

 

CREATE TABLE  emuzo.msgs (
  `mid` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `created` datetime default NULL,
  `from` int(11) NOT NULL,
  `to` int(11) NOT NULL,
  `subject` varchar(255) default NULL,
  `message` text,
  `unread` bit default 1,
   deleted bit default 0
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE INDEX idx_to ON emuzo.msgs (to);
CREATE INDEX idx_from ON emuzo.msgs (from);
--you might want to put an index on unread if you ever wanted to show only read or unread

 

 

(P.S.  Why `unread` int(11) default NULL?  Why not tinyint or bit or something?

 

Thanks for the feedback. I'm still learning MySQL skills so I know thy way I do things are not very efficient - hence the INT(11) etc.

 

I'll have a chew over your suggestions and see if it is not too late to make some ammendments.

[paschim]

Thanks guys, I understand now! I have created the structure, and it seems pretty much good for my purpose! Thanks to the suggestions you gave!