V
V
Vadim2014-06-13 22:53:57
Database design
Vadim, 2014-06-13 22:53:57

Messaging and database implementation

There are many users. But I will give an example on one.
The user needs to send a letter, and preferably 100 letters.
These letters are already there, or can be created.
Letters are sent according to the schedule set by the user. (For example, every 17 minutes)
Now let's put it all together:
There are 100 emails in the queue. Schedule every 17 minutes. Everything is simple, FIFO and excellent.
But each letter can have its own parameters, for sending or deleting or something else.
For example:
There is a letter that should be sent at a certain time.
Another letter should go next.
The third letter, an hour after sending, should be deleted from the database.
The fourth letter has 2 actions at once, and deletion as in the 3rd, and at a certain time.
The fifth letter is special, after sending it, we no longer send letters for the next 5 hours.
Also, the user can create a letter and add it to the mailing list with various combinations of statuses.
For example:
Send an email instantly.
Send email to next( LIFO last in first out)
Simply added to the queue
Added to the queue, with priority(from 0 to 100)
Problem: o
There are a lot of different parameters that a letter can have. And I had a problem how to design a database for this case.
My thoughts on this:
We have 3 states.
When we send, What do we do after sending.
When we send:
- Instant - will be sent the next time the sender script is run.
- Next (LIFO) - becomes the first in the queue
- By time - additionally specify the time when we will have to send this particular letter.
- Queued - it is possible to specify the priority (0..100) By priority, you can change the order in which messages are sent.
- Immediately after the first one - After sending the next one, we immediately send
What we do after sending:
- Delete the letter in a day.
- We delete the letter July 18 at 15-00
- We delete the letter immediately after sending the next letter.
- Freeze sending for 1 day
Table "mails"
id
user_id
title - title
text - text
date - filled during sending
status - status sent / not sent
before_type - type of action that controls the order of sending
before_time - if necessary, then time.
delete_type - action type, what to do after.
delete_time - time.
freeze_type - freeze sending
freeze_time - Time
Actually, somehow it's hard to put everything in your head.
This mails table will be used for mailing. (ie, sending scripts will use this table)
Additionally, there is also a schedule table with a schedule set by the user.

Answer the question

In order to leave comments, you need to log in

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question