Database structure question...Emails scheduled for delivery?

Question

I am trying to implement a system where emails to large (or small) groups of people are scheduled to be delivered by a cron job (instead of in a loop, while the user is waiting for them to finish sending).

There are two types of email a user could send: an email to everyone in the subscribers table, or an email only to members of a group. I suppose I don't really need to include the email to group members since they will be emails to small groups and not the large (all subscribers) group.

I'm trying to figure out how to structure my database so it makes sense, but I'm having a hard time even explaining how it should work.

Do you have any experience with this sort of thing that you can share? How should I structure my database to keep track of emails that are waiting to be delivered?

Answer 1

Use a join table to link pending emails to users. Use another join table to manage Groups. When an email is destined for a group, a stored procedure could be used to populate the EmailRecipients table with the IDs of all members in the group.

Tables:

PendingEmails
  ID
  Subject
  Body

EmailRecipients (join table)
  EmailID
  UserID

Users
  ID

Groups  (join table)
  GroupID
  UserID

Answer 2

What you wish to implement is a basic queue utilizing your storage layer.

User (
    id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    email VARCHAR(125) NOT NULL
);

User_Group (
    id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    user_id INTEGER UNSIGNED NOT NULL,
    group_id INTEGER UNSIGNED NOT NULL
);

Group (
    id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(75) NOT NULL,
    description VARCHAR(255)
)

MailingList (
    id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    user_id INTEGER UNSIGNED NOT NULL,

);

# mails to be sent out to groups / all 
MailQueue (
    id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    group_id INTEGER UNSIGNED,
    time_to_send DATETIME DEFAULT CURRENT_TIMESTAMP
);

You would queue up group emails be entering entries in the MailQueue with a valid unix timestamp. You should create a group 'All' which you will assign each and every user to so that you can still utilize the tables properly. You would run your cron query like so:

SELECT User.email
FROM MailQueue
INNER JOIN User_Group ON (MailQueue.group_id = User_Group.id)
INNER JOIN User ON (User_Group.user_id = User.id)
WHERE MailQueue.time_to_send < NOW();

You need to ensure you DELETE the resultset after you have used them as you do not which to send duplicate email entries. If you wish to keep a log of sent emails, simply add another TINYINT(1) UNSIGNED field indicating sent_email = 1 and fix your SELECT query to only grab results if the value is 0.