Data model for entities with a relationship to both guest users and authenticated Users

Question

I am working on an application in which users can report security incidents.The idea is that everyone in an organisation has an account and can report an incident by logging in. Some of the users have more permissions than others. They can for example view incidents reported by others and close incidents. For this the following tables exist:

********************************
 users
********************************
 PK email        string
 FK organisation int (FK -> organisations)
    name         string
    password     string(salted)
********************************

********************************
 organisations
********************************
 PK id         int
    name       string
********************************

********************************
 incidents
********************************
 PK id          int
    name        string
    description string
    status      string
 FK uploaded_by int (FK -> Users)
 FK updated_by  int (FK -> Users)
********************************

Now, I am trying to design a data model such that visitors that do not have an account can still report incidents (for legal reasons they must report the incident themselves). Some of the visitors do not have an email address or computer (e.g. truck driver coming in). In these cases, an existing user, through his/her account, will submit an incident "as guest" after which the visitor can use the computer of the user to report the incident. The visitor must add his/her name and organisation as well when submitting the incident.

As of now, I see two options:

Option 1: Separate guest users table

Store guest users in a new table and add extra field to incidents (e.g. uploaded_by_guest). Serious drawback here is that many incidents will have a null reference and that in the application code one must always deal with the possibility that an incident was created by a guest or by an authenticated user.

Option 2: Change users table to include guests

Replace email PK by user_id, allow email field to be empty and add is_guest flag. Serious drawback is that the users/organisations table will be "cluttered". That is, the same organisation can be entered twice under a different ID because a guest user mistyped or typed the organisation name slightly different. With "normal" users this does not happen because these organisations are entered only by the system admin on account configuration. The same argument applies to the users table.

In conclusion, the options I see both have serious drawbacks. Therefore I was wondering if anyone has a better idea on how to model guest users and authenticated users?

Answer 1

Either option is applicable here, but my personal choice would be Option 2 just because I find it simpler. Also as you said, the drawback here applies to both options, meaning Option 1 has more drawbacks than Option 2.

Additionally, I'll mention that the drawback of cluttered data due to typos, etc, should be handled in the application layer by not allowing a free-entry text field then. Instead, the application should provide some sort of auto-complete or drop-down that the user chooses the Organisation from (populated from your Organisations table). If guests need the ability to enter a new one that's not in the list, then there should be a special option in the drop-down such as -Other- so that way they're forced to at least pick from the list first.

That is the minimal data integrity handling the application should employ. Obviously you can add as much additional logic, such as fuzzy matching on newly entered Organisations as well, before they're actually saved to the database, etc.