Designing tables for arbitrary sequence inputted by user?

Ignition
1

More of a database design question but its directly tied to an Ignition project and I think people here will have more experience with this sort of problem than stack overflow.

Here’s the feature I am working on -

  1. Let users create new sequence or load existing sequence. This sequence will be sent to the PLC to execute it in its entirety.
  2. These recipes can be of arbitrary length, could be 3 steps, could be 20 steps.
  3. Each of these steps is one of 6 step “types”
  4. this is the tricky part where I am getting hung up on - each step has a variable number of parameters - step type 1 requires 4 parameters, step type 2 requires 2 parameters, etc

Right now my design would be as follows -
image

My question is given that each step has a different number of different typed parameters, how would be the way to go about this?

I know one thing I could do is make enough columns under recipe_steps for each potential parameter, most of which would be left NULL in any row as only the ones related to that step type would get filled in. However, this is not a performance critical part and if I can normalize the tables a bit I’d rather that, I am just struggling to think how that would work.

Or is making a bunch of extra columns like

stepType1_param_1, stepType1_param_2, stepType2_param_1, etc in my recipe_steps be the easiest best way to do it?

2

If the sequence is going to be sent directly from the DB to the PLC, I would probably go with this. I might go with this any way, though if you're going to be doing any processing prior to the PLC then there is potentially an opportunity to store them in a more "processing" friendly way.

1 Like
3

Yea this works for sure. Seems reasonable enough. I’m thinking too if I did normalize it I might need a conditional join statement based on the step type which may be confusing to other maintainers, this way seems pretty straight forward for the next person.

I think I was probably overthinking it or being too attached to having it normalized.

1 Like