数据库设计外键
我的数据库中的两个表如下：
[Employee] Table:
Id (Primary Key, Autoincrement)
FirstName
LastName
Status
[Status] Table:
Status (Primary Key)
Status 是以下之一： "FullTime" "Contractor" "Termied"
[Employee].Status 应如何引用 [Status].Status 作为外键？我看到有两种执行此操作的方法：
- [Employee].Status 直接指向 [Status].Status
- 我将 Id 列添加到 [Status] 表并使其成为 PK/自动增量。然后 [Employee].Status 指向 [Status].Id。这意味着我必须进行连接才能获取状态文本。
其他表也可以引用状态表。这两种方法中的一种是“正确”的做事方式，还是都是设计问题？
Two tables in my database are as follows:
[Employee] Table:
Id (Primary Key, Autoincrement)
FirstName
LastName
Status
[Status] Table:
Status (Primary Key)
Status is one of the following: "FullTime" "Contractor" "Terminated"
How should [Employee].Status reference [Status].Status as foreign key? I see two ways of doing this:
- [Employee].Status points directly to [Status].Status
- I add an Id column to [Status] table and make it PK/Autoincrement. Then [Employee].Status points to [Status].Id. This means I have to do a join in order to get the status text.
Other tables may also reference the Status table. Is one of the two methods the 'correct' way of doing things or are both a matter of design?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
这基本上是一个设计问题，但通常最好将 ID 字段添加到状态表中。这允许您更改状态值（拼写更正、语言翻译、更改术语以澄清含义等），而无需更新引用它的表中的数据。
另外，如果链接到字符串字段，则链接字段需要足够的空间来存储最长的状态字符串。链接到 ID 意味着您只需在链接字段中存储一个整数（或者最坏的情况是 GUID）。
It's basically a matter of design, but it's generally better to add an ID field to the Status table. This allows you to make changes to the Status values (spelling corrections, language translation, change of term to clarify meaning, etc) without having to update the data in the tables that reference it.
Also, if you link to the string field, then the linking field needs enough space to store the longest status string. Linking to the ID means you just have to store an integer (or at worst, a GUID) in the linking field.
在更复杂的表中，您想要删除记录和更新名称而不丢失引用的第三个选项是这样的
a third option in more complex tables where you want to both delete records and update names without losing reference would be something like