用户和员工在同一个表中还是在单独的表中？ [英] Users and Staff in the same table or separate tables?

问题描述

我必须为我的应用程序决定数据库的设计。到目前为止，我有存储这些字段的Users表：

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

此表存储有关用户的所有信息。应用程序中还有一个名为Staff的部分。根据他们希望在表格上看到的规格

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

如您所见，工作人员的用户表中已有一些字段，例如名字，姓氏和电子邮件。最重要的是，工作人员记录不必是用户。换句话说，员工记录永远不会登录系统，并且他们不需要所有用户信息/字段，但是用户可能是员工。因此，我不确定该走哪条路。我应该为员工创建另一个表吗？这将导致一些冗余数据，因为来自员工的记录可能已经是Users表中的记录。同时，我想在用户表中保留没有登录信息的员工记录。具有两个单独的表将保持数据库整洁但多余。在这种情况下最好的方法是什么？另外，我还在考虑将来的效率和查询数据。如果有人可以提供一些建议或示例会有所帮助。

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.

推荐答案

或创建第三个表： Contact 包含一般的联系信息：

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.

这篇关于用户和员工在同一个表中还是在单独的表中？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！