423,103 Members | 1,428 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,103 IT Pros & Developers. It's quick & easy.

Best Way to Design Task Tracking Table

twinnyfo
Expert Mod 100+
P: 2,499
Cohorts in Code,

Our office sends out a series of correspondence related to Air Force Officer Promotions. This correspondence is all related to a specific "Project" (ProjectID) that we are working. Some of this correspondence is information only, so we only need to track "that we sent it" and the date we sent it.

Other correspondence requires a response from each of the customers to which the Project applies (these customers are referred to as "Senior Raters" in our world - [SRID]). For this correspondence, we need to know "that we sent it" and when we sent it, but we also need to know that the SR has responded to the correspondence (response date is immaterial). Some correspondence applies only to certain SRs, so we would also keep track of that.

So, to explain the nature of this question, here is the intent, or vision, of what we want to do: As we send out correspondence, this table (that has yet to be designed and built) is updated to reflect "that we sent" a particular piece of correspondence and the date. If the correspondence requires a response, it will also identify which SRID requires a response. If it is Info-Only, just an update "that we sent it" and a date.

Ultimately, I want to build a dashboard that lists all SRs, and a "grid" showing the various correspondence sent and which SRs have responded. Additionally, we want to be able to "re-send" the correspondence and check for all those who have yet to respond and send to only those who have not responded.

Now, I am not asking for responses or recommendations for all of this, but more specifically, the design of the table. I see two possible methods for building this table, which each have their advantages and disadvantages, but I ask for some advice or some "outside the box" thinking, for ideas I may not have considered.

My first possible design goes like this: A "wide table" which reflects the ProjectID, SRID, and the types of correspondence sent, with appropriate dates. For Info-Only correspondence, there would be no response field, and sending the sorrespondence would update every SR's record to reflect "Sent" and "Date":

Expand|Select|Wrap|Line Numbers
  1. tblCorrespondence
  2. Field      Type  Description
  3. CorrID     Long  PK
  4. ProjectID  Long  FK to Projects
  5. SRID       Long  FK to SRIDs
  6. CorrA      Y/N   Indicates Correspondence A sent
  7. CorrADate  Date  Date Correspondence A was sent
  8. CorrAComp  Y/N   Did the SR respond?
  9. CorrB      Y/N   Indicates Correspondence A sent (Info only)
  10. CorrBDate  Date  Date Correspondence A was sent
  11. CorrC      Y/N   Indicates Correspondence A sent
  12. CorrCDate  Date  Date Correspondence A was sent
  13. CorrCComp  Y/N   Did the SR respond?
  14. Etc...
The main advantage to this design is that it becomes very easy to now show at a glance which correspondence went out and who has responded. However, it is terribly un-Normalized, and Info-Only correspondence would then update the 50 SR records just to say we sent out a note. There are also challenges with the correspondence that goes out to individual SRs. This would require me to update the response to "Yes" for all others and leave the specific SR as "No" until they respond. I'm fine with the lack of normalization, as these records are simply deleted once a project is complete, but it does get under my skin of the "messiness" of it all.

The other potential design I have been thinking about is a more properly normalized "tall" design, made from two (maybe three) tables. One table would indicate the Correspondence type and who receives it. When we build the project, we also append the records to the correspondence table, based upon the requirements of the first table.

Expand|Select|Wrap|Line Numbers
  1. tblCorrTemplate
  2. Field     Type       Description
  3. CorrID    Long       PK
  4. CorrName  ShortText  Name of Correspondence
  5. CorrDesc  ShortText  Brief description of Correspondence
  6. Audience  Integer    Describes who the audience is
  7.                      (could be FK to tblAudience if I choose)
  8. Response  Y/N        Indicates if a response is required
Then, the corresponding table:

Expand|Select|Wrap|Line Numbers
  1. tblCorrespondence
  2. Field      Type  Description
  3. CorrID     Long  PK
  4. ProjectID  Long  FK to Projects
  5. SRID       Long  FK to SRIDs
  6. CorrID     Long  FK to tblCorrTemplate
  7. CorrDate   Date  Date Correspondence was sent
  8. CorrComp   Y/N   Did the SR respond?
Obviously, one advantage is that this is much more normalized. The only extraneous data is the CorrComp field which doesn't apply for Info-Only correspondence. The big challenge is converting this table back again into a grid that displays all SRs with the status of their correspondence (we would also need to log in their responses in this table/grid display). Additionally, there are nearly 30 pieces of correspondence we send out via the DB to up to 50 different SRs for each project. So, in the first design, we just create 50 records. This second design could create nigh unto 1,500 records for each project! However, it still might be the better solution.

As usual, I recognize that I often have blinders on and am unable to see some things conceptually at first--so I may be overlooking a very simple solution to the design of this table. I am more than happy to provide any additional informaiton concerning this design if needed, but I hope I have provided enough information to get the conversation started.

I can foresee that I will probably learn something in this process, as well!

Thanks for any hepp y'all can provide.

Grace and peace!
5 Days Ago #1

✓ answered by PhilOfWalton

Have a look at this

Phil

Share this Question
Share on Google+
3 Replies


PhilOfWalton
Expert 100+
P: 1,257
Have a look at this

Phil
Attached Files
File Type: zip Gregory.zip (33.6 KB, 4 views)
5 Days Ago #2

twinnyfo
Expert Mod 100+
P: 2,499
Phil,

I see what you are doing there and that was just the "outside my blinders" thinking I needed. I can use this--maybe not bit for byte, but the principle is exactly what I was looking for.

Thanks so much!

For those who don't wish to download Phil's DB template, here is the skinny:

He's used the table design for option number 2 above. However, for the "display" he uses a main form plus two subforms. The first subform holds the Correspondence Template (parent-child on ProjectID). As one selects a different piece of correspondence, the list of SRs who need to respond is updated to show the current status.

Very "simple" if you ask me, but I was simply not seeing that as an option.
5 Days Ago #3

PhilOfWalton
Expert 100+
P: 1,257
I only do "Simple"

I like your description of the Db. Spot on.

Anyway, it kept me amused for an hour or so.

Glad to have given you an idea,

Phil
5 Days Ago #4

Post your reply

Sign in to post your reply or Sign up for a free account.