Mysql – Designing MySQL Database For Two Different User Types (Staff and Client)

database-designMySQLrdbmsschemasubtypes

I'm creating a web-app that will connect to a MySQL database. This app will have 2 users: a Staff and Client that can both log in from the same login page.

Both Staff and Client share the following common attributes:

  • staffID/clientID
  • status
  • timestamp update
  • username
  • password
  • email
  • image profile
  • first/middle/last name
  • gender
  • date of birth
  • address
  • phone

But the Staff has the unique attributes:

  • position
  • license
  • badge
  • date hired

And the Client has the unique attribute:

  • company name

1.) Would it be advisable to put all the common attributes in one single Users table with a column user_type to determine whether it's a Staff or a Client that logs in or make two separate tables for Staff and Client that have the exact same attributes?

2.) Would it be advisable to further separate the following attributes:

  • gender
  • date of birth
  • address
  • phone

into another table since they will not be queried as much (these attributes are only queried when viewing users' profiles)? Will this help speed up query time for, say, log ins?

3.) Would it be ideal to separate the image profile attribute into one table Image_Files or keep it within the same table with all the other attributes?

As of now, I'm leaning on making the following tables with columns:

USERS table:

  • userID [PK] (formerly staffID/clientID)
  • status
  • timestamp update
  • username
  • password
  • email

USER_DETAIL table:

  • user_detailID [PK]
  • userID [FK]
  • gender
  • date of birth
  • address
  • phone

STAFF_DETAIL table:

  • staff_detailID [PK]
  • userID [FK]
  • position
  • license
  • badge
  • date hired

CLIENT_DETAIL table:

  • client_detailID [PK]
  • userID [FK]
  • company name

IMAGE_FILES table:

  • imageID [PK]
  • userID [FK]
  • image pathfile pointer

Is this database design unnecessarily complicated? If so, how may I streamline it to make it more simpler, faster to query, and yet still satisfy the unique attributes of each entities?

Best Answer

I think your problem is that you are focusing to much on performance. From the description it seems like the user table will be rather small (say, less than a million users).

Instead you should focus on the functionality you want from the system. There are questions you should ask and design for such as:

  • Can a user be both client and staff?
  • Can a user be user for several companies?
  • Can a user have several adresses, say a business adress and a home adress?
  • Do you need to have "time based changes", say that a client should be made active (or inactive) at a future date?
  • Do you need to store change information (who and when information was changed)?
  • Will you need to support several different login schemas (say through facebook or using some kind of one-time-password)?

My take on this is that I would have one table describing a login user and a second table describing a users role. When a user has successfylly logen in I would check if the users has several roles, and the user would be required to select role: staff or user for one company. This would allow the login to be for one physical person, over time it could evolve to include OTP or challenge response or what is deemed necessary. The user would at any time have zero, one or more selectable roles.

Edit (added): If the user has only one role, there is no need to show any selection. Even if a user has no role right now the system might allow changing adress, mail adress and so on. Role based might allow a user to be administrator for the users at one company. End edit.

In the role table I would have start and stop dates. If necessary from a performance view it would be possible to later add an "active" field but that would be an optimization which I would not add until necessary.

Related Question