Hierarchical Data Structure Issue

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.
or
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.
greillyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TrygveCommented:
It sounds to me like this could be done more easily; without the relationship table etc.
Can the sub processes be connected to more than one process ? If not you could include the processID as a field in your subproject table. The same is valid for the objectives table(s), you could probably also include the ID of the project/subproject it is linked to.

If you want you could send me a zipped version of your app, including the tables involved and I will look at it. email: kontry@statoil.no

Trygve
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
greillyAuthor Commented:
You should be able to find a zipper on any shareware site. Try:

http://www.slaughterhouse.com:80/zip.html

0
TrygveCommented:
Did you find the zipper ?
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

TrygveCommented:
can you recommend one in particular?  I looked, downloaded one (I don't remember which) and struggled with how to use it.  I think I downloaded a shell which I'm not sure is actually a full program.  What do you use?
0
greillyAuthor Commented:
I usually use WinZip or Visual Zip. I also use pkZip which is a good old DOS program. I can send you this one on mail if you provide me with your mail address...
0
TrygveCommented:
thanks it's greilly@measure.net
0
greillyAuthor Commented:
Did you get the file I sent you ?
0
TrygveCommented:
the file is on the way to you.  let me know if you don't get it in one piece.
0
greillyAuthor Commented:
I got the file. I will have a look at it later today or tomorrow.
0
TrygveCommented:
OK, it took more time than I had imagined to get back to you.

I have sent you a mail including some comments and a MDB with suggestions on how the tables could be set up.
0
TrygveCommented:
Got it.  That structure makes sense.  I'll email you at the end of the week regarding the other issues you brought up.  Thanks again.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.