我应该创建一个可以为空的外键还是创建一个新表? [英] Should I make a foreign key that can be null or make a new table?

问题描述

我有一个关于如何设计数据库的小问题.我有一个动物收容所的餐桌狗，我有一个餐桌主人.所有曾经和曾经在收容所里的狗都被放在餐桌上.现在我想建立餐桌狗和餐桌主人之间的关系.

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.

问题是，在这个例子中，并不是所有的狗都有一个主人，而且由于一个主人可以有多个狗，所以应该在表 dogs 中放置一个可能的外键(一只狗不能有多个主人，至少不在庇护所的管理中).但是如果我这样做，一些狗(收容所里的狗)将 null 作为外键.阅读其他一些主题告诉我这是允许的.(或者我可能读错了题目)

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)

但是，另一种可能性是在两个表之间放置一个表 - 例如dogswithowners" - 如果狗有主人，则将两个表的主键放在其中.

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?

推荐答案

我同意 Philip 和 Erwin 的观点，最合理、最灵活的设计是创建一个新表.

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

基于空值的方法的另一个问题是不同的软件产品在 SQL 的可空外键的工作方式上存在分歧.甚至许多 IT 专业人士都无法正确理解它们，因此普通用户更不可能理解它.

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.

这篇关于我应该创建一个可以为空的外键还是创建一个新表?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！