EF 中使用的最佳自连接 SQL 架构
我有一个关于自连接的架构问题。
我有一个带有唯一 int 标识符的对象表。这些对象可以单独存在，也可以作为对象集合的一部分存在。该集合表示为同一个表中的对象，但其类型设置为集合。
例如，
1 | ObjectName | IsolatedObject
2 | CollectionName1 | CollectionObject
3 | CollectionName2 | CollectionObject
该对象以前可能属于 0 个或多个集合，因此我有另一个表来存储映射，
例如
2 | 1
3 | 1
已决定它们现在只能属于 1 个集合。我的问题是，是保留现有的额外表还是更改对象表以具有存储唯一 ID 的 ParentID 字段更好？
这也与 EF 一起使用，因此关系映射可能会变得复杂。
感谢您提前提供的任何帮助。
I've got an architechural question regarding a self-join.
I have a table of objects with an unique int identifier. The objects can exist in isolation or as part of a collection of objects. The collection is represented as an object in the same table but with it's type set as collection.
e.g
1 | ObjectName | IsolatedObject
2 | CollectionName1 | CollectionObject
3 | CollectionName2 | CollectionObject
The object previously could belong to 0 or more collections, therefore I had another table that stored the mappings
e.g.
2 | 1
3 | 1
The decision has been made that they now can only belong to 1 collection. My question is whether it is better to keep the existing extra table or alter the table of objects to have a ParentID field which stores the unique ID?
This is also use with EF therefore the relationship mapping can become complicated.
Thanks for any help in advance.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
如果树中有多个分组，则映射表是一个很好的“可扩展”解决方案，但如果只有一个分组“级别”，则映射表就太过分了。
IE：如果一个对象集合可以有一个主控，但不能成为其他任何东西的主控，那么一定要进行自连接。
但是，如果对象集合也可以成为大师，那么您当前的映射解决方案会更好。
原因是“你需要走树吗？”
要返回 SQL 中的所有关系，您需要为每个级别重新查询表（递归）。
如果只有独立对象可以成为主控，那么单个查询可以返回所有值
Your mapping table is a good "extensible" solution if there are multiple groupings in the tree but it is overkill if there is only one grouping "level".
IE: If an Object Collection can have a master and NOT be a master for anything else then by all means do a self join.
However if the Object Collection can also be a master, your current mapping solution is better.
The reason for this is "do you need to walk the tree?"
To return all the relationships in SQL you will need to requery the table for each and every level (recursive).
If only Isolated Objects can be Master then a single query can return all values
一般来说，在应用程序层中强制执行完整性约束（即保持数据结构不变）不会立即给您带来问题，但从长远来看可能会出现问题。在最终用户可以修改所有数据以适应新的策略/要求之前，保持数据结构不变可能是一个很好的中间解决方案，但我们在我的办公室有一句话：“如果可以将数据放在那里，它将被放在那里'；这意味着如果引用完整性允许，您可以打赌它会发生。
我的建议是，如果这是真正模拟需求的解决方案，请尽快使用 ParentID/自引用模型；当然，这必须与预算、时间、用户承诺等相平衡……
In general enforcing the integrity constraints in the application layer (aka leaving the data structure as-is) will not cause you problems immediately, but are likely to in the long run. Leaving the data structure as-is might be a good intermediate solution until all of the data can be modified by end users to fit the new policy/requirement, but we have a saying in my office that 'if data can be put in there, it will be put in there'; meaning that if the referential integrity allows it you can bet it will happen.
My advice would be to get to the ParentID/Self-referencing model as fast as you can if that is the solution that truly models the requirement; of course this must be balanced with budget, time, user commitment, etc...