MYSQL – How to make a table with a column that will sum columns from parent tables

MySQLmysql-workbench

I have a table called FOODS. FOODS has two foreign keys: IDsize and IDflavor.

The parent tables FLAVOR and SIZE each have a column price.

The table FLAVOR has columns IDflavor, flavor and price with an example row (1, 'Beef', 10). The table SIZE has columns IDsize, size and price with an example row (3, 'BIG', 15).

Is it possible to make a column in FOODS that will automatically sum the values of the price columns of the corresponding rows in the parent tables FLAVOR and SIZE (using the foreign keys)? So if FOODS has IDsize = 3 and IDflavor = 1, the column should automatically get 25 (10+15).

Best Answer

What you're asking to do is a type of denormalisation: You're adding redundant data to a single table in order to avoid having to query multiple tables to get the same information. This is done to increase performance of SELECT queries and/or simplify such queries.

Since the price columns are in different tables / rows, you unfortunately can't use generated columns.

So you're going to have to consider other options:

  1. You could use a VIEW on top of the FOODS table which summed the prices from the two parent tables. So whenever you wanted to SELECT from the FOODS table (and you wanted to include sum_price), you instead SELECTed from this VIEW. Something like:

    CREATE VIEW V_FOODS([the columns of FOODS], sum_price) AS SELECT [the columns of FOODS], FLAVOR.price + SIZE.price AS "sum_price" FROM FOODS INNER JOIN FLAVOR USING(IDflavor) INNER JOIN SIZE USING (IDsize);

    (As pointed out by RDFozz, note that this isn't actually denormalisation of the data, since the actual data stay in the parent tables.)

  2. You could use TRIGGERs to populate a sum_price column in FOODS. You would need a trigger on UPDATEs to each of FLAVOR and SIZE, and a trigger on INSERTs to FOODS.

  3. You could have a sum_price column in FOODS and give your application the responsibility of keeping it up-to-date.

  4. You could also give up and not denormalise. Just let your application join FOODS, FLAVOR and SIZE whenever you need the sum of prices.

Related Question