0

I'm writing a simplified version of an email system on a site I'm creating.

The basic premise is users can message each other on the site, best example would be ebay, you can message other users on the site itself and it basically acts as an email system.

What I have is the messages themselves, who they're from, to and the text.

I would also like to have basic "read/not read" and "deleted", possibly even "sent" categories.

something like this:

table structure:

id, to, from, subject, body, dateTime

What I'd like to know is if it makes more sense to just add a "read" and "deleted" column to that table, and search for those particular conditions, when I need them on the site, or if it is more efficient/best practice to have another "categories" table and then have a join table putting a message id with category id, and then using that join table to serve up the info when it's requested?

If my question doesn't make sense forgive me, I'm still pretty new at this stuff.

Improve this question

4 Answers 4

Reset to default
3

i would add two columns:

read tinyint(1),
deleted tinyint(1)

and use them as booleans.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

I'm leaning this way pretty hard right now, my main concern is that the potential scope of user base is extremely large, I'm talking in the hundreds of thousands of users. I'm just a little worried about speed and how a query of this manner would function on a potentially very high load. Every other site I've done so far has had no more than 3 users, all admins so, these kinds of issues are new to me. Hence the desire to get it right the first time.
You could do something like having a specs table but that will only slow down the query even more as having a simple 1 or 0 is smaller than having a simple 1 or 0 and a reference to the message. it will also be quicker as no joining is needed.
I've had some great suggestions from everyone but I think I"m gonna go with this one, it's the simplest and likely quickest for me for now at least, although @Pierre-Olivier's suggestion may end up being more useful in the long run if the project evolves further down the road. Thanks everyone!
3

I've just implemented something similar. I put the column right in the "message" table. I chose the following:

ReadDate DATETIME DEFAULT NULL,

If the message hasn't been read, ReadDate is NULL. When the user has read it, I fill in the date it was read. This allows the sender to know when it was read by the receiver.

Improve this answer

2 Comments

When you move it to the deletedMEssages table, are you keeping all the same info, except now the message is "deleted"? What I"m aiming for in the long run is that if people so choose they can move it back, or if they deleted something accidentally they can still see the stuff if they still need it.
@DavidMorin, I removed that part of my answer because I realized you're treating deleted more like another folder (common implementation for accidental deletes). In my case, I was talking about archiving. My archived messages get moved to a DeletedMessages table with same schema plus one "DeletedDate" column. It still might be viable for your use, considering it would improve performance when reading messages. Would just be slower when deleting.
1

I would create three tables for this system. One for your threads (group of messages), one for your actual messages and another one for your categories.

Something like this,

MessagesThreads
--------------------
id (int, serial)
from (int, foreign: Users.id)
to (int, foreign: Users.id)
subject (varchar)
category (foreign key: Categories.id)

MessagesContent
--------------------
id (int, serial)
threadId (int, foreign: MessagesThreads.id)
content (text)
date (datetime)
status (tinyint) (0 for unread, 1 for read, 2 for deleted, for instance)

Categories
--------------------
id (int, serial) 
name (varchar)

This would be a properly normalised database.

With this, a thread can contain one-to-many messages (because the foreign key is in MessagesContent), a message is attached to only one tread and a thread can have one category.

I think this is the most efficient way for storing your messages, according to your specifications.

Improve this answer

5 Comments

This is very helpful and would make sense if I was doing a "forum style" messaging system. What I'm aiming for with this little guy here is more of an e-mail style, so basically I message you, and when you message me back it'll be another "email" and the body of the email will contain the text already sent back and forth to each other, same idea as a reply sort of thing.
@DavidMorin that has nothing to do with how the data is actually stored. Whether the previous emails appear or not depends on whether you query the previous emails or not. Also, emails systems that have that feature, actually copy the data and send it in a new email with the reply appended.
My schema was designed to actually avoid repeating messages content in each email/message. I designed it to keep a record of the discussion/previous messages. In my opinion, repeating messages content in each reply might not be the best solution at long term. Your database will become large very quickly.
@Pierre-OlivierBourgeois I don't see a problem with copying the email data into a new email when you click "Reply". If it is not copied - you are saying you should reference it? Then what happens if the original is changed? All references (replys) will have replied to something different.
You would simply do SELECT <...> FROM MessagesContent WHERE threadId = discussion ORDER BY date DESC ? That would give you all the messages ordered by date for a particular discussion (group of emails) ?
0

Given a user account table like this:

CREATE TABLE tbl_accounts(
    id INT NOT NULL AUTO_INCREMENT,
    PRIMARY KEY(id),
    email_addr VARCHAR(50) NOT NULL,
    passkey BLOB /* AES KEY */
) ENGINE = InnoDB;

You will need a mail box table to find an email depending on the user account & the folder they are checking (inbox|outbox). We separate this table keep the database "normalised".

CREATE TABLE tbl_email_box(
    account_id INT, /* owner|sender of mail */
    FOREIGN KEY (account_id) REFERENCES tbl_accounts(id) ON DELETE SET NULL,
    email_id INT,
    FOREIGN KEY (email_id) REFERENCES tbl_emails(id) ON DELETE SET NULL,

    folder TINYINT(1), /* 0=inbox->account=owner; 1=outbox->account=sender; */

    status TINYINT(1) DEFAULT 0, /* 0=unread, 1=read */

    date TIMESTAMP     /* because the query executes when they check/send mail, */
                       /* then record is created (date=sent|received date) */
) ENGINE = InnoDB;

This table will store the actual email data

CREATE TABLE tbl_emails(
    id INT NOT NULL AUTO_INCREMENT,
    PRIMARY KEY(id),
    subject VARCHAR(100),
    message BLOB,
    created TIMESTAMP
) ENGINE = InnoDB;

Hope this helps.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.