1 Reply Latest reply on Aug 21, 2009 3:33 PM by etripoli

    Table design advice



      Table design advice


      Greetings folks!


      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 

        • 1. Re: Table design advice

          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