存储要在数据库中执行的操作,还是从代码中决定?

I've got a website in which I want to send some followup emails to customers a certain number of days after they bought something. I now wonder how to do that. I think there are two options:

  1. Create a table in my DB in which I store the emails which I plan to send. So I simply store the customer email address and the date on which I want to send it. I then simply run a cron every day and send the emails that need to be sent and set the status in the table to "sent". The advantage of this method is that I know which emails need to be sent. The disadvantage is that I'm less flexible; it's not easy to change the number of days after which I send the emails because they are stored in the DB.
  2. I can also do it from the code by simply running a cron that gets the list of customers who bought something an x number of days ago, send them the email, and only then store the fact that I sent them an email in the database. The advantage of this method is that I'm more flexible. If I want to send out the emails later I can simply define that in the code (or some var). The disadvantage is that I don't have a list of emails which are going to be sent (although I don't really know what that would be useful for).

My question is actually; what is best practice in this case? How do most websites handle this and why?

I would choose method 2.

The disadvantage is not really a disavantage. Supposing you got an "order" table, you can get the list of mail to send just making a query quite similar the one used by your cron.

But it is a personal choice. I don't know which method is normally used.

I would go on a combination of both options, and that is the method I actually doing so in a system I'm currently developing.

Having a "ready to send" list is useful for logging and tracking your emails, for example, if you use a third party emailing solution, and you have a limited number of emails per month, you can track the amount you used from within your program, and maybe even trigger an automatic "upgrade" of the account if required because you need more emails.

The required flexibility can be achieved by designing a good schema for that table.

The solution you described will have a schema like so I guess:

|---------|---------|------|---------|-------|
| send_to | subject | body | send_at | sent  |
|---------|---------|------|---------|-------|

That is really not flexible, because once inserted into the database, in order to change the send_at column you will have to retrieve data from orders, and recalculate the send_at value.

I propose a schema like so:

|---------|---------|------|-----------|---------|-------|
| send_to | subject | body | added_at  | send_in | sent  |
|---------|---------|------|-----------|---------|-------|

The change is that the send_at column is not fixed now. When you run the cron, you will retrieve only emails that match the following query:

!sent && added_at + send_in >= now

This will return the same result as querying the first schema using:

!sent && send_at >= now

But now, you can easily change the duration of the waiting between the time that the email is added to the queue and the time that it actually will be send.