Question

What table might the database need to help determine the balance field in the Patient table? Would you want to record when a bill is paid and the amount? How would the insurance part of the bill be recorded? 

Answer 1

You should have an additional table that records each Patient Number, the date and the amount paid for the outstanding bill. That amount would then be subtracted from the Patient’s balance. When the Patient’s bill is generated, and that most likely would be in an additional table, the portion that the insurance company is billed should be included as a field.

Add a table for patient payments, deduct payments from balance; include insurance billing in bill generation.