blocks|key|1137748|text|这并不是一个不寻常的设置，你有什么特别的理由相信它的效率会很低吗？|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1137749|客户端应该有一个集群主键id_client+Affiliate应该有一个集群主键id_client+%2B+id_affiliate|1137750|两者之间的连接是一个简单的b-tree查找，没有用于页面查找的额外跳跃(因为聚集键)，并且给定客户端的所有分支机构可能都在同一数据页面上，因此查找将非常快。|1137751|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

This is not an unusual set up at all, do you have any specific cause to believe it will be inefficient?

Client should have a clustered primary key of id_client
Affiliate should have a clustered primary key of id_client + id_affiliate

The join between the two is a simple b-tree lookup without an additional hop for page lookup (because of the clustering key), and all of the affiliates for a given client are likely to be on the same data page, so the lookup will be very fast.

blocks|key|5414167|text|我相信你误解了弱实体关系和强实体关系的复杂性。实际上，在设计表格时，这个概念几乎没有相关性。作为规范化过程的一部分，您需要关注的是两个表之间的基石关系。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|5414168|你的细节有点粗略，但如果你认为这将是低效的，因为你会有多个记录返回当你查询联属寻找客户id的时候，我不会这么认为。|5414169|如果这个关系是强制的，你将不会遭受任何低效，如果这是一个可选的关系，那么在关系数据库模型的上下文中，你的强实体关系和弱实体关系没有显著的区别。|5414170|事实上，在你的问题中，你回答了自己的问题，声明：|5414171|5414172|但我的问题是，在SQL中，因为分支的主键必须由它的鉴别器和父级的主键定义，所以一对多的关系将是低效的，例如，在分支中可能有一个id_affiliate与多个id_client相关。|blockquote|5414173|5414174|5414175|这不是问题，这就是它的工作方式。|5414176|entityMap^0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|R|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|S|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|T|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|U|8|@]|9|@]|A|$]]|$1|H|3|-4|5|6|7|V|8|@]|9|@]|A|$]]|$1|I|3|J|5|K|7|W|8|@]|9|@]|A|$]]|$1|L|3|-4|5|6|7|X|8|@]|9|@]|A|$]]|$1|M|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]|$1|N|3|O|5|6|7|Z|8|@]|9|@]|A|$]]|$1|P|3|-4|5|6|7|10|8|@]|9|@]|A|$]]]|Q|$]]

I believe you are mistaken the complexity of weak and strong entity relationship. In fact, in practice this concept has very little to no relevance when designing tables. What you need to focus on is the cornerstone relationship between the two tables as part of your normalization process.

Your details are a bit sketchy, but if you think that it will be inefficient because you will have multiple records returned when you query AFFILIATE looking for client id's, I would beg to differ.

If this relationship is mandatory, you will not suffer any inefficiencies, if this is an optional relationship then in the context of relational database model there is no significant difference in your strong vs weak entity relationship.

In fact, in your question, you answer your own question stating: 

<blockquote>
 But my problem is that in SQL, as the primary key of the AFFILIATE must be defined by its discriminator and the parent's primary key, the one to many relationship will be inefficient as for example it will be possible in AFFILIATE to have one id_affiliate related to many id_client.
</blockquote>

This is not a problem, this is how it works.

blocks|key|5360132|text|@John+Wu和@Hituptony，非常感谢你们的帮助，我的问题现在已经解决了。事实上，我不太理解“弱实体集”的概念，即我们可以有具有相同判别器的弱实体，但表示不同的对象，因为它们与不同的强实体相关。例如，我们可以有相同的affiliate_id，但代表不同客户的不同分支。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|5360133|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

@John Wu and @Hituptony, thank you a lot for helping and the problem i had is now resolved. In fact, i did not understand very well the concept of "Weak Entity set" where we can have weak entities with same discriminator but representing different objects as there are related to different strong entities. For example, we can have same affiliate_id's but representing different affiliates of different clients.

I have a big challenge that i am facing right now.
I am designing a database when i have a strong entity "CLIENT" with a weak entity "AFFILIATE" which has the primary key of itself combined with the parent's primary key.The identifying relationship between the two must be a "ONE-TO-MANY" relationship and have the total participation. 
But my problem is that in SQL, as the primary key of the AFFILIATE must be defined by its discriminator and the parent's primary key, the one to many relationship will be inefficient as for example it will be possible in AFFILIATE to have one id_affiliate related to many id_client. Please help me.

one-to-many identifying relationship in sql

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我现在面临着一个很大的挑战。当我有一个强实体"CLIENT“和一个弱实体"AFFILIATE”时，我正在设计一个数据库，该实体本身的主键与父实体的主key.The相结合，确定两者之间的关系必须是“一对多”关系，并具有完全的参与性。但我的问题是，在SQL中，因为分支的主键必须由它的鉴别器和父级的主键来定义，所以一对多的关...

问sql中的一对多标识关系
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问sql中的一对多标识关系EN