Java HashMap as an element in MySQL via JPA

Question

I already asked this question but I didn't receive a straight answer: https://stackoverflow.com/questions/31489973/java-hashmap-in-many-to-many-relationship-mysql?noredirect=1#comment50944140_31489973.

This MySQL database has three tables: Meal, Ingredient, and IngredientListAndAmount. Each row in Meal corresponds to a row in IngredientListAndAmount. IngredientListAndAmount can be stored as a Java HashMap, where an Ingredient ID is the key and an integer amount is the value.

How I would represent this dependency in a MySQL database?

Answer 1

What you seek is often called a join table. This is how SQL represents many-to-many relationships between two entities. The purest of pure join tables joining tables A and B consists of two columns, one containing A_id, and the other B_id. The two columns together constitute the primary key of the join table.

In your case you'll have three columns in the join table:

 Meal_id        pk, fk to Meal table
 Ingredient_id  pk, fk to Ingredient table
 Amount         something giving the amount

If you want the ingredients for a particular meal, you use a query like this:

SELECT m.Mealname, i.IngredientName, mi.Amount
  FROM Meal AS m
  JOIN MealIngredient AS mi ON m.Meal_id = mi.Meal_id
  JOIN Ingredient AS i ON mi.Ingredient_id = i.Ingredient_id