1

Background

Two years ago, I asked a question about how to model a food's relationship with its nutrients. Today, that design has evolved. The biggest change is that whenever a food is updated, we insert a new row into the food_versions table. We do not delete or update food versions.

This design was influenced by the following answer: https://dba.stackexchange.com/a/278893/240214. I've since learnt this is based on a Type 2 'slowly changing dimension'.

Additional information

A food version's nutrients (e.g. fat), allergens (e.g. milk), and diets (e.g. vegetarian) are stored in seperate tables and can also change over time. For example, a new food version that has a change to ingredients will often need new nutrient records also.

The relevant tables

CREATE TABLE "foods" (
  "id" uuid DEFAULT gen_random_uuid() PRIMARY KEY, -- Surrogate key
  "code" text NOT NULL, -- Natural key
  "organisation_id" uuid NOT NULL REFERENCES "manufacturer"("id") ON DELETE CASCADE,
  UNIQUE("code", "organisation_id") -- One natural key per manufacturer
);

CREATE TABLE "food_versions" (
  "food_id" uuid REFERENCES "foods"("id") ON DELETE CASCADE,
  "created_time" TIMESTAMP(6) WITH TIME ZONE DEFAULT NOW(),
  "name" text NOT NULL,
  "quantity" numeric NOT NULL,
  "quantity_unit_id" text NOT NULL REFERENCES "units"("id"),
  "ingredients" text NOT NULL,
  PRIMARY KEY("food_id", "created_time")
);

CREATE TABLE "food_nutrients" (
  "food_id" uuid REFERENCES "foods"("id") ON DELETE CASCADE,
  "nutrient_id" text REFERENCES "nutrients"("id"),
  "created_time" TIMESTAMP(6) WITH TIME ZONE DEFAULT NOW(),
  "quantity" numeric NOT NULL,
  "quantity_unit_id" text NOT NULL REFERENCES "units"("id"),
  PRIMARY KEY("food_id", "nutrient_id", "created_time")
);

CREATE TABLE "food_allergens" (
  "food_id" uuid REFERENCES "foods"("id") ON DELETE CASCADE,
  "allergen_id" text REFERENCES "allergens"("id"),
  "created_time" TIMESTAMP(6) WITH TIME ZONE DEFAULT NOW(),
  "status" text NOT NULL, -- 'FREE_FROM', 'MAY_CONTAIN', 'CONTAINS'
  PRIMARY KEY("food_id", "allergen_id", "created_time")
);

CREATE TABLE "food_diets" (
  "food_id" uuid REFERENCES "foods"("id") ON DELETE CASCADE,
  "diet_id" text REFERENCES "diets"("id"),
  "created_time" TIMESTAMP(6) WITH TIME ZONE DEFAULT NOW(),
  PRIMARY KEY("food_id", "diet_id", "created_time")
);

The data in these tables changes in bulk several times a year when we receive new catalogues (semi-structured spreadsheets) from food manufacturers.

Our import process

  1. Move the data we need from the manufacturer's semi-structured spreadsheet into our own structured spreadsheet. This looks similar to if all tables above were joined together:
code name ... fat ... peanuts ... vegetarian ...
1 Sausage roll ... 21 ... FALSE ... FALSE ...
  1. For each row in the structured spreadsheet, select the food from the database by code and compare its fields against those in the spreadsheet. This also requires querying relations (nutrients, allergens, and diets).

  2. If fields have changed, determine which tables to insert new rows into.

For brevity, I've not included cases where the update is an addition (e.g. a new food) or removal (e.g. delisting of a food or removal of an allergen).

It's worth noting that these problems are inherent in the supply chain, because manufacturers do not always create new codes when they update existing products.

Question

Is there a better design, than the one proposed below, to simplify our process of importing only changed data?

Join all tables together into one "foods" table, like our structured spreadsheet, and add a column to store a hash for change comparison.

Pros

  • Instead of comparing all fields against those to be imported, we only have to compare the hashes.
  • Any change (name, nutrient etc) results in a single transaction/insertion, which is easy reason about.
  • We almost always need nutrients, allergens, and diets when we request a food's information.

Cons

  • We are denormalising our data, which will result in a wide table with a large quantity of NULLs.
  • Due to denormalisation, we will also have more duplicated data (e.g. a change in name will mean a new version that has the same nutrient values as the one above it). Duplication already happens to some degree.

1 Answer 1

0

I'm considering joining all food tables into one big "foods" table (similar to the spreadsheet we use for importing) and adding a column to store a hash.

You don't need to do this in a table. Create a view instead, and add the hash column there. This will save you from duplicating or denormalizing your data. If performance is a concern (though it probably isn't, unless you have 10s of millions of foods), you may be able to utilize a materialized view also, to persist the calculated hash value.

Cons

  • We are denormalising our data, which will result in a wide table with a large number of NULLs.
  • Due to denormalisation, we'll also have more duplicated data (e.g. a change in name will mean a new row that will have the same nutrient values as the one above it).

Yea, again, don't do this to your tables. Normalization in the tables is good, otherwise you can run into the aforementioned problems. Denormalization in views is acceptable though, since views don't actually store the original data. They just show a manipulation based on the original data.

4
  • Thanks @J.D., I can see how this would help with identifying changes, but how will it help when it comes to inserting a new food, nutrient, allergen, and/or diet version? I.E. If the hash of the row in the view is different to the one I'm importing, I know there has been a change, but without comparing each value, I won't know which underlying table I need to insert new records into. With a single table, I only have to know there has been a change and the insertion of a single row will cover it. I hope this make sense.
    – Check12
    Nov 20, 2023 at 16:32
  • @Check12 "how will it help when it comes to inserting a new food" - No different than if you used a table. You have to identify what's not in your table either way. You can accomplish this with an outer join to identify what foods are new. You can do the same to a view. Alternatively you can just insert everything, and if the food exists already, it'll error out (if keyed properly). If you chose this methodology, you can also do it through a view, as views are also insertable (which push the inserts down into the base table).
    – J.D.
    Nov 20, 2023 at 19:02
  • Thanks again, @J.D.. Imagine we have a record in food_versions that has a food_id of "1" and a name of "sausage roll" with a related food_nutrient that has a nutrient_id of "fat" and quantity of "21". In the latest spreadsheet, food with id "1" still exists, but its recipe has changed and its "fat" quantity is now "19". The hashes of the row in the view and spreadsheet don't match, so we know we need to insert something, but how do we know we need to insert a new row into food_nutrients to reflect the updated fat? As a reminder, we never delete or update. All changes are tracked with insert.
    – Check12
    Nov 20, 2023 at 21:38
  • @Check12 Knowing you only insert & looking closer at your schema, then what you should probably do is have a separate hash for each set of attributes (one hash field for each table essentially) and compare those hashes to the data provided to you, again, only comparing the relevant fields from that source data when comparing hashes. Then you'll know which types of data changed (e.g. the nutrients, or the allergens, or the diets) and ergo know which table(s) needs to be inserted into. You probably don't even need views to accomplish that then, rather just a "generated column" in each instead.
    – J.D.
    Nov 20, 2023 at 22:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.