Medical billing Database Design

[Ray67]

Medical billing Database Design



Hello all

I have been only using access for about 6 months. We currently do billing for about 30 doctors. The billing system we use doesn’t have too many options for reports. I would like to build a database in access that would store all of the monthly charges, payments and adjustments by patient and provider. This would allow me to build all the reports in access.

1. Patient Demo: would included Provider Name, Patient Last Name, Patient First Name, DOB, Gender, Medical Record Number, Address, City, State, and Zip Code. The only reason this information would change due to human error. I would identify each patient with patient name, DOB and Medical Record.
2. Patient Visits: Provider Name, Patient Last Name, Patient First Name, DOB, Medical Record, Transaction Date (when charges were entering in the billing system), Appointment Date, CPT Code, Procedure, ICD9 Code, and Charge Amount. For each visits the patient can have about four charges. This information can change due to many reasons.
3. Payments: Provider Name, Patient Last Name, Patient First Name, DOB, Medical Record, Transaction Date, Appointment Date, CPT Code, Payment Type, Insurance Name, Method of Payment, Payment Amount.
4. Adjustments: : Provider Name, Patient Last Name, Patient First Name, DOB, Medical Record, Transaction Date, Appointment Date, CPT Code, Adjustment Type, Insurance Nam, Adjustment Amount,

I know the way this is setup is a violation of Normalization rules. The problem is that the data would be collected within three reports and imported. I still have to speak with the billing system people to see how they would be able to provide me with the data. Before I speak with them I would like to have a template setup. Since the data is going to be imported how do I go about storing data without braking normalization rule?

Thank you,

[June7]

Are those the table or report layouts?

If those are table structures, normalization is broken by all the duplicated data. The MRN is patient ID, so just save MRN in the child tables.

[Ray67]

They are table structures. How can i import data into access without duplicating data???

[June7]

Can use the import wizard to pick columns to import and save the import steps and should be able to repeat the operation without the wizard (I have never done) by code to automate the imports using the saved steps.

If all your data is going to be imported and not involve any entry/edit in the db, then don't worry about the duplication unless it kicks you over the 2gb Access file size limit.

[Ray67]

Going over file size limit is going to be an isuess. We work with over 30 doctor.

[June7]

2gb is a lot but if storage conservation by not duplicating data is not enough to stay under the limit, you maybe should be looking at MySQL backend for data.