Generate a unique access key for account setup with SQL

user_5272740 0
Viewed 77

I'm trying to come up with an efficient way to generate/validate unique access keys for an application I'm writing. The issue I'm having is because of how the SQL database is set up.

Basically, there are two tables in question. A Users table and a Faculty table. There is a one to many relationship between the Users and Faculty tables. Every user will create an account using just a personal email and password. That will create an entry in the Users table.

Now, I want to allow admins to enter a domain email and name, and generate a setup key. The user will then be able to enter that access key which will link their row in the Users table to a row in the Faculty table with a user_id foreign key.

The way I've thought about doing it is that when an admin generates the setup key it will create a row in the faculty table with a null foreign key. The user would then have to enter the setup key AND the domain email that the admin entered. Assuming it all matched, it would then add their user_id to that foreign key column.

Here's where the issues/questions lie. Would it be more efficient to use a separate table containing the setup keys and emails as sort of a lookup table and only searching a table with two columns before adding a row to the faculty table? I think this may be faster as rows could be deleted once they were validated leaving fewer rows to search every time. Also, I'm not sure if there is an issue with leaving the foreign key null as in my first method.

Assuming a unique key/email pair, would that be secure enough, or would I also need some way to check against the signed in user?

Any resources or ideas are greatly appreciated.

unique-key sql-server sql
1 Answers

I doubt there would be large benefit in performance if you create a separate table for this purpose, but it would have a large benefit on data integrity. Creating a new faculty record for every allowed access would cause redundancy, since you will have to add the same faculty in multiple records and would create large problems on the long-run, being ever more difficult to handle all the redundancies, ultimately leading to inconsistencies. So, the initial approach would violate the concept of database normalization. You will have much better results if you have a table for users, a table for faculties and another table for users_of_faculties. This would allow users to have access to more faculties if that's needed and each user, each faculty would be created at exactly one place, their relation would be more frequently changed, when access is granted, modified or revoked for some users at some faculties.

user_4365600
Related