Three Tables which needs more direct relationships! (1 Viewer)

[manix]

Hi,

I have three tables in a database simply designed to control document revisions.

1. The Document Register Table where each document has record.
2. The Revision Log Table which is directly related to the Document Register as each document could have multiple revisions- so One to Many.
3. The Change Request Table where change requests can be raised to up issue documents. This is also related to the Document Register as each document can have multiple change requests but it is also related to the revision log, as each change request will be related to a new revision of a specific document.

Now my dilemma is, that I have is that I cannot get the referential integrity to work on the document when I use the Change Request form because this is a revision log subform of the change request log, it has no direct relationship with Document Register. It will only work when used as a subform of the Document Register! It's almost like I need a fourth direct relationship?

I have attached a picture of the tables and their relationships.

How can I make this work, basically I need to be able to automatically update the revision log with DocID and the ID from the change request table.

Can anyone help?

 

[jdraw]

Perhaps you could tell us more about the "business" behind this proposed database. Can you tell us in simple, plain English what goes on in the "business" in a typical day/cycle?
What are the things involved? How are these things related in plain English? Refrain from database/Access jargon -- just simple English.

There are a number of free data models here --some related to document management.

 

[Galaxiom]

Do answer jdraw's question because I have not analysed your structure at all.

Meanwhile try this:

Drop the DocId relationship between DocID and ChangeRequest.
Drag the ChangeRequest in a second time. Connect this alias to DocReg on DocID.

 

[spikepl]

Your change requests, logically, are each related to a revision - you need to refer to a revision when you request a change! In fact they are related twice: the revision in which the change requests needs a change, and then the revision where the change was implemented - so you need two RevisionID's in the record of one change request.

So when issued, consider the first document a revision without any preceding ones. Drop the link between documents and change requests. You can can recover all change requests for a document by scanning which change requests relate to the revisions of the specific document.

 

[manix]

Perhaps you could tell us more about the "business" behind this proposed database. Can you tell us in simple, plain English what goes on in the "business" in a typical day/cycle?
What are the things involved? How are these things related in plain English? Refrain from database/Access jargon -- just simple English.

There are a number of free data models here --some related to document management.

Basically:

Documents exist in soft copy on a network drive.

The business must control them as part of their Quality Management System, this involves ensuring the same formats and information are available consistently and not subject to change, without review and approval. The register just houses each document and it's information, with a revision log that shows the docs history.

If you want to make a change to a documents, you have to go through an approval process, which is to raise a change request, circulate to the relevant people, then once agreed and the authorized person has approved, the change can be made and the revision log updated to the next revision.

I just cannot make that link between the revision log and the document via the change request form, if I simply keep the link an indirect one between the rev log and the change request, when you go into the doc register, the revision history will be incomplete, as the doc ID link is not updated in the change log.

I guess what I could do is simply have a simplified doc reg subform, on the change request form where you can update the rev log, but then I cannot maintain the link between the rev log and the change request!

 

[spikepl]

I just cannot make that link between the revision log and the document via the change request form, if I simply keep the link an indirect one between the rev log and the change request, when you go into the doc register, the revision history will be incomplete, as the doc ID link is not updated in the change log.

Explain. Why "cannot"? Either no changes to the system are allowed, in which case the entire discussion is moot, or you should fix the system to fit the structure of the data.

Changes are requested for a revision. So one or more change requests concern changes to the revision they relate to. Also, one or more change requests are satisfied by some subsequent revision. A document can have one or more revisions, so all change requests can be found by finding the revisions of a document and the related change requests. Where is the "cannot" in that? Or what have I misunderstood?

 

[manix]

Explain. Why "cannot"? Either no changes to the system are allowed, in which case the entire discussion is moot, or you should fix the system to fit the structure of the data.

Changes are requested for a revision. So one or more change requests concern changes to the revision they relate to. Also, one or more change requests are satisfied by some subsequent revision. A document can have one or more revisions, so all change requests can be found by finding the revisions of a document and the related change requests. Where is the "cannot" in that? Or what have I misunderstood?

OK, my dilemma is how the data gets recorded in the forms. The relationships in theory work if you manually enter the docID and the CR Number. However, I am using Subforms, which is fine but only works at one level:

If you have the Rev Log as a subform of the Change Request, you can enforce the referential integrity of the CR number.

If you have the rev log as a subform of the doc register it will record the docID in the rev log.

The dilemma is how you get this referential integrity for both at the same time? This is revision 2 for Doc x on Change Request 7!

 

[spikepl]

If you have the Rev Log as a subform of the Change Request

Huh?

One or more change requests request changes in one specific revision. So change requests are on the many-side, and the revision on the one-side. In a form/subform that means a revision is on the form and the pertaining change requests on the subform.

I think you still need to work on the concepts - what is really related to what - before diving into forms. Do not change data structure to fit some perceived need of a user interfarce. Set up data structure as dictated by the data, and the display that data in a way logical to the user. These two ways are not necessarily the same.