Table Relationship problem

[uarana]

Hi All,

I've been plugging away at this problem for a while now and was wondering if anyone had an idea they could share.

I'm working on a document management database that tracks revisions of Manuals. So here it goes..

There are a group of manuals that need to be maintained. Each manual has an associating set of tabs that relate to it. Each tab in turn has a set of chapters that relate to it.

A client can sign out an entire manual, or an entire tabs or specific chapters.

When a request comes in to revise these manuals/tabs/chapters, they need to be tracked as signed out so that no other client can make changes to them. The request usually carries more then one change with it (like to an entire manual, or an entire tab, or a set of chapters from several manuals).

Now so far i have 4 tables:
-Clients
-Manuals
-Tabs
-Chapters

They are linked as follows:
Client has a one to many relationship with Manuals, Tabs and Chapters Tables
Manual Table has a one to many relationship with the Tabs Table
Tabs Table has a one to many relationship with the Chapters Table

Now i need to create a table that monitor's the incoming request (as a batch). It should be able to assign a request an ID, and track the client's name, and the manual, tab or chapter they have signed out.

I will then use the request ID from this table and take it through two other revision processes (where other Auto ID's have to be assigned - Job ID, PublishedID)

I just don't know how to relate this table to the other's. Any help would be much appreciated.

Thanks,
Oz

[sanniep]

Hi All,

I've been plugging away at this problem for a while now and was wondering if anyone had an idea they could share.

I'm working on a document management database that tracks revisions of Manuals. So here it goes..

There are a group of manuals that need to be maintained. Each manual has an associating set of tabs that relate to it. Each tab in turn has a set of chapters that relate to it.

A client can sign out an entire manual, or an entire tabs or specific chapters.

When a request comes in to revise these manuals/tabs/chapters, they need to be tracked as signed out so that no other client can make changes to them. The request usually carries more then one change with it (like to an entire manual, or an entire tab, or a set of chapters from several manuals).

Now so far i have 4 tables:
-Clients
-Manuals
-Tabs
-Chapters

They are linked as follows:
Client has a one to many relationship with Manuals, Tabs and Chapters Tables
Manual Table has a one to many relationship with the Tabs Table
Tabs Table has a one to many relationship with the Chapters Table

Now i need to create a table that monitor's the incoming request (as a batch). It should be able to assign a request an ID, and track the client's name, and the manual, tab or chapter they have signed out.

I will then use the request ID from this table and take it through two other revision processes (where other Auto ID's have to be assigned - Job ID, PublishedID)

I just don't know how to relate this table to the other's. Any help would be much appreciated.

Thanks,
Oz

I think I'm missing the point here but what about the following.. When a new request comes in a Request will be created in the Request table, this request gets it's own ID. The rest of the table should contain at least a ClientId, ManualId, TabId, ChapterId. You fill out these fields with the used Id's from the other tables and then you will have your relation... It's 7.30 in the morning here so maybe I'm talking nonsens but does this help?

Sander

[uarana]

I think I'm missing the point here but what about the following.. When a new request comes in a Request will be created in the Request table, this request gets it's own ID. The rest of the table should contain at least a ClientId, ManualId, TabId, ChapterId. You fill out these fields with the used Id's from the other tables and then you will have your relation... It's 7.30 in the morning here so maybe I'm talking nonsens but does this help?

Sander

Hi Sander,

I was thinking along the same lines. This is what i have constructed so far. I know that the ManualID, TabID and ChapterID need to be in the Job's Table. But the problem is, how will one JobID account for multiple chapters that have been signed out. Every record will have a unique ID, but will only be able to house info for one chapter signed out.

I'm trying to move a set of chapters in a capsule with one unique ID through revision phases.

Please disregard the ManualID and TabID in the tbl_Jobs in the picture below. Don't quite know what i was thinking having them there...