How can I simplify the process of importing only changed data?

Question

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	...

2. 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).

3. 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.

Answer 1

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.