jasonjthomas
IS-IT--Management
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
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