Hello everyone. Hope your holidays are going well. I have been given the task of developing a DB that will be used to generate reports for scheduling services for clients. These are medically related services and are provided around the clock. I have divided the Services into two broad categories A&B for ease of discussion and I think it helps in the DB design. The reports will display client information, services, service detail, appointment frequency and times. The reports will be printed in duplicate, with one copy for technician providing service and the other for book keeping purposes. I have it in 5 tables:
1.
tblClient
Auto#-pk
Client ID
Client Last name
Client First name
Client Location
(pretty straight foward, single client, main table)
2.
tblServices
ServiceA1-5&B1-5
(Table with a complete list of all the services offered. Clients can have up to abut 6 different services at once of several different types. The services aren't really related to one another - for DB purposes...I think
)
3.
tblServicesA
Auto#-pk
ServiceA1-5
Frequency
(The services provided here are really more of a maintenence type of service which occurs about 4times/day around the clock (known as the frequency). The technician observes the client, if service is being utilized,then the time listed on the report will be crossed out, if the service is not used the time will be circled on the report. Services in the A category are very straight foward. Each service has a frequency at which they are checked (as stated above 4 times/day for Service A), technician checks client, if service used, service is charged. Again, pretty straight foward, but wait till we get to ServiceB. The patient can have up to 3 services from this category on their report)
4.
tblFrequency
Frequency
Times
(This table is for the different frequencies at which all the
services are checked +/or performed. Each service in ServiceA category has only one frequency (4 times/day) we'll call fID. In ServiceB category, each service is associated with one (actually two, but we'll get to that later) frequency. In this category, there are about 13 different frequencies to choose from. All frequencies are associated with one set of times. For example: fID is associated with 0600, 1200, 1800 & 0000. Again, these times will be displayed on the reports used by the technicians (the frequency will also be displayed on the report). The frequencies of ServiceB are subject to change. When this happens, the technician makes appropriate changes on current report, then will update the frequency for future reports. Wasn't sure if a PK was necessary here.)
5.
tblServiceB
Auto# - PK
ServiceB1-5
Frequency
Medication
Dosage
(ServicesB3-5 are similar to ServicesA in that they are only associated with a single frequency that will not change. So on the report the service and frequency will be displayed.
ServiceB1 is not too difficult, but instead of having a timed freq/day, it only occurs on a 72hr basis. What needs to be displayed on the report will be the next due date of the service. I was hoping that the technican could just refer to order summary to select a date that the service will need to be 'observed', and enter this data as text in a field on a form. When the service is observed, it then needs to be observed again another 72 hours later, so the date will need to be changed. The problem is, that there are times when the observation of this service occurs before the 72 hrs is up, and times when it is done later than 72hrs, so I can't have the date automatically updated.
ServiceB2, a little tricky. ServiceB, like all the other services will have a single frequency which when selected will display the times the service needs to be performed on a report for technicians to refer to. It also has a secondary frequency that will also need to be displayed on the report, but only as a free text. There are not specific times that are associated with this secondary frequency, and like the date in ServiceB1, I was hoping it could be entered as text in a field on a form. ServiceB2 is also associated with a medication (in some
cases up to 3 medications). Each medication has a single dosage.
I'll end it here, hope the explanation doesn't make it seem any more complex than it really is.
Although I have been doing alot of research in MSA recently, I'm still very much a noob. Thanks for any help.
Frank
1.
tblClient
Auto#-pk
Client ID
Client Last name
Client First name
Client Location
(pretty straight foward, single client, main table)
2.
tblServices
ServiceA1-5&B1-5
(Table with a complete list of all the services offered. Clients can have up to abut 6 different services at once of several different types. The services aren't really related to one another - for DB purposes...I think
)3.
tblServicesA
Auto#-pk
ServiceA1-5
Frequency
(The services provided here are really more of a maintenence type of service which occurs about 4times/day around the clock (known as the frequency). The technician observes the client, if service is being utilized,then the time listed on the report will be crossed out, if the service is not used the time will be circled on the report. Services in the A category are very straight foward. Each service has a frequency at which they are checked (as stated above 4 times/day for Service A), technician checks client, if service used, service is charged. Again, pretty straight foward, but wait till we get to ServiceB. The patient can have up to 3 services from this category on their report)
4.
tblFrequency
Frequency
Times
(This table is for the different frequencies at which all the
services are checked +/or performed. Each service in ServiceA category has only one frequency (4 times/day) we'll call fID. In ServiceB category, each service is associated with one (actually two, but we'll get to that later) frequency. In this category, there are about 13 different frequencies to choose from. All frequencies are associated with one set of times. For example: fID is associated with 0600, 1200, 1800 & 0000. Again, these times will be displayed on the reports used by the technicians (the frequency will also be displayed on the report). The frequencies of ServiceB are subject to change. When this happens, the technician makes appropriate changes on current report, then will update the frequency for future reports. Wasn't sure if a PK was necessary here.)
5.
tblServiceB
Auto# - PK
ServiceB1-5
Frequency
Medication
Dosage
(ServicesB3-5 are similar to ServicesA in that they are only associated with a single frequency that will not change. So on the report the service and frequency will be displayed.
ServiceB1 is not too difficult, but instead of having a timed freq/day, it only occurs on a 72hr basis. What needs to be displayed on the report will be the next due date of the service. I was hoping that the technican could just refer to order summary to select a date that the service will need to be 'observed', and enter this data as text in a field on a form. When the service is observed, it then needs to be observed again another 72 hours later, so the date will need to be changed. The problem is, that there are times when the observation of this service occurs before the 72 hrs is up, and times when it is done later than 72hrs, so I can't have the date automatically updated.
ServiceB2, a little tricky. ServiceB, like all the other services will have a single frequency which when selected will display the times the service needs to be performed on a report for technicians to refer to. It also has a secondary frequency that will also need to be displayed on the report, but only as a free text. There are not specific times that are associated with this secondary frequency, and like the date in ServiceB1, I was hoping it could be entered as text in a field on a form. ServiceB2 is also associated with a medication (in some
cases up to 3 medications). Each medication has a single dosage.
I'll end it here, hope the explanation doesn't make it seem any more complex than it really is.
Although I have been doing alot of research in MSA recently, I'm still very much a noob. Thanks for any help.
Frank
