Guidance for data modelling

0
We use Ingredient_Staging only for raw Excel import, then transform and distribute the data into normalized master tables (Ingredient, Supplier) and a transactional junction table (IngredientSupplier) to maintain data integrity and support historical supplier pricing.Why we did this: Because the requirement is:One Ingredient can have many Suppliers.One Supplier can supply many Ingredients.Pricing changes over time.We must prevent duplicates on re-import.That cannot be handled correctly in a single flat table.High Level FlowExcel ↓Ingredient_Staging ↓ (Transformation Microflow)IngredientSupplierIngredientSupplierI want to create a tranformation microflow for this pls help i am stuck in this not been able to do this for quite some time.
asked
2 answers
0

First add an IsNew (Boolean) attribute to the Ingredient_Staging entity and set it to true during the Excel import. Also configure access so that only the owner can read and write these staging records. This ensures that if multiple users perform imports at the same time, their data remains isolated and does not interfere with each other.


In the transformation microflow, retrieve only the staging records where Owner = CurrentUser and IsNew = true. Before entering the loop, create two in-memory cache lists: IngredientList and SupplierList. These lists help prevent creating duplicate Ingredient or Supplier objects during the same import process.


Inside the loop, for each staging record, check whether the corresponding Ingredient and Supplier already exist in the cache lists using their business keys (for example, material number for Ingredient and supplier number for Supplier). If they do not exist, create them and add them to the lists. If they already exist, reuse them instead of creating duplicates. Then create or update the IngredientSupplier junction entity based on your defined unique key, typically a combination such as Ingredient + Supplier + Year or Ingredient + Supplier + SnapshotDate, depending on your pricing history logic.


After processing each staging record, set its IsNew attribute to false so it will not be processed again in future runs. Once the loop is complete, perform a batch commit (if more data exists) on the IngredientList and SupplierList. This improves performance and ensures a consistent, duplicate-free transformation from staging to your normalized master and transactional tables.


answered
0

hi,


Move data from:

Ingredient_Staging →

Ingredient + Supplier + IngredientSupplier

Microflow: MF_Transform_Staging

1.Retrieve Staging Records

Retrieve all Ingredient_Staging records (or only unprocessed ones).

Loop through the list.

2.Inside Loop – Ingredient

Retrieve Ingredient using a business key, for example:


[component_material_num = $Staging/component_material_num]

If empty → Create + set attributes + Commit

Else → Use existing record

3.Supplier

Retrieve Supplier:


[supplier_number = $Staging/supplier_number]

If empty → Create + Commit

Else → Use existing

4.Junction Table (IngredientSupplier)

Retrieve:


[IngredientSupplier_Ingredient = $Ingredient
 and IngredientSupplier_Supplier = $Supplier
 and year = $Staging/year]

If empty →

Create IngredientSupplier

Set pricing fields

Set associations

Commit

Else →

Update pricing

Commit

Important (Prevents Duplicates)

Add Unique Indexes:

  • Ingredient → component_material_num
  • Supplier → supplier_number
  • IngredientSupplier → Ingredient + Supplier + Year

This is mandatory for data integrity.

5.After Processing

Delete or mark Ingredient_Staging as processed.

Why This Works

• No duplicate master data

• Supports historical pricing

• Safe for re-import

• Follows Mendix best practice for normalization

answered