Table design advice
I am in the process of scratching out my plans for a database that I need to create. This database will list a population of medical providers, dates of treatment, medical coding used on those dates of treatment and the charges for the services.
I will eventually be importing around 66,000 rows of data from an excel spreadsheet and want to make sure I get this developed correctly in the beginning so I can evaluate and report on this data.
As far as tables go, I know I'll need the following:
- Clinic Table, which will include the provider(s) who practice there. I plan on populating this with basic Demographic info (names / addresses / phone / etc...)
- Billing Table. This would be populated with medical codes and their associated charges
Treatment on each date of service generally has more than one medical code / charge associated with it. This is were I want to make sure I get it right. Am I good with just these two tables...the Clinic table being the parent and the billing table being the child? Should I create a third table that would be similar to an invoice-type of table...where each invoice could have more than one product?
Hope I'm being clear enough here. I need to be able to evaluate the coding and the associated charges and report on it by date of service and/or Clinic name. Just want to make sure I'm setting this thing up correctly.
Thanks in advance for your wisdom and insight!
- - Jeff
On my experience you'd likely need more tables:
Providers - In case providers move around between clinics
Clinics - Actual physical location
Patients - person/people receiving services
Services - list of services with codes and charges
Billing - records based on provider, clinic, patient, and service, with date
And possibly a table to keep track of invoicing