Database schema design: What to do about unverified invitations?

Question

I'm not quite sure how to approach this issue:

I am creating a web application that has an invite only registration system. An admin user sends an email invitation to a user, the user clicks the link, and takes them to a page where they can create an account that has been linked to their email address.

My initial idea was to insert a row into my users table, with the verified column marked false. The problem is I have username, and password as required fields and username must be unique. So I can't just insert an empty row to be filled out later.

Should I create a separate table for the invitations? What would be the best way to approach this type of scenario?

Update: The admin will enter the first name, last name, email address, and user role (permissions). So I will need to store all these things in the invitations table. I could also store date sent and update that value if the email ever needed to be re-sent.

Answer 1

You have to consider scenarios where invitations get lost, get deleted by accident, or for a host of other reasons, are not usable and have to be re-sent.

Also, an invitation is a separate entity from a registration. I think you should create a separate table to track the invites vs. to see who registered (from which invite)

Answer 2

Couple of ways to look at this. If you go the separate route where you create another table what happens when the evaluation phase is over, do you have to copy those evaluation users over to your real user table? User names can be defaulted to a specially crafted GUID that you've programmed to handle entry into your users table.

Answer 3

I would recommend a separate table for invitations. It seems pretty clear to me that your invitation is basically a token allowing someone to register.

Answer 4

A separate table makes the most sense to me. That would allow you to maintain data integrity in the users table, and would provide a more readable data model. It is easier to say "invitations go in the invitations table" than "invitations go in the users table but with the verified column set to false".

Answer 5

Yes, you would make a separate table to manage invitations.

Then when an invitation is accepted, you have a few choices. First you'll want to decide if you need to maintain an association to the original invitation either for tracking or "family tree" or some other historical purposes.

Next, if so, you'll need to decide how you'll do that. Delete the invitation and store any relevant info w/the user record? Keep the invitation record and set a foreign key?

I might be able to give some more fine-tuned advice if I know more about the system you intend to create.