Newsletter Software Database Solutions - MySQL & PHP

I recently build a newsletter application which. It allowed users to subscribe some different categories of newsletter. I have design some tables like these below.

category Table: id cname description...

subscriber Table: id sname category_id...

newsletter Table: id nname content...

category_newsletter Table: id newsletter_id category_id

When the manager create a newsletter, some categories will be selected. This is done by category_newsletter table, something like,

category_newsletter Table: id newsletter_id category_id

Everything is going well until now. But the question is, how to send the newslette开发者_如何学编程r to subscribers in categories selected? I have a solution, but I have no idea if this is ok.

I design another table named "newsletter_queue", when manager creates a newsletter and selects some categories, for example "cakephp category", then the system will select all subscribers in "cakephp category" and insert "email" filed, "subscriber_id" filed to the "newsletter_queue" table, so that the system has enough power to handle the email sending process even the manager pause it in sending.

So, anyone has some experience in newsletter core database? Talk about it. Thanks in advance!

Category Table: 
  CategoryId
  Name
  Description...

Subscriber Table:
  SubscriberId
  Name

Subscription Table:
  SubscriberId
  CategoryId

Newsletter Table:
 NewsletterId
 Name
 Content...

CategoryNewsletter Table:
  NewsletterId
  CategoryId

NewsletterQueue Table:
  SubscriberId
  NewsletterId
  Sent...

You do not need the additional id column in category_newsletter. You can create a primary key which combines both NewsletterId and CategoryId and it should be enough.

The subscriber table should also be split into two tables. You should have a subscriber table that has the id of the subscriber, and name. A second table for subscriptions which is the id of the subscriber and the id of the category, that way one user can subscribe to multiple categories as you stated.

When selecting who to send the newsletter to, you will also need to make sure you get the distinct email addresses, as it seems possible for a user to sign up for multiple categories and for the newsletter to be assigned to multiple categories.

If a user follows categories A and B and a manager creates a newsletter for categories A and B, if you just select all the users who subscribe to the selected categories it will return the user twice, once for each category they follow.

Insert into NewsletterQueue (SubscriberId, NewsletterId)
Select Distinct s.SubscriberId, cn.NewsletterId
From SubscriptionTable s
INNER JOIN CategoryNewsletter cn ON s.CategoryId = cn.CategoryId
WHERE cn.NewsletterId = [x]