标志列还是外键？
我有企业和域表。每个企业的属性是它应该有一个主域，但它可以有多个域。我想出了这个表结构
+---------------------------------------+
| ENTERPRISES |
+----+--------------+-------------------+
| ID | Name | Primary Domain ID |
+----+--------------+-------------------+
| 1 | Enterprise A | 2 |
| 2 | Enterprise B | 4 |
+----+--------------+-------------------+
+---------------------------------------+
| DOMAINS |
+----+------------------+---------------+
| ID | Domain Name | Enterprise ID |
+----+------------------+---------------+
| 1 | ent-a.com | 1 |
| 2 | enterprise-a.com | 1 |
| 3 | ent-b.com | 2 |
| 4 | enterprise-b.com | 2 |
+----+------------------+---------------+
我的同事建议了这种替代结构：
+-------------------+
| ENTERPRISES |
+----+--------------+
| ID | Name |
+----+--------------+
| 1 | Enterprise A |
| 2 | Enterprise B |
+----+--------------+
+----------------------------------------------------+
| DOMAINS |
+----+------------------+---------------+------------+
| ID | Domain Name | Enterprise ID | Is Primary |
+----+------------------+---------------+------------+
| 1 | ent-a.com | 1 | False |
| 2 | enterprise-a.com | 1 | True |
| 3 | ent-b.com | 2 | False |
| 4 | enterprise-b.com | 2 | True |
+----+------------------+---------------+------------+
我的问题是，哪一个更有效/正确？
另外，在第一个示例中，我应该使用 ID 作为主域列还是字符串值，以便 ENTERPRISES 表不具有对 DOMAINS 表的循环依赖关系？
I have ENTERPRISES and DOMAINS table. The property of each enterprise is that it should have a single primary domain, but it can have more than one domain. I have come up with this table structure
+---------------------------------------+
| ENTERPRISES |
+----+--------------+-------------------+
| ID | Name | Primary Domain ID |
+----+--------------+-------------------+
| 1 | Enterprise A | 2 |
| 2 | Enterprise B | 4 |
+----+--------------+-------------------+
+---------------------------------------+
| DOMAINS |
+----+------------------+---------------+
| ID | Domain Name | Enterprise ID |
+----+------------------+---------------+
| 1 | ent-a.com | 1 |
| 2 | enterprise-a.com | 1 |
| 3 | ent-b.com | 2 |
| 4 | enterprise-b.com | 2 |
+----+------------------+---------------+
My co-worker suggested this alternative structure:
+-------------------+
| ENTERPRISES |
+----+--------------+
| ID | Name |
+----+--------------+
| 1 | Enterprise A |
| 2 | Enterprise B |
+----+--------------+
+----------------------------------------------------+
| DOMAINS |
+----+------------------+---------------+------------+
| ID | Domain Name | Enterprise ID | Is Primary |
+----+------------------+---------------+------------+
| 1 | ent-a.com | 1 | False |
| 2 | enterprise-a.com | 1 | True |
| 3 | ent-b.com | 2 | False |
| 4 | enterprise-b.com | 2 | True |
+----+------------------+---------------+------------+
My question is, which one is more efficient/correct?
Also, in the first example should I use ID for primary domain column or a string value, so ENTERPRISES table does not have a circular dependency on DOMAINS table?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
两者都是正确。但还是去FK吧。
您建议的域具有较少的稀疏数据，而在第二个示例中，您可能有 100 个属于同一家公司的域，所有域的
IsPrimary设置为False 并且只有一个域设置为True。此外，在第一种情况下更容易强制执行一个主域，而在第二种情况下，您必须在代码中编写触发器或检查以查看是否有一个且只有一个, 始终是主域。
再次，坚持FK。
Both are correct. But go for the FK.
The one you suggest has less sparse data, while in the second example you may have 100 domains belonging to the same company, all with
IsPrimaryset toFalseand just one domain set toTrue.Also, it's easier to enforce exactly one primary domain in the first scenario, while in the second you'll have to write a trigger or a check in your code to see that there is one, and only one, primary domain at all times.
Again, stick to the FK.
循环引用是可以的。循环依赖则不然。只要
Primary Domain ID可为空，就可以了。否则，您将遇到先有鸡还是先有蛋的情况，无法在没有Enterprise的情况下创建Domain，而且也无法创建Enterprise< /code> 没有主域 ID。我会选择前者（您建议的解决方案），因为您正在定义一对一的关系。虽然
企业->域关系是一对多，但企业->主域关系是一对一。Circular references are OK. Circular dependencies are not. As long as
Primary Domain IDis nullable, then you're fine. Otherwise you'll have a chicken-or-the-egg scenario, being unable to create aDomainwithout anEnterprise, but also unable to create anEnterprisewithout aPrimary Domain ID.I would choose the former (your proposed solution), because you're defining a one-to-one relationship. While the
Enterprise->Domainrelationship is one-to-many, theEnterprise->Primary Domainrelationship is one-to-one.在第一个模型中，您说企业应该有一个主域。扩大一下，说它将有一个主域。此时，您倾向于将该列标记为不可为空。
那么问题是您将无法插入数据，因为您已经创建了循环依赖性。您无法插入没有域的企业，也无法插入没有企业的域。
我更喜欢第一个模型，因为它更干净、更明确。您的模型强制存在一个主域，而第二个模型中没有任何内容，因此您将被迫使用某种其他机制来强制执行此规则。
In the first model you say an Enterprise should have a single primary domain. Expand that a moment and say it will have a single primary domain. At this point you'd be inclined to mark that column as not nullable.
The problem then is you won't be able to insert data since you've created a circular depedancy. You can't insert an enterprise without a domain and you can't insert a domain without an enterprise.
I prefer the first model as it is cleaner and more explicit. Your model enforces that there is a single primary domain where there is nothing in the second model so you'd be forced to enforce this rule using some other mechanism.