0

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.

1
  • Why not to make uninitialized_accounts and migrate them into initialized_accounts instead of setting up the "activation date"? Mar 31, 2016 at 22:37

1 Answer 1

1

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).

3
  • Thanks very much Rick. Yes I believe starting with a good design is key. But then again fretting over the schema design too much will just prevent me to go forward with what I have. Feb 25, 2015 at 20:13
  • Yeah the DB will really only handle millions of IDs every 2 year perhaps. I think I will stick with recording the sent emails and confirmations in one table. So UPDATE the sent records table everytime a confirmation has come through. Until a confirmation has come through has_confirmed records will be 0, but is it ok to set when_confirmed to NULL? Feb 25, 2015 at 20:21
  • For a boolean (true/false) field, I prefer TINYINT UNSIGNED NOT NULL. If, instead, you stored the date & time of the confirmation, then I would go with DATETIME NULL or TIMESTAMP NULL, where NULL means "not yet confirmed". You have not yet asked a question for which I would say you must do it a certain way. You may find after a few months that a change in requirements forces you into a different decision (such as changing the flag to a TIMESTAMP). You are looking at 1 INSERT per minute -- extremely slow.
    – Rick James
    Feb 25, 2015 at 21:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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