Reporting with cyclic or relationships

Question

I’ve got me a design and implementation problem extracting and formulating the results I need.
What I’m trying to do is build some reporting on forecasted stock commitments in an inventory system to help drive JIT stock procurement and manufacturing. I’m really sorry, there is a fair bit to this so if you stick with me on this explanation, I really appreciate it.

The underlying database for the inventory system I’m using is a 3rd party propriety database that I cannot alter. What I’m doing is using C# I'm executing a number of simple SQL queries via an oleDB connection to extract the data into object collections then manipulate the results via a series of linq queries and lamba functions. The data model I’m working with is as follows: Please note, this is somewhat simplified

For any one product, what I need to show:

1. The Stock on hand
2. The list of jobs that make up forecasted demand (Needs to be separated by Job manufacture attribute)
3. Calculate the forecasted commitments for products based on business rules
4. Calculate the lead time of manufactured products.

Some other notes

1. A Kitset is a product that has components (End product is manufactured).
2. A component can itself be a kitset.
3. A component is a product used in the manufacture of kitsets.
4. Any product can be supplied direct to end consumer regardless of being either a kitset, a component or otherwise.

Rules to calculate product Lead Times If product is not a Kitset, use lead time from database, no further change is required Otherwise, will need to calculate based on the lead times for the component products as follows

If Kitset product, Kitset Lead Time = DB LeadTime + Maximum component Lead Time where NonDiminishing = false + the Sum of component LeadTime where NonDiminishing = True

To calculate the future commitments of products Consider this example.

Easiest to start at the bottom and work my way up.

• Kitset Product 3, I’ve got 22 in stock, and future commitment for 30. This means I need to manufacture a further 8 units to satisfy demand.
• Kitset Product 2, I’ve got 18 in stock and a supply commitment 16. In addition to this I also need 8 units to satisfy the manufacture requirements of Kitset Product 3 giving me a total commitment of 24 meaning I have to manufacture 6 units.
• Kitset Product 1 follows the same sort of path. I’ve got 4 in stock, I need 6 units to satisfy the requirements of Kitset Product 2 so I need to manufacture a further 2 units.

This leads me to where I’m getting hung up. Effectively I’ve got a collection of products, each instance of a product may have a collection of products and so on. I’m really not sure how to implement this cyclic relationship.

Really appreciate any feedback you can give me.

Answer 1

After a couple days working on other issues, I had a moment of clarity.

So, turns out my problem isn't so much the cyclic relationship. It's how to recursively calculate the future stock commitments. What was bugging me, I'm able to do it manually quite easily but I wasn't able to see how to translate that process into my code.

When Calculating it manually, what I found I was doing was starting at the bottom of the product/kitset/component hierarchy and calculating from the bottom up. When coding my solution, I've been starting at the top and trying to calculate top down. The top down approach was getting me stuck in a bit of a dependency loop.

So using pretty much the same model structure as suggested by JimmyJames, add one more int property to indicate the build level. Then starting at the highest build levels, I can iterate back to the source products.

Really do appreciate everyone that looked at this for me

Answer 2

Object model:

Essentially your kitset is a tree structure. The most direct way to do this is to define something along these lines:

public class KitSet {
  public List<KitSet> GetKitsets() {
    // TODO return kitsets.  Return empty list if none.
  }

  public List<Product> GetProducts() {
    // TODO return products.  Return empty list if none.
  }

  public string ToString () {
    // TODO output the kit name
  }
}

Processing model:

Then let's say you want to print a Kitset out for debugging purposes. You can write a recursive function like so:

decimal Total(Kitset kit) {
  decimal sum = 0.0;

  foreach (Product p in kit.GetProducts) {
    sum += p.total;
  }     

  foreach (Kitset k in kit.GetKitsets) {
    sum += Total(k);
  }

  return sum;
}

I've attempted to follow C# sytax and conventions here but I may have not gotten it quite right. Please feel free to ask if you are unsure about what I've written.