SQL多对多组合表

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

我有一个包含以下字段的工作站表: - key_station(主键) - station_name - info

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

然后我有一个位置表: - key_position(主键) - position_number - info - key_station(站点的外键)

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

和设置表: - key_setup(主键) - setup_number - info - key_station(站点的外键)

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...

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

Setup 1
Position1 - AA
Position2 - BB
Position3 - NULL

设置1位置1 - AA位置2 - BB位置3 - 空

Setup 2
Position1 - NULL
Position2 - CC
Position3 - NULL

设置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

所以我用这些字段创建了一个表名设置: - key_setting(主键) - key_position(外键) - key_setup(外键) - 部分

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.

这里的问题是我必须确保Settings始终具有引用表的组合。因此,当我在示例中插入新位置时,它必须在“设置”中插入2条记录(每个设置一条),或者当我插入新设置时,它必须在“设置”中插入3条记录(每个位置一条),其中包含NULL部分用户可以稍后填写。为什么?因为每个设置中的位置不会改变,只有它们分配的部分。

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.

我目前所做的是使用两个SQL语句在表位置和设置(或设置和设置)中插入和删除。不幸的是,我在表格设置中没有相应记录的插入位置或设置问题,因为我没有处理我的操作的原子性。

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?

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

2 个解决方案

#1

• Configure FKs with ON UPADE CASCADE ON DELETE CASCADE

  使用ON UPADE CASCADE ON DELETE CASCADE配置FK

• After inserting new StationSlot or StationSetup run this

  插入新的StationSlot或StationSetup后运行此命令

---

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.

这将插入任何与Part为NULL的新组合。

#2

In Setups table remove key_station and add key_position and remove Settings table altogether. That way you will know to which station a given row belongs by following the position reference and you will store the mappings between position and their values in that same table (Setups).

在“设置”表中,删除key_station并添加key_position并完全删除“设置”表。这样,您将通过跟随位置引用知道给定行属于哪个站,并且您将在同一个表(Setups)中存储位置及其值之间的映射。