I'm wondering if there is a better way to set up my data structure.
The nature of the data is this:
I have a set of processes.
Within each process there are several sub-processes.
Each process and sub-process have a set of objectives associated with them.
The way it is currently set up is as follows:
Process Lookup table lists the process names
Subprocess Lookup table lists the subprocess names
Process Subprocess Relationship table has an ID, Process ID and Subprocess ID
This results in a list in which each process ID is repeated for each of its associated subprocesses plus a blank which I use to denote all subprocesses in the process.
Now I want to add objectives. First I built the lookup table:
Objectives Lookup table lists the objectives
Then I saw two alternatives:
I could add an Objectives ID field to the Process Subprocess table - seems like the wrong thing because I'd be repeating the Process - Subprocess relationship multiple times for no reason.
I could create a Process Objectives table with Process Subprocess ID and Objectives ID - seems right because now I can attach all the objectives I want to each process subprocess pair with repeating the pairing. Problem is it tells me the relationship is indeterminate when I do it.
Am I missing a far better way to do this or am I on the right track?
Thanks in advance.