The requirements:

  1. User needs to confirm their email after signing up
  2. User needs to confirm their email after changing their email before this email is activated

My understanding is that the schema for this should look something like:

email_address (id, email_address)
user_account (id, email_address_id, activated_at)
email_address_authentication_token (id, email_address_id, user_account_id, token, used_at)

When user creates account, we create records in these tables:

email_address (1, '[email protected]')
user_account (1, 1, null)
email_address_authentication_token (1, 1, 1, 'XXX', null)

When user confirm their email address, then we activate user_account and mark token as used.

When user change their email address, then we create a new entry in email_address_authentication_token and when they confirm it, we update user_account_id email_address_id.

Does this seem logical?

  • Looks ok to me, but the question is, does it work with your use case requirements? Can 1 email address be used for more than 1 account? How would typical queries cope with that given your model. when a user logs in, do they use an acct no or email address? Sep 11, 2022 at 23:56
  • Only 1 active email address can be associated with the account. They use their email address when they attempt to login.
    – Gajus
    Sep 12, 2022 at 0:02
  • I understand an account can only have 1 email. But can many accts use the same email address? Sep 12, 2022 at 1:05
  • They cannot, no.
    – Gajus
    Sep 12, 2022 at 1:06


Your Answer

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

Browse other questions tagged or ask your own question.