Bad Table Design?

[Jason J. Thomas]

I wonder if I am needlessly complicating a database design. I have
tried posting in different places, but I have had little success in
getting a response. Here's hoping the wise folks here are able to get
me moving in the right direction.

I have a database that is being used to maintain the status of various
items that are due for the auditing engagements our firm performs. To
look at this abstractly, there is a client, a partner that is
responsible for the audit engagement, and a series of
forms/letters/reports that are required. Essentially they want to track
the status of these various forms and reports in a database. The goal
here is to know what is outstanding, who is accountable, and browbeat
accordingly. ;-)

In my design currently, I have three tables and three lookup tables.
tlkpClients--supplies ClientID and ClientName
tlkpPartner--Partner Name
tlkpDeliverables--Deliverable Reports Available

I then have three tables to track the initial client setup, the reports
that are required, and the deliverables that are required. Here is the
design I have thus far:

tblClientSetup
ClientSetupKey (Prim Key)
ClientID (lookup from tlkpClients)
PartnerName (lookup from tlkpPartner)
BackgroundCheckStatus (Yes/No)
BackgroundComplete (Date)

tblReports
ReportsKey (Prim Key)
ClientSetupKey (Foreign Key)
Report1Status (Yes/No)
Report1Complete (Date)
Report2Status (Yes/No)
Report2Complete (Date)
....

tblDeliverables
DeliverableKey (Prim Key)
ReportsKey (Foreign Key)
DeliverableType (Lookup from tlkpDeliverables)
DeliverableTitle (Text)

As of this writing, my relationships are:
tblClientSetup --> tblReports
1-Many Relationship between ClientSetupKey in both tables
tblReports --> tblDeliverables
1-Many Relationship between ReportsKey in both tables

Essentially, I want the data in tblReports and tblDeliverables to be
tied to tblClientSetup. My thinking here is with forms, those folks
doing data entry can merely select the appropriate ClientID and get the
form populated with what has been done so far.

Am I needlessly complicating this setup, and do I have my relationships
completely wrong? I am stuck as in my previous two attempts at this I
have not been able to do this.

Thanks for any wisdom on this,
Jason

 

[mscertified]

I looked at your design and am a bit perplexed about your requirements.
The concept of reports and deliverables confuses me - they sound like the
same thing.
The first thing I see is that you have multiple reports in your Reports
table. That is not good. You need a separate record in this table for EACH
report not columns for Report1, Report2 etc. I'd have a ReportNumber or
ReportName column to distinguish the reports.
Assuming deliverables are related to reports rather than clients, the rest
looks ok. The relationships should be one to many in both cases. One client
to many reports and one report to many deliverables.
The rest is just a case of setting up your forms and subforms with the
appropriate data sources based on queries.

Dorian

 

[Jason J. Thomas]

Dorian,

That's what I thought as well. However, the reports are mainly forms
that are required of the engagement, while the deliverables are things
like tax returns, financial statements, etc. I think the distinction is
a thin one, but I am not an accountant/auditor (although I play an IT
auditor on TV ;-) ).

Allow me to unpack your idea on the Reports table. From what you are
saying, I should have a table that looks like this:

tblReports
ReportsKey (Prim Key)
ClientSetupKey (Foreign Key)
ReportName (lookup from new table called tlkpReportNames)
ReportStatus (Yes/No)
ReportComplete (Date)

Based on your suggestion, this make perfect sense (especially in a
one-to-many relationship. Also, I am thinking of moving the Background
Check to the Reports table as well, given it is a form that is being
maintained. My thinking is to make the tblClientSetup table a bit
cleaner.

Further thinking on this, I can probably do away with the distinction
between Deliverables and Reports. I can put everything into one lookup
table, and let tblReports get populated with the appropriate data.

This actually makes this a bit less complex than I had originally thought.

Jason

 

[mscertified]

Yes, your Reports table design now looks good.

The key question is: are Deliverables related to ClientSetup or to Reports?
In other words, do you have multiple Deliverables per ClientSetup or
multiple per Report? Combining them into one table makes sense if they are
really the same thing and it does seem that you'd want a status and a
completion date for the deliverables. Sometimes you have to talk to the
actual people who do the tasks to figure out what is related to what.

Good luck.

Dorian.