2向关系（数据库设计缺陷） [英] A 2-way relationship (a database design flaw)

问题描述

我有两个表，它们通过一对多关系相互连接。
的问题是被引用的表还具有第二个表的外键！

I've two tables which are connected to each other through a one to many relation. the problem is that the referenced table also has a foreign key of the second table!

我知道这很令人困惑，因此，我将向您展示一个简单的设计我的数据库：

I know it's confusing, So I'll show you a simple design of my database:

作者

AuthorId      Name       DefaultBookId (FK)
--------      -------    -------------
1             John       1
2             Mike       3
3             Mig        5

书籍

BookId        Title            AuthorId (FK)
--------      -------          -------------
1             TitleInfo1       1
2             TitleInfo2       3
3             TitleInfo3       2
4             TitleInfo4       1
5             TitleInfo5       3
6             TitleInfo6       3
7             TitleInfo7       1

---

当然我必须在books表中制作 AuthorId 外键，这是正常情况，但是现在我需要为每位作者设置一本默认书，因此我将在新列（DefaultBookId）上定义一个外键来指定每个作者的默认书。

---

Of course I have to make the AuthorId in the books table a foreign key and that's the normal case, but now I need to set a default book for every author so I'll define a foreign key on a new column (DefaultBookId) to specify the default book for each writer.

现在，两个表都相互依赖，因此除非删除它们之间的关系，否则我不能删除任何项目。

Now, both tables are dependent on each other so I can't delete any item unless I remove the relation between them and it doesn't feel the right thing to do!

任何关于此设计是否存在缺陷的想法，我该怎么办？

Any ideas on whether this design flawed or not and what can I do about it ?

推荐答案

我将使用连接其他两个表的第3个表来实现这一点。您最终会得到：

I would implement this with a 3rd table that joins the other two. You'd end up with:

作者

AuthorId      Name   
--------      -------
1             John   
2             Mike   
3             Mig    

DefaultBook

AuthorId      DefaultBookId (FK)
--------      -------------
1             1
2             3
3             5

书籍

BookId        Title            AuthorId (FK)
--------      -------          -------------
1             TitleInfo1       1
2             TitleInfo2       3
3             TitleInfo3       2
4             TitleInfo4       1
5             TitleInfo5       3

通过在 DefaultBook.AuthorId 上放置唯一约束，可以防止每个作者具有多个默认值。如果您需要删除某个作者，只需删除其默认值以及与他相关的任何书籍，就可以删除该作者。

By putting a UNIQUE constraing on DefaultBook.AuthorId you can prevent each author from having more than one default. If you need to delete an author, simply delete his default and any books associated with him, then you can delete the author.

这个问题是很难强制每个作者都有一本默认书，但这首先导致您遇到此问题。

The one problem this has is that it's hard to enforce that each author has a default book, but that requirement is what led you to this problem in the first place.

这篇关于2向关系（数据库设计缺陷）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！