Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

The problem: When we were sending newsletters to customers, there was no way to confirm if the customer already received the mail. So the boss decided to implement this idea:

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
}

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.

share|improve this question
10  
You should possibly revise the title of your other question to "I am worse than my boss, so do I ignore him"... ;-). –  Jon Hopkins Dec 20 '10 at 11:35
9  
Do you honestly believe your way is better? Because if so I would read very deeply into the answers below in an effort to regain your modesty. This is a very arrogant attitude, especially when you're wrong. –  Damien Roche Dec 20 '10 at 11:41
3  
Blindly concatenating something is always a dirty hack. If there are two clients, one's address is "me@something.co", and the other's address is "me@something.com", then how do you figure out whether you've sent a newsletter called "mail" to "me@something.co", or a newsletter called "ail" to "me@something.com"? –  Joonas Pulakka Dec 20 '10 at 12:12
14  
@mahen23 - you asked the question and the overwhelming response is that your boss is right. Let go of your solution, it's a total hack! Learn from this. Ask your boss more questions. Keep asking questions until his idea seems like a good one to you. Until you do, you'll just be hacking solutions through your career. –  Walter Dec 20 '10 at 13:34
4  
I was going to say Kudo's... then I read your reply about putting in both idea's. When everyone says your idea is bad, why would you still implement it? It performs an unneeded function that's already covered by the first solution... –  WernerCD Dec 20 '10 at 14:55
show 5 more comments

9 Answers

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.

share|improve this answer
13  
+1 for second paragraph! Maintenance is very very important –  Tech Jerk Dec 20 '10 at 10:50
add comment

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.

share|improve this answer
add comment

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)
share|improve this answer
    
If it's got the same title, I'd assume it was the same. ;) –  Macke Dec 20 '10 at 20:09
    
Point #1.3 (not self-documenting) is a very important consideration. –  Dean Harding Dec 20 '10 at 22:56
add comment

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.

share|improve this answer
add comment

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
share|improve this answer
    
+1 For pointing out the 'Success by Failure' design, which is both horrible and probably slow. –  nicodemus13 Jun 12 '12 at 14:26
add comment

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.

share|improve this answer
    
Didn't see this before I posted mine... +1 for suggesting to normalize the database –  WernerCD Dec 20 '10 at 15:09
add comment

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.

share|improve this answer
    
The boss has a better solution - after that we're into implementation details (-: –  Murph Dec 21 '10 at 8:23
add comment

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
share|improve this answer
1  
Are you joking about the boss thing because it's terrible advice. –  davidk01 Dec 21 '10 at 0:18
    
Yes, it's a well-known joke here in Austria. –  user281377 Dec 21 '10 at 6:44
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.