“许多对许多人来说”关系。

I have a simple database of cosmetics. Each product may consist of multiple ingredients. Therefore it is a simple many to many relation and currently I have a database like this:

我有一个简单的化妆品数据库。每种产品可能由多种成分组成。因此这是一个简单的多到多的关系，目前我有一个这样的数据库:

CREATE TABLE `products` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` TEXT NOT NULL,
  PRIMARY KEY (`id`),
);

CREATE TABLE `ingredients` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` TEXT NOT NULL,
  PRIMARY KEY (`id`),
);

CREATE TABLE `ingredient_to_product` (
  `ingredient_id` INT UNSIGNED NOT NULL,
  `product_id` INT UNSIGNED NOT NULL,
  PRIMARY KEY (`ingredient_id`, `product_id`),
  INDEX (`product_id`)
);

However, some ingredients can have many different names. For example: "LINALOOL ESSENTIAL OIL", "LINALYL ALCOHOL" and "3,7-DIMETHYL-1,6-OCTADIEN-3-OL" refers to the same ingredient.

然而，有些成分可以有很多不同的名字。例如:“亚那洛尔精油”、“亚那利醇”和“3,7-二甲基-1,6-辛-3-醇”指的是同一种成分。

When I am presenting one product, I would like to keep ingredient name as on product label. So in case of one product it will be "LINALYL ALCOHOL" and in case of another product it may be something other, but referring to identical ingredient.

当我展示一种产品时，我想把成分名称保留在产品标签上。因此，如果有一种产品，它将是“LINALYL酒精”，如果是另一种产品，它可能是另一种产品，但指的是相同的成分。

Now I would like to query database for all names of the product by specifying one of its names or by its ID, however how one ID could refer to many names? Do I have to keep two tables: one for specific ingredient in general and another table just for names (relation many names to one ingredient)? Is there any better way?

现在我想通过指定产品的名称或ID来查询数据库的所有名称，但是一个ID如何可以引用多个名称呢?我必须保留两个表:一个是针对特定的成分，另一个是为名称(关系多个名称到一个成分)?有更好的办法吗?

3 个解决方案

#1

Ingredients

成分

ID (int) (pkey) (ai)
ID(int)(pkey)(ai)
Name (varchar)
名称(varchar)

Synonyms

同义词

ID (int) (pkey) (ai)
ID(int)(pkey)(ai)
IngredientID (int) (foreign key maps to: Ingredients.ID)
配料(int)(外键映射到:配料。
Name (varchar)
名称(varchar)
Flag (optional; sample values: IUPAC Name, Common Name, Spanish Name, ...)
国旗(可选;示例值:IUPAC名称、通用名称、西班牙语名称、…)

So yes, for the rules you described, you will at minimum have a primary name, and then a synonym table using the Ingredients.ID

因此，对于您所描述的规则，您将至少拥有一个主名称，然后使用该成分的同义词表。

I also introduced a Flag field so that you can programmatically choose when to use a synonym rather than just hard-coding it all the time. For example, if you're listing a product flagged as an industrial chemical, it would be programmed to pull the IUPAC* name

我还引入了一个标志字段，以便您可以编程地选择何时使用同义词，而不是一直硬编码同义词。例如，如果您要列出一个标记为工业化学品的产品，它将被编程以提取IUPAC*名称

*IUPAC refers to conventions for naming chemicals, and is used by professional chemists almost always, but almost never used in consumer supermarkets: http://www.chem.uiuc.edu/GenChemReferences/nomenclature_rules.html

*IUPAC指的是化学品命名的惯例，专业化学家几乎总是使用，但几乎从未在消费品超市中使用:http://www.chem.uiuc.edu/genchemreferences/nomenclave _rules.html

#2

I suppose the good news is that one name cannot be used for multiple different ingredients, right?

我想好消息是一个名字不能用于多种不同的成分，对吧?

So yes, you probably need an "ingredient name" table, something like

是的，你可能需要一个“原料名称”表，比如

`name` TEXT NOT NULL,
`ingredient_id` INT UNSIGNED NOT NULL

(I'd probably just use name as the PK here, but if your design calls for always using surrogates then also add ingredient_name_id I guess...)

(我可能只是在这里使用名称作为PK，但是如果您的设计要求总是使用代理，那么还需要添加一些成分——name_id，我猜…)

And then the details of the ingredient (except name) would still go in ingredient table. (You could keep a default name in the ingredient table as well, but I wouldn't. If you need that concept, I'd consider adding a "default flag" to the ingredient name table or something like that.)

然后原料的细节(除了名字)仍然会出现在配料表中。(你也可以在配料表中保留一个默认名称，但我不会。如果你需要这个概念，我会考虑在配料表或类似的东西上添加一个“默认标志”。

Since you want to know the name used for each ingredient by a given product, you would replace your current "ingredient to product xref" with an "ingredient-name to product xref".

因为您想知道给定产品中每个成分的名称，所以您可以将当前的“产品xref成分”替换为“产品xref成分名称”。

#3

Why don't you create a separate table for other ingredient name/chemical names, etc. and have it connected with the product id so it can be used separately when needed, and to avoid having any NULL values in your main table.

为什么不为其他成分名称/化学名称等创建一个单独的表，并将它与产品id连接，以便在需要时可以单独使用，并避免在主表中有任何空值。

#1