Database schema: what to do with unverified prompts?
I'm not really sure how to approach this problem:
I am creating a web application that has a invite-only registration system. The admin user sends the user an email invitation, the user clicks the link and takes them to a page where they can create an account associated with their email address.
My initial idea was to insert a row into my table users
with a column verified
marked false
. The problem is I have a username and the password is required fields and the username must be unique. So I cannot just insert a blank line to be filled in later.
Should I create a separate table for invitations? What would be the best way to approach this scenario?
Update: Admin will enter first name, last name, email address and user role (permissions). So I will need to store all of these things in the prompt table. I could also store the sent date and update this value if the email ever needed to be re-sent.
source to share
Yes, you have to make a separate table for managing invitations.
Then, when the invitation is accepted, you have several options. First, you will need to decide if you need to keep in touch with the original invitation for a tracking or "family tree" or for some other historical purpose.
Then, if so, you need to decide how you do it. Delete the prompt and keep any relevant information with the user's record? Save the invitation entry and set the foreign key?
Perhaps I can give more subtle advice if I learn more about the system you intend to create.
source to share
You should consider scenarios where invitations are lost, deleted by accident or for a variety of other reasons, are unusable, and must be resubmitted.
In addition, the invitation is a separate entity from the registration. I think you should create a separate table to track invitations versus to see who signed up (from which invited)
source to share
Several ways to look at this. If you take a separate route where you create a different table, what happens when the assessment stage is complete, do you need to copy those rating users to your real users table? Usernames can be defaulted to a specially crafted GUID that you programmed to handle input to your users table.
source to share
A separate table makes the most sense to me. This will allow you to maintain the integrity of the data in the users table and provide a more readable data model. It is easier to say that "prompts go in the prompts table" than "prompts go in the users table, but with the checked column set to false".
source to share