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]
精彩评论