Mysql – Suitable schema to record email sent and response received and invoicing in DB

database-designMySQL

When a form submission is made on a website:

  1. An email with form's details is sent to a set of recipients email addresses stored in a DB.

  2. The email contains a link for the recipient to show interest. When the link is clicked a response is sent to server with verify_code. The server will UPDATE the table below setting has_confirmed to true or 1 to record their interest on DB.

  3. An email is sent back to the recipient as acknowledgement

This is the current table to record the above:

RequestSentConfirmation

Sent_ID (int) PK
Request_Id (int) FK
Recipient_Id (int) FK
verfied_code (varchar(100))
has_confirmed (bool)
when_confirmed (datetime)

Request_Id is ID to form submission details stored in another table.
Recipient_Id is ID to recipient email addresses stored in another table.

Is it better to store the has_confirmed in the same table as above.

The confirmations are required to collect and generate an invoice for each recipient.
To generate an invoice from above table I will need to run SQL script to check which sent emails are confirmed.

Or best to store the confirmations in separate table? That way I could run reporting script on the separate confirmation table to generate invoice for each recipient?

Is there a better way to design this with DB since I'm using emailing here.

Best Answer

Assuming the system handles only a few actions per second and only millions of ids, either flavor of schema design should work well enough.

I can think of no strong argument for, or against, splitting the confirmations off into a separate table -- performance, sparseness (or lack of) in the Confirmations table, 1:1 relationship, coding convenience, purging, etc.

If you anticipate hundreds of actions per second or billions of ids, then you should sketch out more details, including INSERTs, UPDATEs, and SELECTs. Plus consider a purging policy (which might include PARTITIONing by date).

You've had the foresight to think about the 'right' design -- Good. Suggest you plan on significant redesign after maybe a year of use. Build a layer between you application an MySQL. Then focus on the application without fretting over the schema design. With luck, the impact of the redesign will be mostly limited to the 'layer'.

For example, the layer would be asked to SendConfirmation(id); it would then either UPDATE (if one table) or INSERT (if 2 tables).

Related Question