Users and Staff in the same table or separate tabl

问题:

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.

回答1:

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.