Inception session: product chain data model

Continuing discussion from Discovery Session: Networks (WIP) and first inception from Myriam A first iteration on product chain logic enabling full transparency.

A- Reminder: what are the problems/needs

B- The data model we ended up with and that seems to handle all the cases we could think about

Agents
(Agent_ID / Agent_name)

Products
(Product_ID / Agent_ID / Product_name / Product_description)
Every product line will represent one “unit”. Then in the description we can detail if one “unit” is an item, or a number of kg or L, we don’t have to detail it here.

Products relationships
(Product_relationship_ID / Product_ID / Origin_Product_ID)
As soon as an agent creates a new product ID, a new Product relationship is created:

  • if the product is a “source” product (the raw product from the original producer) the Origin_Product_ID will be empty.
  • if the product is a “derivated product” we want to encourage users to specify from which product(s) they created the new product:
    • A derivated product can have a single origin (ex: buy potatoes by kg > sell potatoes by bags of 5 kg). If the derivated product is a perfect copy of the origin product (when Mary buys a product to Fred for instance and she receives the product and it is added to her inventory) a new product is created under the new agent ownership, and connected to the origin product (the product from the supplier he bought it from).
    • Or a multi-origin (ex: buy potatoes by kg + carrots by kg > sell vegetable baskets by item)

Products recipes / stock recipe…
(to be renamed maybe + we don’t know yet how/when we store the data but we know we need “recipes”).
When a new product relationship is created, we need the user to give the “recipe” of the new product, for example:

  • New product A comes from both B and C (product relationship)
  • AND Recipe : A = 2 x B + 1 x C

This will enable to manage stock all along the chain.

C- Two examples detailed so that you understand the logic

Let’s add a “stock movement table” in this example to show how that is going to work.

a- Compound derivated product

Ex: Mary buys vegetable to Fred and sells weekly vegetable baskets to Jane

Look at “aggregation” tab here to see the case: 'https://docs.google.com/spreadsheets/d/1_FUBRqC0W_nrThR6rE59eHEffCjfFy-lbSeI7kJ0w0A/edit#gid=480003136

b- Split derivated product

Ex: Mary buy an apple box of 100kg from Shannon and sells apple bags of 1kg to Jane

Look at “split” tab here to see the case:
'https://docs.google.com/spreadsheets/d/1_FUBRqC0W_nrThR6rE59eHEffCjfFy-lbSeI7kJ0w0A/edit#gid=1836728487

D- Next steps

[UPDATED by Myriam 08/07/2018]
THIS HAS NOT BEEN PRIORITIZED YET. Inception can move forward but nothing will happen on it before Q4 2018 or Q1 2019.
1- Enrico, Pau and Hugo proposed to build a quick prototype so that we can test and make sure all the cases we can think about are going to fit in it. Estimated time needed: 2/3 days.
2- Rachel and I wrote the following user stories for them to fuel their prototype. In this we started with simple cases but also ended up with very complex ones like Alterconso case and it seems that the model handle all the cases perfectly (For Alterconso part of the problem will be solved by delivery note – see other thread on delivery note to come)
3- When we have that prototype you can all test if that cover what we want and we can then start to break all that in stories and work on UX (UX will be a BIG job in here). Rachel and I might have a first go at it meanwhile the prototype is developed.
4- This feature is BIG and impact all the OFN basically:


We discussed about what was the very smaller step we could do toward that and more generally the network feature. We agreed the first bit would be customer categories / pricing tables that we could implement without starting to touch everything in the OFN. @Rachel and Myriam started to incept on it and Rachel is gonna open a new thread to present the first ideas we had, so that we can slowly start to get our heads around on how we gonna do it for when it is prioritized :slight_smile:

@Rachel @enricostn please check it is conform to what we said. Also I created an Epic https://github.com/openfoodfoundation/openfoodnetwork/issues/2228 to start working on the first step but not quite sure yet how to handle this, I asked our awesome @danielle some guidance.
@Kirsten @oeoeaio @maikel @lin_d_hop please have a look and tell if the path we propose (prototype to test that data model, then if all good we break into stories) fits you.

Does this make variant overrides obsolete, because they are just a new product?

I think so @maikel but @enricostn can confirm. And we can still have more flexible “taxonomies” that enable Mary to display multiple products under the same “banner” if we need, but they will all be different products (they are in real life aren’t they?).

I never quite understood what was the use case variant overrides actually covered. Was it a first implementation of the network feature? I assume so, right?

Yes. So the farmer would say: I have 100 lemons. And a shop would include these lemons in an order cycle. It would display that 100 lemons are available. But the farmer would say: hang on, you can’t sell all my lemons. 50 of them are promised to the other shop. I’m giving you only 50 lemons. So the first shop would “override” the onhand value to 50, because that is what they would get delivered.
Or one shop wanted to call them “delicious lemons” and the other shop wanted to call them “big lemons”. So each shop would override the original name. That’s at least how I understood it.

Thanks @maikel, that’s a good explanation. You’re right then, that’s exactly what creating new products will solve on a cleaner way, I believe.