Bad Design Perhaps?

[jasonjthomas]

I have been working on the same database for about 3-4 weeks now--admittedly, my forte is not database design. I am now at an impasse that I cannot seem to get around.

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.

Any wisdom on this is appreciated. I am officially at wits end.

Thanks,
Jason

Jason J. Thomas

 

[BobRodes]

Ok. I'm not quite familiar with your semantics, so I'd prefer not to comment directly on your design. Here's how I would do it:

Kernel entities (basically, tables that represent stuff, abstract or otherwise):

1. Clients
2. Partners
3. Audits
4. Papers

Associative entities (tables that associate kernel entities with one another, when they are not in many to many relationship):

1. PapersAudits

And that's about it. Audits are the glue that holds the whole thing together, and that is as it should be, since audits are the deliverable of your business domain.

Ok. Now, let me see what in your design I can plug into my design. It looks like tlkpClients is Clients, tlkpPartners is Partners, tlkpDeliverables is Papers, and tblClientSetup is Audits. Now, it also looks like tblReports and tblDeliverables are both trying to be PapersAudits, but I can't tell for sure. I think you're breaking them apart because reports are internal and deliverables are what the client pays for. However, if you think about it, they both relate to an audit, and they both have much the same data, like what is it, who's responsible for it, when's it supposed to be done, who's it go to, and the like.

Now, if you have the exact same list of required reports, etc. you can set them up in the Audits table. Since each audit requires one of each, they are in one to one relationship with audits so they can go there. This is a highly inflexible arrangement, however; if your reporting requirements change you will have a very tough job implementing it. So, personally, I'd prefer to have the PapersAudits associative entity.

So, basically, you're on the right track. However, assuming I understand what your deliverables and reports are, I would consolidate these into one.

Finally, STOP thinking about how users will access the data. It is NOT related IN ANY WAY to the best way to store the data. (It is related to what data you will keep, of course, but again NOT to how you store it.) You can show your data any way you want, and you can do whatever you want with user input. Get your schema tightened up, and then investigate how to present to the user.

HTH

Bob

 

[jasonjthomas]

Bob:

Apologies for not responding sooner, but my other tasks at the office have precluded me from doing so until now.

I actually got a good bit of advice on this from another forum--thank God for the Internets--on how to approach this.

I consolidated my tblReports and tblDeliverables into one table, tblReports. For all essential purposes, these items are the same--they are just data. They may be different to the folks who do the work, but in the end they are just reports with a specific name.

Bob, you are absolutely correct on forgetting how my users will enter data. That comes when I need to design the forms. ;-) My issue has been determining the relationships, as accountants have a tendency to think in flat files, e.g., Excel. Now that I have that down, it is onto the forms for data input and queries to generate reports.

Your advice did help, though. As someone who has never been--nor does not see himself--as a DBA, I tend to need all the help I can get.

Thanks again,
Jason

Jason J. Thomas

 

[lespaul]

You may also find the fundamentals document below useful in determining if your tables are structured according to the norms.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[BobRodes]

That's a great document. In fact, I always recommend it, and the way I find it is by looking up one of Leslie's posts and copying the link in the signature into my own post.

Now, then, the simple way to put it all together is to boil everything down to one to many relationships. Here are a few rules:

1. One to one relationships just go together in the same table.
2. One to many relationships go in different tables.
3. Many to many relationships go in different tables, too, but also need another table that is in one to many relationship with each of them to "resolve" the relationship.

So, in your case, your relations probably are:

One Many
Partners Audits
Clients Audits
Papers PapersAudits
Audits PapersAudits

There may be a couple more, such as if one of your clients is assigned to a specific partner.

HTH

Bob

p. s. Now then, if you have a little spare time for something frivolous, you might be so kind as to check thread1551-120784, the case of the missing dollar, to see if my accounting for it is up to standard.

TIA,

Bob

 

[BobRodes]

that would be thread1551-1207841 actually.