如何在数据库中实现一对多和多对多的关系

I have a data for rubber belts where one compound has many chemicals and these many chemicals can be combined in any combination to form a new compound(one chemical in many compounds). I have created one table for compounds and one for chemicals. How do i form a relational table without huge repeat of data?

我有橡胶带的数据，其中一种化合物含有许多化学物质，这些化学物质可以任意组合形成一种新化合物（许多化合物中的一种化学物质）。我创建了一个化合物表和一个化学品表。如何在没有大量重复数据的情况下形成关系表？

2 个解决方案

#1

No doubt there's a huge number of tutorials on the net if you do a search. You should do some research or get a good database text. To help you out here's an example of your many to many relationship realised as one to many tables with an associated CompoundChemical table.

毫无疑问，如果你进行搜索，网上会有大量的教程。你应该做一些研究或获得一个好的数据库文本。为了帮助你，这里有一个你的多对多关系的例子，实现了一对多的表与相关的CompoundChemical表。

CREATE TABLE Compound
(
  CompoundId INT NOT NULL PRIMARY KEY,
  Name VARCHAR(100) NOT NULL
);

CREATE TABLE Chemical
(
  ChemicalId INT NOT NULL PRIMARY KEY,
  Name VARCHAR(100) NOT NULL
)

CREATE TABLE CompoundChemical
(
  CompoundId INT NOT NULL,
  ChemicalId INT NOT NULL,
  PRIMARY KEY (CompoundId, ChemicalId),
  FOREIGN KEY fk1 (CompoundId) REFERENCES Compound(CompoundId),
  FOREIGN KEY fk2 (ChemicalId) REFERENCES Chemical(ChemicalId)
)

#2

Adding table structures will help but you can probably go with something like this

添加表结构会有所帮助，但你可以选择这样的东西

Creating one table for compounds and one for chemicals is good but you'll most probably need one more table because there is no fixed number of chemicals in each compound.

为化合物创建一个表，为化学品创建一个表是好的，但是你很可能还需要一个表，因为每个化合物中没有固定数量的化学物质。

Maybe table named Substances that has following columns that reference chemicals and compunds. (ChemicalID, CompoundID)

也许这个名为Substances的表有以下列，它们引用化学品和计算。（ChemicalID，CompoundID）

#1

CREATE TABLE Compound
(
  CompoundId INT NOT NULL PRIMARY KEY,
  Name VARCHAR(100) NOT NULL
);

CREATE TABLE Chemical
(
  ChemicalId INT NOT NULL PRIMARY KEY,
  Name VARCHAR(100) NOT NULL
)

CREATE TABLE CompoundChemical
(
  CompoundId INT NOT NULL,
  ChemicalId INT NOT NULL,
  PRIMARY KEY (CompoundId, ChemicalId),
  FOREIGN KEY fk1 (CompoundId) REFERENCES Compound(CompoundId),
  FOREIGN KEY fk2 (ChemicalId) REFERENCES Chemical(ChemicalId)
)

#2