0

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?

  • Do define the faster to query criteria you need to look at what queries you are going to be doing on the dataset. How often is USERS considered the criteria in a query vs STAFF or CLIENT? – danblack Nov 6 '18 at 2:12
  • 1
    It seems like you can toss the detailIDs and promote the userIDs to be PRIMARY KEY. – Rick James Nov 6 '18 at 2:22
  • @RickJames That would accomplish the same thing as making everything into one table. And if I have 1,000 rows, queries might be affected due to the number of columns in the table. Also, some attributes are unique to Staff and Client. Hence, why I can't put them all into one big table. – Jae Bin Nov 6 '18 at 4:33
  • @danblack User data needs to be queried every login, browsing their profile pages, and making status reports (for Staff only). – Jae Bin Nov 6 '18 at 4:35
  • @JaeBin - 1000 rows is 'tiny', even with dozens of columns, many being NULL. The choice here is not clearcut -- One table vs several tables will make virtually no performance difference and not much difference in handling the data. – Rick James Nov 6 '18 at 4:58
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.