I�m in the early stages of design (or redesign) of a client tracking database for a non-profit Agency. The existing database was originally written in PAL (Paradox) over 15 years ago, and some updates were done in Delphi. (Note: everything currently is in two primary tables and many lookup tables: There is a �clients� table with 293 fields containing all information about the client; and a �contacts� table with 37 fields containing information on each contact or service provided. There are approximately 19k client records, and 350k contact records, all going back to 1997.
I�ll be breaking those tables into smaller groups, and later will be working to add three other databases (one from paradox, one from access, and one done in Excel spreadsheets) that are used in other divisions of the Agency, so their data will be integrated. I�m probably going to be over my head a lot, so I�m sure I�ll have more questions. (UBI #1: (useless bit of information) 22 users plus managers use this database at main office, on a network. Three users at outlying office enter data in a database that contains a subset of the data on their stand-alone computers. So as I move forward at least the data-entry portion of this will have to be available on the web; as far as I know at this point all reporting functions are done by managers in the main office.)
So far, it�s actually going fairly well, but there is one table I am having trouble getting my head around. I want to see if I�m on the right track before I get too far along.
The specific service program has over 12 funding sources, and the Agency receives a �fee for service�. Each client is supported by only one of the fund0rs, and there can be up to five different types of service provided to each client. Each client has one primary counselor, but some of the services may be provided by another counselor. Each fundor has a set fee for each type of service they support. The fees are per unit (hour or half-hour, depending on the fundor), and services may be for multiple units on any day. The fee amount is different for each funding source, the fee amount can change from one year to the next. A significant complication is that occasionally (it has occurred 8 times in 10 years, so it is rare but must be �do-able�) the fee can be renegotiated during the funding year.
So client �steve� might receive service type "group counseling" over a two month period, and the fee the Agency received could potentially be 3 different fees.
In the old system (apparently the person who originally put this together had �a hammer, so the whole world looked like a nail�) the reports showing fees receivable are done by hard coding the fee into Paradox �query by example�, and when errors occur because of the conditions stated above, the reports are manually edited.
The fee lookup table is simple, I think:
What I�m not clear on (and it might become clear when I move beyond design and start putting this together, so I may be �putting the cart before the horse) is how I will relate each service instance to the correct fee. (Note: each client can receive a given service type only once on a date; they may receive more than one service type on the same date.)
Is it as simple as something like (pardon my really bad SQL knowledge):
Select fee where Service_Date is equal to or greater than begin_date and Service_Date is equal to or less than end_date?
Would something like that give me a report that showed 3 different fees for the same service if that was the situation for the given dates of the report?
I�ll be breaking those tables into smaller groups, and later will be working to add three other databases (one from paradox, one from access, and one done in Excel spreadsheets) that are used in other divisions of the Agency, so their data will be integrated. I�m probably going to be over my head a lot, so I�m sure I�ll have more questions. (UBI #1: (useless bit of information) 22 users plus managers use this database at main office, on a network. Three users at outlying office enter data in a database that contains a subset of the data on their stand-alone computers. So as I move forward at least the data-entry portion of this will have to be available on the web; as far as I know at this point all reporting functions are done by managers in the main office.)
So far, it�s actually going fairly well, but there is one table I am having trouble getting my head around. I want to see if I�m on the right track before I get too far along.
The specific service program has over 12 funding sources, and the Agency receives a �fee for service�. Each client is supported by only one of the fund0rs, and there can be up to five different types of service provided to each client. Each client has one primary counselor, but some of the services may be provided by another counselor. Each fundor has a set fee for each type of service they support. The fees are per unit (hour or half-hour, depending on the fundor), and services may be for multiple units on any day. The fee amount is different for each funding source, the fee amount can change from one year to the next. A significant complication is that occasionally (it has occurred 8 times in 10 years, so it is rare but must be �do-able�) the fee can be renegotiated during the funding year.
So client �steve� might receive service type "group counseling" over a two month period, and the fee the Agency received could potentially be 3 different fees.
In the old system (apparently the person who originally put this together had �a hammer, so the whole world looked like a nail�) the reports showing fees receivable are done by hard coding the fee into Paradox �query by example�, and when errors occur because of the conditions stated above, the reports are manually edited.
The fee lookup table is simple, I think:
Fundor_ID
Fundor
Service_Type
Fee
Begin_Date
End_Date
Fundor
Service_Type
Fee
Begin_Date
End_Date
What I�m not clear on (and it might become clear when I move beyond design and start putting this together, so I may be �putting the cart before the horse) is how I will relate each service instance to the correct fee. (Note: each client can receive a given service type only once on a date; they may receive more than one service type on the same date.)
Client_ID
Service_Date
Service_Type
Duration
Counselor
Service_Date
Service_Type
Duration
Counselor
Is it as simple as something like (pardon my really bad SQL knowledge):
Select fee where Service_Date is equal to or greater than begin_date and Service_Date is equal to or less than end_date?
Would something like that give me a report that showed 3 different fees for the same service if that was the situation for the given dates of the report?
Comment