SQL多对多组合表 [英] SQL Many-to-many Combination Table

问题描述

我的数据库设计有一个有趣的问题，解决方案还不能令人满意。

我有一个站以下字段：

- key_station（主键）

- 站名_
- 信息

然后我有一个职位的表格：

- key_position（主键）

- position_number

- info _
- key_station（站的外键）

和设置表：

- key_setup（主键）

- setup_number

- info

- key_station（站的外键）

现在，Positions表列出了一个电台的所有插槽号，Setups表列出了电台的所有可能的不同设置。我需要的是根据设置在每个位置有不同的部分的表。有些职位可能是空的。例如...

设置1

Position1 - AA

职位2 - BB¥$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ / / / / / / / / / / / / / / / / / / / / / / / / >
Position1 - NULL

Position2 - CC

Position3 - NULL

所以我创建了一个表名称设置，其中包含以下字段：
- key_setting（主键）

- key_position（foreign key）

- key_setup（Foreign key）

- part

这里的问题是我必须确保设置始终具有引用表的组合。所以当我在示例中插入一个新的位置时，它必须在设置中设置两个记录（每个设置一个），或者当我插入一个新的设置时，它必须在设置（每个位置一个）中插入3个记录，用户可以稍后填写。为什么？因为每个设置中的位置都不会改变，只能分配它们的分配。

我目前所做的是在两个表位置和设置（或设置）中插入和删除和设置）与两个SQL语句。不幸的是，由于我没有处理我的操作的原子性，所以在表格设置中没有相应的记录，我已经插入了位置或设置的问题。

所以我想知道什么是最优雅和最正确的解决方案，其中中间表必须具有该关系的所有组合。我需要什么样的规范化？或者可以在SQL Server中添加什么样的约束？这个问题是否有特定的名称，以便我可以搜索更多？

解决方案

• 配置 FKs with ON UPADE CASCADE ON DELETE CASCADE




• 插入新的 StationSlot后或 StationSetup 运行此

---

 插入到StationConfiguration（StationID，StationSlotNo，StationSetupNo）
选择
 a.StationID 
，b。 StationSlotNo 
，a.StationSetupNo 
从StationSetup作为
加入StationSlot作为b在b.StationID = a.StationID 
其中不存在（
选择1 
从StationConfiguration为xx 
其中xx.StationID = a.StationID 
和xx.StationSlotNo = b.StationSlotNo 
和xx.StationSetupNo = a.StationSetupNo 
）
 ; 
  

---

• 新组合与 Part 为NULL。

I've had an interesting problem with a database design whose solution is not yet satisfactory.

I have a table of Stations with the following fields:
- key_station (Primary key)
- station_name
- info

Then I have a table of Positions:
- key_position (Primary key)
- position_number
- info
- key_station (Foreign key to Stations)

And table of Setups:
- key_setup (Primary key)
- setup_number
- info
- key_station (Foreign key to Stations)

Now, the Positions table lists all the slot numbers of a station, and the Setups table lists all the possible different setups of a station. What I need is a table that has a different part in each position depending on the setup. Some positions may be empty. For example...

Setup 1
Position1 - AA
Position2 - BB
Position3 - NULL

Setup 2
Position1 - NULL
Position2 - CC
Position3 - NULL

So I created a table name Settings, with these fields:
- key_setting (Primary key)
- key_position (Foreign key)
- key_setup (Foreign key)
- part

The problem here is that I have to ensure that Settings always has the combinations of the referenced tables. So when I insert a new position in the example, it has to insert 2 records in Settings (one per setup), or when I insert a new setup, it has to insert 3 records in Settings (one per position) with NULL parts that the user can fill later. Why? because the positions don't change in each setup, only their assigned parts.

What I currently do is that I insert and delete in both table Positions and Settings (or Setups and Settings) with two SQL statements. Unfortunately I've had issues of inserted positions or setups without their corresponding records in table Settings because I didn't take care of the atomicity of my operations.

So I want to know what is the most elegant and correct solution to a many-to-many relationship in which the intermediary table must have all the combinations of that relationship. What kind of normalization do I need? Or what kind of constraint can I add in SQL Server? Does this problem have a specific name so I can search more?

解决方案

• Configure FKs with ON UPADE CASCADE ON DELETE CASCADE

• After inserting new StationSlot or StationSetup run this

---

insert into StationConfiguration (StationID, StationSlotNo, StationSetupNo)
select
      a.StationID
    , b.StationSlotNo
    , a.StationSetupNo
from StationSetup as a
join StationSlot  as b on b.StationID = a.StationID
where not exists (
     select 1 
     from StationConfiguration as xx
     where xx.StationID      = a.StationID
       and xx.StationSlotNo  = b.StationSlotNo
       and xx.StationSetupNo = a.StationSetupNo
     )  
;   

---

• This inserts any new combination with Part being NULL.

这篇关于SQL多对多组合表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！