How can I model resource exchange processes?

Question

I'm trying to model the economy of a resource management game. Processes turn one type of resource into another, at varying rates.

For example, process A turns 1x item A into 1x item B. That's simple, but process B turns 2x item B or 1x item A and 1x item B into 5x item C and 4x item D, and process C turns N x item C into (N - 4)x item E.

I don't know how to express that a process can have multiple outputs, optional inputs, or variables instead of integers. How should these processes be represented in a database? The goal is to build a hierarchy of processes to then find the most efficient path to produce specific items. Is there a name for this model?

Answer 1

I suggest this table layout https://dbfiddle.uk/YnGVLVVS This will allow a query that presents all of the Trade Option details

--Breakdown of output
SELECT pkTradeOption as TradeOption, 
       P.Process, 
       T.Description,  
       T.InputOption, 
       R.Description, 
       R.Amount, 
       R.ResourceType, 
       P.Output,
       --p.OutputAmount,     
       O.ResourceType,
       R.Amount,
       Sum(R.Amount) OVER (PARTITION BY pkTradeOption) as InputAmount,
       CASE Sum(P.OutputAmount) OVER (PARTITION BY pkTradeOption) 
       WHEN 0 THEN 0.0
       ELSE CAST(p.OutputAmount as FLOAT)  / CAST (Sum(R.Amount) OVER (PARTITION BY pkTradeOption) as FLOAT)
       END as EfficiencyRating
FROM Process P
LEFT JOIN TradeOption T ON T.fkProcess=P.pkProcess
LEFT JOIN InputResource R ON T.pkTradeOption=R.fkTradeOption
LEFT JOIN OutputResource O ON P.pkProcess=O.fkProcess