0

I have to decide on design of my database for my application. So far I have Users table that stores these fields:

**USERS**
RecID - auto increment
UserID - unique id (Primary key)
UserName
Password
Salt
TempPassword
FirstName
LastName
Email
SystemAdmin
QuestionID
Answer
Active
CreateDate
CreateUID

This table stores all information about users. There is another section in the app named Staff. According to the specs they want to see on the form 

**STAFF**
First name 
Last name 
Middle Initial 
Position
Email 
Comments

As you can see Staff has some fields that already exist in Users table like First, Last name and Email. The most important thing is that Staff record doesn't have to be a user. In other words staff records will never log in the system and they do not need all users information/fields but users might be a staff. For that reason i'm not sure which way to go. Should I create another table for the Staff? This will cause some redundant data since record from the staff might already be the record in the Users table. At the same time I would like to keep Staff records that won't have login info in Users table. Having two separate tables will keep database clean but redundant. What would be the best approach for this situation? Also I'm thinking about efficiency and querying data in the future. If anyone can provide some advise or example that would help. Thanks in advance.

espresso_coffee
  • 5,980
  • 11
  • 83
  • 193

1 Answers1

3

Or create a third table: Contact that holds general contact information:

Contact
--------------
Id   <-- primary key - links to ContactId in linked tables
FirstName
LastName
Email
ContactTypeId -- perhaps to enable identification?
etc...

Then link to the contacts table from Staff and Users:

Staff
-----------
Id
ContactId <-- foriegn key
Position
etc...

Users
------------
Id
ContactId <-- foriegn key
Username
Password
etc....

Then you store contact information centrally in the same table and can link to any table that needs to store contact type data.

Tanner
  • 22,205
  • 9
  • 65
  • 83
  • I'm guessing that Primary Key will be stored in Contacts table? The same id will be foreign key in Users and Staff table, correct? – espresso_coffee Apr 06 '18 at 14:07
  • Yes, `Contact..Id` links to `Staff..ContactId` and `Users..ContactId`, I have updated the post to highlight this. – Tanner Apr 06 '18 at 14:15
  • Thank you. Regarding `ContactTypeId` should that specify if record is related to Users or Staff table or something else? I'm not sure that I understand the purpose. – espresso_coffee Apr 06 '18 at 14:20
  • I guess if I need staff records I would do something like `SELECT * FROM Contacts LEFT INNER JOIN Staff ON Id = ContactId` This will pull all the records from the staff and all matching id's from Contacts table, correct? Or maybe better option is to do `INNER JOIN`? That will pull only matching records from both tables. – espresso_coffee Apr 06 '18 at 14:28
  • 1
    Inner join would do the trick, as when you are querying staff you only want staff contacts, when users then you only want user contacts – Tanner Apr 06 '18 at 14:58
  • That make sense. The only doubt I have is Insert/Update query for Users and Staff table. This might not be that easy to decide when to insert First, Last name and other info stored in Contacts table. Should that be separate form or these fields should be optional on Users and Staff form? – espresso_coffee Apr 06 '18 at 15:02