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

    Table design advice

    Nursedad

      Title

      Table design advice

      Post

      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
          etripoli
            

          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