Is there a better way to do it?

[natursalus]

Hello,

I have the following tables:

Parent

Child

Child Item

GrandChild

GrandChild Item

The table relationships(Parent -> Child; One -> Many)are the following:

Parent -> Child -> GrandChild

Child -> Child Item

GrandChild -> GrandChild Item

In the "real" world each table represents a type of document.

For each table (type of document) there are different tasks (document appraisal; document users; document points of use; document storage; etc) carried out by people from the organization.

So, for a record of any of these tables the same person can be doing different tasks as: document appraiser, user, responsible of the document point of use; reposible for document storage, etc.

For example, for the Parent table (Management System Manual):

John Smith could be the originator (document appraisal) of the MS Quality Manual

John Smith could be the user of the MS Quality Manual

John Smith could be the responsible for the use of the MS Quality Manual in the Manufacturing Plant

John Smith could be the responsible for the storage of the MS Quality Manual in the Manufacturing Plant

etc.

To make sure that John Smith can show up several times, as needed, in the same record doing different tasks I have created a table of People for each task.

So I have the following People tables:

Appraisers

Users

Points of Use

Storage

etc

All of these tables have the same information:

FullName

Job Title

Department

So for the Parent table I have the relationships with the People tables:

Appraisers -> Appraiser/Manual <- Parent

Parent <- Parent/Users <- Users

Points of Use -> PointsofUse/Parent <- Parent

Parent <- Parent/Storage <- Storage

It is not viable just to associate to a person's data a list of all his/her tasks, since this makes no sense in my project.

So in order to repeat the same information ( a person's data) in different places of the same layout I came out with the aforementioned structure.

I am sure that there must be a better way to use the same information (a person's data) on a layout but for different tasks.

Any comment on the right direction will be appreciated.

Thanks

[comment]

In the "real" world each table represents a type of document.

How different are these types - in terms of data recorded about each type? I suspect the type should be just another field in a combined table of Documents - but I believe I have already said this before.

All of these tables have the same information:

Well, obviously that's not the best approach. The idea is to enter a person's name once only. Just imagine what you'll have to go through if John Smith changes his name.

You should have one table for all the people doing any work on any project. This will contain their personal info - and none of the tasks. Define another table for Tasks, with fields for PersonID, the DocumentID, and the task the person fulfills in this context. Of course, if you have many document tables, you will also need many Tasks tables - and your relation graph will be difficult to manage.

[natursalus]

Hello Comment,

How different are these types - in terms of data recorded about each type?

Parent, Child and GrandChild have identical information

ChildItem & GrandChildItem have identical information

I suspect the type should be just another field in a combined table of Documents - but I believe I have already said this before.

Yes, you did in a former post.

I am having problem visualizing how it will be for the user to enter in the same table information for:

Parent, Child and GrandChild tables

and

ChildItem and GrandChildItem

without getting lost.

Surely it is easier form the developper point of view to create a Document table with a field for document type, but I am not sure if, from the user point of view, it is easier going to a specific document table.

What I would like to achieve is the sensation that when the user goes to the Procedures table or chooses the Procedure document type he/she is in the Procedure "box" or "window" without being distracted by the records from another document type. Can this be achieved with just the document table?

and your relation graph will be difficult to manage.

It's rather messy right now as it is.

As always many thanks for your expertise and help

[comment]

Surely it is easier form the developper point of view to create a Document table with a field for document type, but I am not sure if, from the user point of view, it is easier going to a specific document table.

It's not a question of what's easier, but what is correct. And there is a lot of work for the developer in creating the proper user interface - so that users don't get lost or distracted. I am not familiar with your workflow, so I cannot offer any specific advice, but have a look here for one possible idea:

http://fmforums.com/forum/topic/72391-getting-more-out-of-filtered-portals-6-hierarchical-portal/page__view__findpost__p__342213

[natursalus]

Many thanks Comment