我应该让一个外键可以null或做一个新的表？ [英] Should I make a foreign key that can be null or make a new table?

问题描述

我有一个小问题，关于我应该如何设计我的数据库。我有一个表狗为动物收容所，我有一个表业主。在表中狗所有的狗，一次是在庇护所正在放。现在我想在表狗和表所有者之间建立关系。

问题是，在这个例子中，并不是所有的狗都有一个所有者，有一个以上的狗，一个可能的外键应该放在桌子狗（一只狗不能有一个以上的所有者，至少不在管理的避难所）。但如果我这样做，一些狗（收容所中的）将有null作为外键。阅读一些其他主题教我，这是允许的。 （或者我可能读了一些错误的主题）

然而，另一种可能性是在两个表之间放置一个表 - 例如'dogswithowners'

现在我的问题是（你可能已经猜到了）这两个最好的方法是什么？为什么？ / p>

解决方案

我同意Philip和Erwin说，最健全和最灵活的设计是创建一个新表。
$ b

基于null的方法的另一个问题是不同的软件产品不同意SQL的可空外键的工作方式。即使许多IT专业人员不能正确理解它们，因此一般用户甚至不太可能理解它。

I have a small question concerning with how I should design my database. I have a table dogs for an animal shelter and I have a table owners. In the table dogs all dogs that are and once were in the shelter are being put. Now I want to make a relation between the table dogs and the table owners.

The problem is, in this example not all dogs have an owner, and since an owner can have more than one dog, a possible foreign key should be put in the table dogs (a dog can't have more than one owner, at least not in the administration of the shelter). But if I do that, some dogs (the ones in the shelter) will have null as a foreign key. Reading some other topics taught me that that is allowed. (Or I might have read some wrong topics)

However, another possibility is putting a table in between the two tables - 'dogswithowners' for example - and put the primary key of both tables in there if a dog has an owner.

Now my question is (as you might have guessed) what the best method is of these two and why?

解决方案

I agree with Philip and Erwin that the soundest and most flexible design is to create a new table.

One further issue with the null-based approach is that different software products disagree over how SQL's nullable foreign keys work. Even many IT professionals don't understand them properly so the general user is even less likely to understand it.

这篇关于我应该让一个外键可以null或做一个新的表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！