What’s the best way to implement “Current Balance” ?

[Edo2008]

I’m building a small medical practice financial application for my
wife. This application will keep track of patients, their visits
(charges) and payments.
So, I have a table of “Visits” showing what procedures were performed
(for insurance purposes) and what the charges are… and have a separate
table showing any payments.

PATIENT TABLE: PatientID, Name, contact info etc.
VISITS TABLE: PatientID, Date, ProcedureID (Procedure Table has
costs).
TRANSACTION TABLE: PatientID, Date, Amount.

To print a statement at the end of a visit, I need to know what the
previous balance is for the patient. My options are:

1) Have an SQL statement which would sum all charges from the VISITS
table and subtract all Payments in the TRANSACTION table… or
2) For each payment, update a “Balance” field in the PATIENTS table.

Does anyone have any advice? It would seem that Option2 breaks the
“no redundant data” rule of databases… but option1 seems like a lot of
work each time.
Any help would be appreciated!
Thanks
-Ed

 

[Allen Browne]

Option 1 is better for the reasons you stated.
Option 2 is less reliable.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I’m building a small medical practice financial application for my
wife. This application will keep track of patients, their visits
(charges) and payments.
So, I have a table of “Visits” showing what procedures were performed
(for insurance purposes) and what the charges are… and have a separate
table showing any payments.

PATIENT TABLE: PatientID, Name, contact info etc.
VISITS TABLE: PatientID, Date, ProcedureID (Procedure Table has
costs).
TRANSACTION TABLE: PatientID, Date, Amount.

To print a statement at the end of a visit, I need to know what the
previous balance is for the patient. My options are:

1) Have an SQL statement which would sum all charges from the VISITS
table and subtract all Payments in the TRANSACTION table… or
2) For each payment, update a “Balance” field in the PATIENTS table.

Does anyone have any advice? It would seem that Option2 breaks the
“no redundant data” rule of databases… but option1 seems like a lot of
work each time.
Any help would be appreciated!
Thanks
-Ed