Here is my challenge. My work has a wretched almost unsearchable "database".
I am trying to cobble together my own relational database from that
monstrosity so that I can pull the information I need quickly and link it to
other databases we have. My biggest road block is in how we store data what
widgets are inside a batch. Here is the problem:

We make widgets. The widgets are made in batches. The widgets that are in
a batch some times are merged into new batches, split, or destroyed. I
really want to be tracking what happens to a widget, not a batch.

The database stores ONLY batch IDs. It records when a widget leaves or
enters a batch, but there is no way to query “hey, what was in batch 66 at
processing step #10”. The best you can do is find that at process step #1
widgets A, B, and C were put into the batch, at step #6 C was removed, and at
step #9 D was added, so at step #10 widgets A, B, and D must be in the batch.

So the evil work database would looks like this for the above:
Batch, Step, Action, Action Value
66, 1, add widget, A
66, 1, add widget, B
66, 1, add widget, C
66, 6, remove widget, C
66, 9, add widget, D


Here is the question, what sort of methodology should I be using to set up a
nice clean relations table. I want it so that there is a table that more or
less says at step #10, batch #66, and components A, B, and D. In my ideal
world I think I would want it to look like:

Step, batch, component
10, 66, A
10, 66, B
10, 66, D
11, 66, A

Is there a way to do this? I really want to be able to answer the question
of “what widgets were in batch XX at step ZZ”.