• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 587
  • Last Modified:

Database Structure Newsletter Subscription

I am have an issue when designing a newsletter subscription table.
Here's the problem.

I am trying to store for all my customers the newsletters they have subscribed / unsubscribed to. I have two possible solutions, none of which are too elegant.

1. Add columns to my Customer table one for each newsletter, each column being a BIT. 1 for subscribed, 0 for unsubscribed. However everytime I add a new newsletter I will have to add a new column to this table.

2. Have a Newsletter table which has all the newsletters. Then have an associative entity which links the customer table to the newsletter table and an attribute on this associative entity which states whether the customer has subscribed or unsubscribed. However, the problem here is that everytime I create a new newsletter I have to add a new row to the table for each customer signalling if we are subscribing or unsubscribing them by default.

Anyone got any better solutions?
1 Solution
LucasMS Dynamics DeveloperCommented:
Take a look at this:


They have  a script to generate a MySQL db, if you can get your hands on MySQL db to view the database structure.

Otherwise i always use a third party software like Constant Contact.
Bill BachPresidentCommented:
Simplify the problem a bit.  You have a Newsletters table.  You have a Customer table.  Why keep track of "unsubscribed" newsletters?

Just build a new table "Subscriptions" which contains a foreign key to Customer and another to Newsletter, along with any other data about the subscription, such as date started.  This would keep your data in 3NF.  Findind subscriptions is easy, and finding "non-subscriptions" for a customer is still possible with NOT IN.

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now