尝试避免多态关联 [英] Attempting to avoid polymorphic associations

问题描述

我正在创建一个MySQL数据库，并且遇到了需要多态关联的实例.显然，SQL不支持此关联，因此我决定为每个外键创建一个列；在下面找到:

I'm creating a MySQL database and I ran into an instance in which I needed polymorphic associations... Obviously SQL does not support this, so I decided to create a column for each foreign key; found below:

CREATE TABLE mixers (
mixers_id INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
0_booze_id INT(11),
0_gen_booze_id INT(11),
0_gen_soda_id INT(11),
0_soda_id INT(11),
1_booze_id INT(11),
1_gen_booze_id INT(11),
1_gen_soda_id INT(11),
1_soda_id INT(11),
2_booze_id INT(11),
2_gen_booze_id INT(11),
2_gen_soda_id INT(11),
2_soda_id INT(11),
3_booze_id INT(11),
3_gen_booze_id INT(11),
3_gen_soda_id INT(11),
3_soda_id INT(11),
4_booze_id INT(11),
4_gen_booze_id INT(11),
4_gen_soda_id INT(11),
4_soda_id INT(11),
5_booze_id INT(11),
5_gen_booze_id INT(11),
5_gen_soda_id INT(11),
5_soda_id INT(11),
6_booze_id INT(11),
6_gen_booze_id INT(11),
6_gen_soda_id INT(11),
6_soda_id INT(11),
7_booze_id INT(11),
7_gen_booze_id INT(11),
7_gen_soda_id INT(11),
7_soda_id INT(11),
8_booze_id INT(11),
8_gen_booze_id INT(11),
8_gen_soda_id INT(11),
8_soda_id INT(11),
9_booze_id INT(11),
9_gen_booze_id INT(11),
9_gen_soda_id INT(11),
9_soda_id INT(11),
FOREIGN KEY (0_booze_id) REFERENCES booze(booze_id),
FOREIGN KEY (1_booze_id) REFERENCES booze(booze_id),
FOREIGN KEY (2_booze_id) REFERENCES booze(booze_id),
FOREIGN KEY (3_booze_id) REFERENCES booze(booze_id),
FOREIGN KEY (4_booze_id) REFERENCES booze(booze_id),
FOREIGN KEY (5_booze_id) REFERENCES booze(booze_id),
FOREIGN KEY (6_booze_id) REFERENCES booze(booze_id),
FOREIGN KEY (7_booze_id) REFERENCES booze(booze_id),
FOREIGN KEY (8_booze_id) REFERENCES booze(booze_id),
FOREIGN KEY (9_booze_id) REFERENCES booze(booze_id),
FOREIGN KEY (0_gen_booze_id) REFERENCES gen_booze(gen_booze_id),
FOREIGN KEY (1_gen_booze_id) REFERENCES gen_booze(gen_booze_id),
FOREIGN KEY (2_gen_booze_id) REFERENCES gen_booze(gen_booze_id),
FOREIGN KEY (3_gen_booze_id) REFERENCES gen_booze(gen_booze_id),
FOREIGN KEY (4_gen_booze_id) REFERENCES gen_booze(gen_booze_id),
FOREIGN KEY (5_gen_booze_id) REFERENCES gen_booze(gen_booze_id),
FOREIGN KEY (6_gen_booze_id) REFERENCES gen_booze(gen_booze_id),
FOREIGN KEY (7_gen_booze_id) REFERENCES gen_booze(gen_booze_id),
FOREIGN KEY (8_gen_booze_id) REFERENCES gen_booze(gen_booze_id),
FOREIGN KEY (9_gen_booze_id) REFERENCES gen_booze(gen_booze_id),
FOREIGN KEY (0_gen_soda_id) REFERENCES gen_soda(gen_soda_id),
FOREIGN KEY (1_gen_soda_id) REFERENCES gen_soda(gen_soda_id),
FOREIGN KEY (2_gen_soda_id) REFERENCES gen_soda(gen_soda_id),
FOREIGN KEY (3_gen_soda_id) REFERENCES gen_soda(gen_soda_id),
FOREIGN KEY (4_gen_soda_id) REFERENCES gen_soda(gen_soda_id),
FOREIGN KEY (5_gen_soda_id) REFERENCES gen_soda(gen_soda_id),
FOREIGN KEY (6_gen_soda_id) REFERENCES gen_soda(gen_soda_id),
FOREIGN KEY (7_gen_soda_id) REFERENCES gen_soda(gen_soda_id),
FOREIGN KEY (8_gen_soda_id) REFERENCES gen_soda(gen_soda_id),
FOREIGN KEY (9_gen_soda_id) REFERENCES gen_soda(gen_soda_id),
FOREIGN KEY (0_soda_id) REFERENCES soda(soda_id),
FOREIGN KEY (1_soda_id) REFERENCES soda(soda_id),
FOREIGN KEY (2_soda_id) REFERENCES soda(soda_id),
FOREIGN KEY (3_soda_id) REFERENCES soda(soda_id),
FOREIGN KEY (4_soda_id) REFERENCES soda(soda_id),
FOREIGN KEY (5_soda_id) REFERENCES soda(soda_id),
FOREIGN KEY (6_soda_id) REFERENCES soda(soda_id),
FOREIGN KEY (7_soda_id) REFERENCES soda(soda_id),
FOREIGN KEY (8_soda_id) REFERENCES soda(soda_id),
FOREIGN KEY (9_soda_id) REFERENCES soda(soda_id)
)ENGINE=InnoDB;

我想做的是设置表格，以便对每组食材-

What I would like to do, is setup the table so that for each group of ingredients -

0_booze_id INT(11),
0_gen_booze_id INT(11),
0_gen_soda_id INT(11),
0_soda_id INT(11)

• 只能使用一列(三列必须为空)或一组中的所有四列必须为空.

是否有可能创建类似这样的东西??? 如果没有，什么是更好的解决方案?

Is it even possible to create something like this??? If not, what would be a better solution?

编辑 为了澄清-我正在制造一个自动饮料混合机器人.我正在尝试创建一个数据库，该数据库允许在没有特定成分的情况下使用通用成分.

EDIT For clarification - I'm making an automated drink mixing robot. I'm trying to create a database that allows the use of generic ingredients in the event that a specific ingredient is not available.

例如，如果某个配方要求使用杰克"和可乐"，而我有可用的杰克"和可乐"成分，那么我想使用那些特定的成分.但是，可以说我有一些我想用可口可乐代替品牌可乐.我希望专门针对杰克"和可口可乐"的食谱能够用可口可乐"代替通用可乐".

For example, if a recipe calls for "Jack" and "Coke", and I have the ingredients "Jack" and "Coke" available, then I would want to use those specific ingredients. However, lets say I have some off brand cola that I wish to use in place of Coke. I want the recipes that call specifically for "Jack" and "Coke" to be able to substitute "Coke" for "generic cola".

我的意图是创建两个通用表，一个用于汽水，一个用于"gen_soda""gen_booze".这些表将包含诸如可乐"，威士忌"，伏特加"之类的项目.苏打水和酒的表将包含特定的成分名称，例如可乐"或杰克丹尼尔斯"；然后将这些项目引用回其通用类型.

My intent was to create two generic tables, one for soda and one for booze "gen_soda" "gen_booze". These tables would contain items such as "Cola" "Whiskey" "Vodka". The tables soda and booze would contain specific ingredient names such as "Coke" or "Jack Daniels"; these items would then be referenced back to their generic types.

我最初发布的搅拌机表是用来容纳机器人知道如何制作的所有各种混合饮料的.唯一应提供的混合饮料是具有特定成分的饮料.这就是我遇到的术语多态关联的方式.

The mixers table that I originally posted about was to contain all the various mixed drinks that the robot would know how to make. The only mixed drinks that should be available are ones that have the specific ingredients available. This is how I came across the term polymorphic associations.

推荐答案

CREATE TABLE mixers (
    mixer_id INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
    mixer_name VARCHAR(255) NOT NULL,
)

CREATE TABLE ingredients (
    ingredient_id INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
    ingredient_name VARCHAR(255) NOT NULL,
)

CREATE TABLE mixer_ingredients
    mixer_id INT(11) NOT NULL,
    ingredient_id  INT(11) NOT NULL,
    PRIMARY KEY (mixer_id, ingredient_id),
    FOREIGN KEY (mixer_id) REFERENCES mixers(mixer_id),
    FOREIGN KEY (ingredient_id) REFERENCES ingredients(ingredient_id),
)

这篇关于尝试避免多态关联的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！