Attempting To Avoid Polymorphic Associations

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

Solution 1:

CREATETABLE mixers (
    mixer_id INT(11) NOTNULLPRIMARY KEY AUTO_INCREMENT,
    mixer_name VARCHAR(255) NOTNULL,
)

CREATETABLE ingredients (
    ingredient_id INT(11) NOTNULLPRIMARY KEY AUTO_INCREMENT,
    ingredient_name VARCHAR(255) NOTNULL,
)

CREATETABLE mixer_ingredients
    mixer_id INT(11) NOTNULL,
    ingredient_id  INT(11) NOTNULL,
    PRIMARY KEY (mixer_id, ingredient_id),
    FOREIGN KEY (mixer_id) REFERENCES mixers(mixer_id),
    FOREIGN KEY (ingredient_id) REFERENCES ingredients(ingredient_id),
)

Solution 2:

I understand what you are trying to do, but think it is the wrong approach. Your data model needs to better fit your problem.

First, start with an ingredients table, with columns like:

• ingredientId
• ingredientName
• ingredientType

Then the MixedDrink table:

• MixedDrinkId
• MixedDrinkName

Then a the MixedDrinkIngredient table:

• MixedDrinkIngredientId
• MixedDrinkId
• IngredientId

So, this handles the mixed drinks without substitutions. How do you represent them?

Well, at this point, you need to make some decisions. Is the substitution generic for the ingredient? Or is the substitution something allowed for some MixedDrinks but not others? Depending on the answers to these questions, the substitution information could go in MixedDrinkIngredients, Ingredients, or both, depending on the rules you need.

By the way, the distinctions between booze and soda is embedded in the type information in Ingredients. This is a simplification, you might actually want a hierarchy, with substitutions at particular places in the hierarchy. For example, you might be willing to substitute Pepsi for Coke, but not Ginger Ale.