The Area being Modelled is :-
Everardo's Database

Email from Everardo received April 10th. 2011
Barry,
Here is the latest model:
  Base Tables Model v00.007.dez

I may have to go with your idea though because there are too many status codes on the visit table.

The only question I have is how can we satisfy a query like this:
Select all visits where visitstatus="arrived" and treatmentstatus="not treated"

If we go with only one status column ?

It may require the addition of a log table where we store all the phases the visit went through and
join with it to satisfy the querying requirements?

Although not very elegant, the need for visit status, treatment status on the visit table may be a
requirement since there are a lot of queries that depend on such conditions being met;

If visit status='arrived' and treatment status = "treated" and billing status != "billed" then do this�..

Thanks,

Everardo


Email received April 7th
thank you so much. I have already created a first draft database based on it.
Now I am writing some quick and dirty prototype to see if the whole thing ties nicely together.

I don't know if you noticed but we use a table called chargecode to tie in the different types
of charges in use here today: namely workelements and cpt4.

Each department has a different work element table.
The structure is the same but the same code means different things.

Here is an example:



So, 101 may mean something else for department 10, for example.

Let me know what you thing about the several statuses on the visit, please.

Thanks,
Everardo




Everardo asks :-
Barry,

Here is what I need.

Please take a look at the requirements and then send me an estimate of how much would
it cost me to have you evaluate and review what I have.

I have sent you only $150 to cover for an initial review. I don�t know if that is enough?

I will have to pay for this myself because the department is having some issues with money.

As i told you in a previous email, I am trying to replace 12 badly designed ms access databases
with a sql server backend and .net ui.

The applications are quite similar to your Hospice Example in that, we have the following:

Glossary of Terms


Batch
A Batch just holds the next batch for the billing cycle, one per day, every day at 7 am.
When we bill, we need to increment the batch id so that each bill will be grouped by this number.



Business Rules

For each visit a staff member is assigned to the visit by the manager who "owns" the visit and either the same staff member or more than one who will actually treat the patient.




A Patient has one or more Insurance Carriers

A patient sees a Primary Care Physician (PCP) because of some Incident.

The PCP refers the patient to our Physical Therapy Department for care.

The referral authorizes the patient to see a Therapist for a number of Visits (say 12) using a primary carrier

Once the patient arrives at the clinic, the patient is "arrived" in the system and pays a copay

The therapist treats the patient and after that enters "charges" for the work done to the patient

The therapist clicks on a "validate" button to validate the visit and the charges

Once the therapist is done entering all the charges, he clicks in a validate button to validate the visit
and the charges.

This executes rules to see if the visit has the right authorization, if the visit date falls under the
proper insurance coverage, etc

If all is well, the visit becomes "Ready for Billing"

A Billing Person runs billing the next morning:

selects all the records that are "Ready for Billing" and clicks on the bill button

Alternatively, she may select some records from the visit table and put them on hold, for further evaluation
(they won�t be billed until they are switched back to ready for billing)

The system should check each visit and charges and if all is good, creates a "bill record" (in reality, it is
just a record to be sent to a downstream system for billing)

When the manager decides it is time to send the billing records downstream, she clicks a "send bill" button.

That is all.

Here is a model:

The problems I am facing now are the following:

They seem to insist on keeping some of their current structure and flow in place even though I think the design is not good.

For example:

on the visit table, they have a visit type field which can contain any of the following values:

As you can see, I think they are mixing visit statuses with billing status and types as well as treatment status. It is a mess.

I wonder if it would be better to add a status column to the visit table for each of
the different statuses and then create a Visit Status column and table to hold:
Cancelled
Kept (Arrived)
Pending
No Show
Rescheduled

An Authorization Status column and table to hold:

A Billing Status column and table to hold:
- Not Billed
- Billing in Progress
- Ready for Billing
- Hold
- Billed

And even a Treatment Status since only patients that were treated will be billed????

This way it would be easier to see on which phase of the whole flow would a visit be in.

I am divided on whether I should put all of the above statuses in the visit table or
create additional tables to mimic each phase in the life of the visit?

Please advise, I am under a lot of pressure to get this done right I think you are the
right person to help me.

Thank you,

Everardo

B. Barry says the Things of Interest are :-
1. Charges
2. Incidents
3. Patients
4. Primary Care Physician
5. Therapists
6. Visits
2. Others to be determined

C. Status fields are :-
B.1 Authorization
B.2 Billing
B.3 Visit

C. The Business Rules are as follows :-
1. To be determined.



 © DataBase Answers Ltd. 2011