Should I store Users and Staff in the same table?

Question

I'm working on database design for my new application that has people with two different roles in the system. Those two groups are User and Staff. The main difference is that Users have activeUser,username,password,security-question and answer and Staff has activeStaff and position. Both of these also share next fields that are required for users and staff like FirstName, LastName, Middle and Email. Originally I thought that this will be very simple and I will add the flag accountType with option User or Staff. Here is example of my interface:

$('#frm_type').on('change',displayForm);
function displayForm() {
    var fldType = $(this).find(':selected').data('type');
    
    if(fldType === 'isUser') {
        $('.isUser').show();
        $('.isStaff').hide();
    }

    if(fldType === 'isStaff') {
        $('.isStaff').show();
        $('.isUser').hide();
    }

    if(!fldType) {
        $('.isUser').hide();
        $('.isStaff').hide();
    }
}

#main-container {
 padding-top: 20px;
}
.isUser {
 display: none;
}
.isStaff {
 display: none;
}

<!DOCTYPE html>
<html lang="en">

<head>
  <title>My Application</title>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <script language="javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
  <script language="javascript" src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
</head>

<body>
  <div id="main-container" class="container">
      <form name="frmSaveuser" id="frmSaveuser" class="frm-agencySubmit" data-frm="SaveUser" autocomplete="off">
        <div class="form-group required">
          <input type="text" class="form-control" name="frm_firstname" id="frm_firstname" placeholder="Enter First Name" maxlength="50" required>
        </div>
        <div class="form-group required">
          <input type="text" class="form-control" name="frm_lastname" id="frm_lastname" placeholder="Enter Last Name" maxlength="50" required>
        </div>
        <div class="form-group">
          <input type="text" class="form-control" name="frm_lastname" id="frm_lastname" placeholder="Enter Last Name" maxlength="1">
        </div>
        <div class="form-group required">
          <input type="email" class="form-control" name="frm_email" id="frm_email" placeholder="Enter email" maxlength="80" required>
        </div>
        <div class="form-group required">
          <select class="form-control" name="frm_type" id="frm_type" required>
              <option value="">-- Select Account Type --</option>
              <option value="1" data-type="isUser">User</option>
              <option value="2" data-type="isStaff">Staff</option>
          </select>
        </div>
        <div class="isUser">
          <div class="form-group required">
            <select class="form-control" name="frm_activeuser" id="frm_activeuser" required>
               <option value="">-- Active User --</option>
               <option value="0">No</option>
               <option value="1">Yes</option>
            </select>
          </div>
          <div class="form-group required">
            <select class="form-control" name="frm_systemadmin" id="frm_systemadmin" required>
               <option value="">-- System Admin --</option>
               <option value="0">No</option>
               <option value="1">Yes</option>
            </select>
          </div>
          <div class="form-group required">
            <input type="text" class="form-control" name="frm_username" id="frm_username" placeholder="Enter UserName" maxlength="50" required>
          </div>
          <div class="form-group password-container">
            <input type="password" class="form-control" name="frm_password" id="frm_password" placeholder="Enter Password" maxlength="50" required>
          </div>
          <div class="form-group">
            <select class="form-control" name="frm_questionid" id="frm_questionid">
                <option value="">-- Select the question --</option>
                <option value="1">What was the name of your first pet?</option>
                <option value="2">In what city were you born?</option>
                <option value="3">What is your mother's maiden name?</option>
                <option value="4">What is your favorite vacation spot?</option>
                <option value="5">What is your favorite musical group?</option>
                <option value="6">Where did you meet your spouse?</option>
                <option value="7">What model was your first car?</option>
             </select>
          </div>
          <div class="form-group">
            <input type="text" class="form-control" name="frm_answer" id="frm_answer" placeholder="Enter Answer" maxlength="100">
          </div>
        </div>
        <div class="isStaff">
          <div class="form-group required">
            <select class="form-control" name="frm_activestaff" id="frm_activestaff" required>
                <option value="">-- Active Staff --</option>
                <option value="0">No</option>
                <option value="1">Yes</option>
            </select>
          </div>
          <div class="form-group required">
            <input type="text" class="form-control" name="frm_position" id="frm_position" placeholder="Choose Position" required>
          </div>
        </div>
        <button type="submit" name="frm_submit" id="frm_submit" class="btn btn-primary">Submit</button>
      </form>
  </div>
</body>

</html>

Problem with the concept above is next: let's say We create account user but that account has to be staff as well or other way around staff that has to be user as well. In that case I would need third option for Account Type that would be Both (User & Staff). This would make querying data more difficult because we would have to include always at least two options. I'm wondering if there is better way to handle this? Also we have to consider switching between accounts for example someone with User account will become Staff or Staff become User. This has to be available and I'm looking for efficient and long term solution. So far I have only one table in my database that stores information for Users and Staff.

Why not two fields User and Staff where one or both can be set? — stark, Apr 19 '18 at 20:54
@stark You mean column for each Staff and User that would indicate isStaff or isuSER? — espresso_coffee, Apr 19 '18 at 20:57
That would work. Then you could add a WHERE for either or leave it out for both. — stark, Apr 19 '18 at 21:08
It is 2018 and you're storing passwords in the database. I do hope you're storing them properly encrypted, and NOT plaintext or MD5 hashed. Where properly encrypted means one of, *In order of preference, use scrypt, argon2, bcrypt, and then if nothing else is available PBKDF2.* (See http://latacora.singles/2018/04/03/cryptographic-right-answers.html for where I got that list from.) — btilly, Apr 19 '18 at 21:14
@btilly Please can you explain what is wrong with storing passwords in database? I already heard few people with the same comment so I'm wondering if I'm doing something wrong. I have all passwords hashed and salted. — espresso_coffee, Apr 20 '18 at 01:59
@espresso_coffee You should assume that some day your system will be breached and your database will be downloaded. When that day comes, what information are you leaking? If you're leaking passwords, those passwords are likely used on other sites by the same users, and you're therefore compromising them multiple places. Hence no plaintext. And also nothing that is too easy to brute force with a dictionary attack (like MD5). — btilly, Apr 20 '18 at 16:25

score 3 · Answer 1 · edited Jan 05 '22 at 10:05

3

You really should not combine users and staff in the same table. What you might do is create a user record for each person on staff, so there is a user_id in the users table.

Why not? The two groups likely have very different requirements, connecting them to different tables. For instance, staff could be part of a hierarchy. Or they might have a schedule of availability. Or they might skills for matching to. Or employment information if they are employees.

If PII is an issue, there are definitely different requirements for maintaining PII about staff versus users.

That said, all staff might also be users. I would keep the two concepts different, with a user_id linking staff to user data.

edited Jan 05 '22 at 10:05

Nimantha

6,405
6
28
69

answered Apr 19 '18 at 21:15

Gordon Linoff

1,242,037
58
646
786

In this case how we would handle staff no longer being staff and becoming a user or other way around. I only can picture that with having 3 different tables actually. Please correct me if I'm wrong. – espresso_coffee Apr 20 '18 at 02:06
A staff who is no longer on staff would presumably have columns that specify the last date or current status. You can check if a user corresponds to active staff by comparing fields in the two tables. – Gordon Linoff Apr 20 '18 at 02:13
The other problem with that solution is duplicating First, Last name, email, Middle name... Like I said this would be an option but only involving 3 different tables . Person table to store first last name, email, middle and then user and staff table. – espresso_coffee Apr 20 '18 at 02:15
@espresso_coffee . . . I don't see the duplication of names as a problem. The names that someone uses on "staff" and as a "user" would not have to be the same. But, if that is important, you can implement a trigger or constraint to guarantee that they are the same. – Gordon Linoff Apr 20 '18 at 02:20
So if you have User that is a Staff member at the same time you would enter First, last name, middle, email twice? In users and staff table? I think that is unnecessary and not recommended. I'm looking for more dynamic solution that would make switch from User to Staff easy and at the same time give an option for the same person to be user and staff at the same time. – espresso_coffee Apr 20 '18 at 02:23

score 3 · Answer 2 · answered Apr 20 '18 at 09:09

3

I'd go for three tables:

Person
User
Staff

Person contains data applying to any person, like name elements and email, regardless of the person being a user or staff member or whatever else. Staff and User contain the specific fields describing their properties in this role and refer to the Person via secondary key.

If you like, you can create a CompleteUser view to combine User and Person fields, so you can access them as if it were one table (and the same for Staff, of course).

answered Apr 20 '18 at 09:09

Ralf Kleberhoff

6,990
1
13
7

Can you provide the example of the view for this purpose or will be the same like any other view? One more thing regarding this solution, there always gonna be at least need of updating/deleting records . In this case that has to affect two tables. How to achieve that? – espresso_coffee Apr 20 '18 at 11:50

Should I store Users and Staff in the same table?

2 Answers2