两张表之间的外键双向数据库设计注意事项 [英] Database design consideration on foreign-key both way between two tables
问题描述
我有两个类,User
和Status
,虽然用户将具有许多状态,但这自然是Many-to-One
关系,并且可以轻松地映射到数据库表.
I have two class, User
and Status
, while a user will have a number of status, this is naturally Many-to-One
relation and can be easily mapped to DB Tables.
但是,该要求还需要用户保持其当前"状态,即在User
表中,它需要具有Status
表的外键.这样的结果是在两个表之间以相反的方向具有两个外键.
However, the requirement also need User to maintain its "current" status, i.e., in the User
Table, it need to have a foreign-key to the Status
table. The result of this is having two foreign-keys between two tables in the opposite direction.
一个明显的问题是,一旦将记录插入到两个表中,我就不能删除它们,因为从一个表中删除将违反另一个表的外键.
One obvious problem of this is once the records are inserted into the two tables, I can delete neither of them cause deleting from one table will violate the other table's foreign-key.
在这种情况下最好的设计是什么?
What is the best Design for this situation?
推荐答案
- 在状态"表中,添加一列,以确定该状态记录是否为当前". * * 对于性能问题,您只能将当前"状态记录设置为"1",其余设置为空值
- in your Status table , add a column that will determine whether this status record is "Current" or not. ** for performance issues , you can set only the "current" status records with '1' value and the rest with null value
- 您现在不必使用2个外键,只需一个-从状态到用户.
- 如果您使用冬眠作为帖子的标签:),则可以在数据库中创建一个视图,该视图将仅选择当前"状态记录,并具有与状态"表相同的结构.
- 使用一对一关系将视图连接到用户实体
希望它能对您有所帮助!
I hope it helped you !
这篇关于两张表之间的外键双向数据库设计注意事项的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!