I am hoping for some advice about changing the set up of a database to follow better design rules and habits. The database I am working on has slowly grown and needs some changes which is a good time to look at the basic structure.
The current requirement is that we receive a number of different documents which are audited for issues by the office staff. These issues are logged and then fed back to the source. There are a selection of specific documents which are logged in and each has a unique set of issues. New issues are sometimes discovered so these need to be added, new document types are sometimes added.
The workflow is -
Users receive document and log details about the document on a form which is stored in the main table. Depending on the document type a relevant subform is displayed with check boxes for each of the issues related to that document type. Users select the problems and then emails etc are generated based on the checked boxes. There is already auditing built into the database and that functions well at the moment, I have not included it in this design to keep it simple.
The current design:
tblDocumentLog - main table stores info about the document itself
DocID, pk
DocType (Doctype1, DocType2, etc)
FirstName
LastName
etc
tblIssueList - master list of all issues used to generate email replies
IssueID, pk (Issue1, Issue2, Issue3)
IssueDescription
IssueCriticalLevel
IssueEmailReplyText
tblDoctype1 - one of these for each of the document Types
ID, pk
DocID, fk
Issue1 (T/F)
Issue2
Issue3
tblDoctype2 - second doc type
ID, pk
DocID, fk
Issue4
Issue5
Issue6
When feedback is needed the users press a button on the subform and I use VBA to loop through the checked boxes on the subform and generate the string which goes in the email.
This is not very efficient and I cringe each time I edit the database knowing this is poor design. Each new document requires a new table, each new issue requires a database update for the end users (split database).
I want to redesign the database to better manage the data.
What I envisage is:
tblDocumentLog - main table stores info about the document itself
DocID, pk
DocType (Doctype1, DocType2
FirstName
LastName
etc
tblIssueList - master list of all issues used to generate email replies
IssueID, pk (Issue1, Issue2, Issue3)
IssueDescription
IssueCriticalLevel
IssueEmailReply
**RelatestoDocType - new field which specifies which doc type this issue relates to
tblDocumentIssues - stores all identified issues for documents in this table
DocIssueID, pk
DocID, fk
IssueID, fk
I would then use lookups to grab all the Issues relevant to that document and generate the email string.
Where I am struggling is creating the subform for the users to select the relevant issues. It is easy in the current design as I just add all the fields for the specific document type to its individual subform and when checked it adds a TRUE to that column. I am unsure how to achieve this with the new design. How do I make a subform which displays a list of issues for that document type for the user to check? I am still happy to have individual subforms for each document type but I am not sure how to create the checkboxes for something which doesn't "exist".
My questions:
Is this change a better way to design the database or am I doing it unnecessarily?
How do I create subforms with True/False controls which are linked to fields which dont yet exist?
Am I doing it completely wrong?
The current requirement is that we receive a number of different documents which are audited for issues by the office staff. These issues are logged and then fed back to the source. There are a selection of specific documents which are logged in and each has a unique set of issues. New issues are sometimes discovered so these need to be added, new document types are sometimes added.
The workflow is -
Users receive document and log details about the document on a form which is stored in the main table. Depending on the document type a relevant subform is displayed with check boxes for each of the issues related to that document type. Users select the problems and then emails etc are generated based on the checked boxes. There is already auditing built into the database and that functions well at the moment, I have not included it in this design to keep it simple.
The current design:
tblDocumentLog - main table stores info about the document itself
DocID, pk
DocType (Doctype1, DocType2, etc)
FirstName
LastName
etc
tblIssueList - master list of all issues used to generate email replies
IssueID, pk (Issue1, Issue2, Issue3)
IssueDescription
IssueCriticalLevel
IssueEmailReplyText
tblDoctype1 - one of these for each of the document Types
ID, pk
DocID, fk
Issue1 (T/F)
Issue2
Issue3
tblDoctype2 - second doc type
ID, pk
DocID, fk
Issue4
Issue5
Issue6
When feedback is needed the users press a button on the subform and I use VBA to loop through the checked boxes on the subform and generate the string which goes in the email.
This is not very efficient and I cringe each time I edit the database knowing this is poor design. Each new document requires a new table, each new issue requires a database update for the end users (split database).
I want to redesign the database to better manage the data.
What I envisage is:
tblDocumentLog - main table stores info about the document itself
DocID, pk
DocType (Doctype1, DocType2
FirstName
LastName
etc
tblIssueList - master list of all issues used to generate email replies
IssueID, pk (Issue1, Issue2, Issue3)
IssueDescription
IssueCriticalLevel
IssueEmailReply
**RelatestoDocType - new field which specifies which doc type this issue relates to
tblDocumentIssues - stores all identified issues for documents in this table
DocIssueID, pk
DocID, fk
IssueID, fk
I would then use lookups to grab all the Issues relevant to that document and generate the email string.
Where I am struggling is creating the subform for the users to select the relevant issues. It is easy in the current design as I just add all the fields for the specific document type to its individual subform and when checked it adds a TRUE to that column. I am unsure how to achieve this with the new design. How do I make a subform which displays a list of issues for that document type for the user to check? I am still happy to have individual subforms for each document type but I am not sure how to create the checkboxes for something which doesn't "exist".
My questions:
Is this change a better way to design the database or am I doing it unnecessarily?
How do I create subforms with True/False controls which are linked to fields which dont yet exist?
Am I doing it completely wrong?