用于级联下拉列表的设计数据库，不带循环引用 [英] Design database for cascading dropdown without circular reference

问题描述

在设计数据库以将数据存储在级联下拉列表后，我遇到了循环引用。我正在创建一个具有依赖于彼此的属性的对象，这些关系似乎不可避免地导致循环引用。



例如，我想在数据库中添加一个新的设备。该设备有类型和子类型。我选择类型，然后选择一个按该类型过滤的子类型。然后，新设备引用了Type和Sub-Type，它们也有关系。所以我最终得到一个三角形/圆形参考（我会包含一个图像，但我无法弄明白）。



在这种情况下，人们通常会有设备参考只有SubType？

那会解决这个实例，因为Sub-Types只有一个Type，但是如果它是Type和Sub-Type之间的多对多关系怎么办？

如果级联具有两个以上相互依赖的选择，该怎么办？ （可能有汽车制造，型号，装饰，发动机）



我尝试过的方法：



使用C＃和EF创建我的数据库我的（简化）对象如下所示：

I have run into a circular reference when designing a database to store the data behind cascading drop downs. I am creating an object with properties that depend on each other the relationships seem like they would inevitable result in a circular reference.

For example I want to add a new piece of equipment to the database. The equipment has a type and sub-type. I pick the type then pick a sub-type that is filtered by that type. The new equipment then has a reference to the Type and Sub-Type which also have a relationship. So I end up with a triangular/circular reference (I would include an image but I cannot figure that out).

In this situation do people normally have the Equipment references only the SubType?
That would solve this instance since the Sub-Types only have one Type, but what if it was a many-to-many relationship between Type and Sub-Type?
What if the cascade has more than two choices that depend on each other? (maybe with cars Make, Model, trim, engine)

What I have tried:

Using C# and EF to create my database my (simplified) objects look like:

public class Equipment
{
       [Key]
       public Guid EquipmentId { get; set; }
       public string Description{ get; set; }

       /***** Relationship and Navigation *****/
       public Guid EquipmentTypeId { get; set; }
       public EquipmentType EquiptmentType { get; set; }
       public Guid EquipmentSubTypeId { get; set; }
       public EquipmentSubType EquipmentSubType { get; set; }
}

public class EquipmentType
{
        [Key]
        public Guid EquipmentTypeId { get; set; }
        public string Description{ get; set; }

        /***** Relationship and Navigation *****/
        public ICollection Equipment { get; set; }
        public ICollection EquipmentSubType { get; set; }
}

public class EquipmentSubType
{
        [Key]
        public Guid EquipmentSubTypeId { get; set; }
        public string Description{ get; set; }

        /***** Relationship and Navigation *****/
        public Guid EquipmentTypeId { get; set; }
        public EquipmentType EquipmentType { get; set; }
        public ICollection Equipment { get; set; }
}

推荐答案

在这个具体的例子中，由于子类型总是有一个类型，一块设备总是有一个子类型，那么省略设备和类型之间的关系是有意义的。



如果子类型是可选的，或者有一个子类型和类型之间的多对多关系，那么你显然需要保持设备/类型关系。



如果您尝试在所有三种关系上启用级联删除，那么您将遇到的唯一问题。 SQL将拒绝创建外键约束，告诉您这将导致循环或多个级联路径。要解决此问题，您需要做的就是禁用设备/类型关系上的级联删除。



实体框架流畅的API - 关系 - 启用级联删除 [ ^ ]

In this specific example, since a sub-type always has a single type, and a piece of equipment always has a sub-type, then it would make sense to omit the relationship between equipment and type.

If the sub-type was optional, or there was a many-to-many relationship between sub-type and type, then you obviously need to keep the equipment/type relationship.

The only problem you'll run into is if you try to enable cascading delete on all three relationships. SQL will refuse to create the foreign key constraint, telling you that this would cause cycles or multiple cascade paths. To resolve this, all you need to do is disable cascading delete on the equipment/type relationship.

Entity Framework Fluent API - Relationships - Enabling Cascade Delete[^]

这篇关于用于级联下拉列表的设计数据库，不带循环引用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！