当字段之间存在不规则关系时，如何设计表

I would like to design the following relation in a table:

我想在表格中设计如下关系:

PARTNO  OEMNO
--------------    
AA1001  12345
AA1001  67890
AA1002  45678
BB1001  12345
BB1002  12345

There is an irregular relation between PARTNO and OEMNO.

零件与零件之间存在不规则的关系。

PARTNO (AA1001) may have more than one OEMNO (12345, 67890)
PARTNO (AA1001)可能有多个OEMNO (12345, 67890)
REPLACEMENT PARTNO (AA1002) of PARTNO (AA1001) may have different OEMNO (45678, 12345 and 67890)
部分(AA1001)的替换零件(AA1002)可能有不同的OEMNO(45678、12345和67890)
REPLACEMENT PARTNO (BB1002) of PARTNO (BB1001) may have same OEMNO (12345)
零件(BB1001)的替换零件(BB1002)可能有相同的OEMNO (12345)
OEMNO (12345) may have more than one PARTNO (AA1001, BB1001, BB1002)
OEMNO(12345)可能有多个PARTNO (AA1001, BB1001, BB1002)

Question: I am really confused how to create tables and relationships between them so that I can select

问:我很困惑如何在它们之间创建表和关系，以便我可以选择

all OEMNO for specific PARTNO and
所有的OEMNO用于特定的PARTNO和
all PARTNO for specific OEMNO
所有的零件都是特定的

2 个解决方案

#1

Whenever you have a many-to-many relationship you will need an intermediate table to decompose the relationship. This kind of table has many different names: e.g. "association", "bridging", "junction", "linking". Something like this:

无论何时拥有多对多关系，都需要一个中间表来分解关系。这种桌子有许多不同的名字。“协会”、“桥接”、“结”,“链接”。是这样的:

create table parts_oems (
partno varchar (10) not null,
oemno varchar (10) not null,
constraint pk_parts_oems primary key (partno, oemno)
)

Then you join your table to either parts, or to oems, to determine which of each is associated with the other:-

然后您将您的表连接到任何一个部分，或到原始设备管理系统，以确定哪一个与另一个相关联:-

select p.partno, count (po.partno) as oemcount
from
parts p
inner join parts_oems po on p.partno = po.partno
group by p.partno

And to determine which oems are associated with partno AA1001 you would write:-

要确定哪些原始设备与partno AA1001相关，你可以写:-

select p.partno, o.oemno, o.some_other_columns
from
parts p
inner join parts_oems po on p.partno = po.partno
inner join oems o on o.oemno = po.oemno
where p.partno = 'AA1001'

#2

You need to establish a relationship table.

您需要建立一个关系表。

+------+   +-----+
| PART |   | OEM |
+------+   +-----+
    \         /
   +----------+
   | PART_OEM |
   +----------+

PART needs a primary key such as PART_ID
部分需要一个主键，如PART_ID
OEM needs a primary key such as OEM_ID
OEM需要一个主键，比如OEM_ID
PART_OEM has two FOREIGN KEY constraints, one pointing to PART.PART_ID and the other pointing to OEM.OEM_ID
PART_OEM有两个外键约束，一个指向部分。PART_ID和另一个指向OEM.OEM_ID

#1