How do I determine if customers have already received a newsletter?

Question

The problem: When we were sending newsletters to customers, there was no way to confirm if the customer already received the mail. We have come up with two different approaches:

1. (Boss's Idea) Each time mail was being sent, do an INSERT in a db with the title of the newsletter being sent and the email address which is receving the email address. To ensure that any email address does not receive the same email twice, do a SELECT in the table and find the title of the newsletter being sent:

   if (title of newsletter is found)
   {
     check to see of the email we are sending mail to is already present. if it does, do not send mail
   } 
   else
   {
     send mail
   }
   

2. (My idea) create a column called unique and mark it as UNIQUE. Each time mail was being sent, concatenate email + newsletter id and record it in the UNIQUE row. The next time we do a "mysql_affected_rows" check to see if our INSERT was successful, we send the mail, else, there is already a duplicate and no need to send it.

Is either approach objectively superior? If yes, which one and why?

Answer 1

I prefer the boss solution because it's easier to comprehend. I immediately understood it, while your own solution is less obvious.

For the people that will have to maintain/alter this in the future, this is important.

Answer 2

Your solution may be faster and typed with lesser code, but also is somewhat of a hack or workaround. It works only exactly for this problem. Think of later extension. What if you want later output all newsletters a customer got. In your solution it is a complicated select over a part of the UNIQUE-string. I personally would prefer the solution of your boss.

Answer 3

My only criticism of your boss's approach is that if you ever happen to want to send a newsletter with the same title as a previous one it will not get delivered. How about using something you know will be unique (UUID per newsletter)?

Your solution is more fragile.

1. It relies on the correctness of the append operation between the two data formats;
   1. Which would require work to be unambiguous (where does the title end and the email start?)
   2. It has to manipulate user generated data (the email address), which means you have to deal with anything the user decides to put in there.
   3. Is not very self-documenting, future maintainers would have to look at the code and the database schema to understand what the data is.
2. If you do decide you want to change the way your algorithm works in the future, upgrading your database data will involve having to un-concatenate all the rows. Which is unnecessary pain. Whereas it would be comparatively easier to go from you boss's solution to your solution (or some other solution)

Answer 4

As Mnementh said. Indeed, the purpose of the database is very likely not only to prevent having mails sent twice. And the danger of hacking around this way is that you'll end up with a database full of unique columns, all for a different purpose. It's a common mistake in DB design, and one the boss rightfully avoids. So I wouldn't be so sure about your statement regarding who is the best programmer. You definitely never had to maintain/update/refactor a database where some wizzkid has been hacking away in your style.

Answer 5

Ok,

1. What happens should the client change their email address? The two column solution enables a simple update to be applied
2. Test by exception (which is what your unique method implies) is a generally considered poor, if I were to go with the concatenated string I'd still want to explicitly test for existence before doing the insert rather than throwing the data at the db and looking for something to break.
3. And as a belt and braces check you could always have a unique constraint across both columns (assuming that is supported by MySQL).
4. In terms of your code, you want to start with a query that says give me all the users that match my selection criteria that haven't already been sent this newsletter - again the split column facilitates this.
5. In terms of design, I'm with @Zenph - user table (with unique ID), newsletter table (with unique ID) and NewsletterSentToUser table (NewsletterID, UserID and possibly its own unique ID 'cos that can make some things easier). Queries are a teeny bit more complex but its a lot more resilient.
6. Oh, and in terms of reporting which user (email address) has been sent which newsletter (subject) the two column solution makes that far more elegant

Answer 6

First, there is no general purpose way to determine if an email has been received. You can only determine if an email has been sent.

Second, the way the codeblock is presented makes it appear as if you would break this conditional into two parts. No need. You would simply do a query like so:

SELECT count(*) from newsletter_sent WHERE title = '$title' AND email = '$email'

There is no need to check for the title first. Also, the title of a newsletter might change. Will this affect the reliability of this solution? Most definitely. For that reason, none of the solutions are ideal.

The ideal solution is to assign an ID to each newsletter, whether this be obtained from a third party site (if you're using third party newsletter management) or otherwise.

Answer 7

Your boss's solution is the better of the two.

But you're actually both wrong. :-)

Each mailing (ie, title) should have a unique id. So you need a table that tracks that. Never rely on a title being unique like that.

Then you need a 'working' table that includes the mailing id, the email address, and a flag for whether the message is sent (integer, 0 or 1). Before you start mailing, do a mass insert into that table of all the addresses you intend to send to.

Then, your mailer will operate against that table, flipping the sent flag as it processes each message. This way a mailing can easily be resumed if the server is interupted for some reason by simply querying those rows that do not have their sent flag set. It also never hurts to track a timestamp of each sent email.

In addition, you can insert the working table's row id as part of an unsubscribe link included in each mailer, so you'll know which mailings caused people to unsubscribe.

This approach allows you to handle multiple mailings (if you have multiple newsletters for example) and maintain a very detailed log of who got what email and when.

Answer 8

Personally, I don't like reliance on UNIQUE constraints. I'd go the SELECT way your boss proposes. It's probably easier to read, too.

BTW, you have to know the rules:

1. The boss is always right
2. If the boss isn't right, apply rule #1

Answer 9

Compromise
Why not implement your idea AND his idea... go a step further?

Assuming you have a customer table with unique customer id's + customer email address... create a table with NewsletterID, NewsletterDescription.

Then you can make a third table that has a unique FK of CustomerID + NewsletterID.

What if customer changes email? Are you going to send all the newsletters to the new address? Are you going to change all the email address? What if you change the newsletter name to correct a typo?

In your solution, it's greatly complicated by having to parse out the email to update part of one field. Same for newsletter name.

In his solution, you are going to have to dance around changing email addresses and/or names in separate columns (much easier than changing a single column, but still changing what is likely to be more than is needed).

Database Normalization
You need to normalize the database to remove the need to change redundant information, specifically data that WILL change: like email's (and less likely, but still likely, newsletter names). The fact that this isn't in either option presented tells me that you both need to buy a SQL book that describes Database Normalization and read it from beginning to end.

Answer 10

Hopefully this doesn't sound too harsh, but neither solution is very scalable or performant. Unlike a lot of other answers I actually prefer your solution over his for what its worth! This is going to be totally dependent on your specific needs, but if you only care if the user has received the LATEST newsletter, you could give a unique auto incrementing id to each newsletter and keep a number per email address specifying the last email they received. You'd then be able to do an integer comparison instead of a string search of a potentially huge string. If you don't only care about the latest newsletter, you might have a look at bloom filters. A bloom filter is a probabilistic set which means you can ask it if something is in a set (has this user received this newsletter?). The interesting part here though is that since it is probabilistic it will tell you "no" or "maybe". No is never a lie, but maybe is uncertain. You could use one of these to ensure that no users got the same news letter twice, at the cost of maybe not all users getting the newsletter in the first place. The bloom filter could also just be used as a "fast query" where if it replies maybe, then you do the slower string check. Hope this gives you some alternate thoughts on solving the problem!