Database structure (1 Viewer)

[bilakos93]

Hello

I am in the process of creating a database for hospital ultrasound appointments
There are inpatients, outpatients. Being an outpatient means that you may be booked in a morning list (which is free of charge) or an evening list (which you'll have to pay a fee). We don't do evening lists yet but may do so in the future.
There are various deparments in the hospital that one can be inpatient in (medicine, surgery etc)
Obviously being an inpatient today means that you may come back at a later date as an outpatient and vice versa.
The endgoal is to generate reports with the daily schedule.
The things I want in the report are:
- patient details (name etc)
- type of scan (abdomen, neck etc)
- time of scan
- the doctor they are booked under (if any)
- whether it is an inpatient, morning outpatient or evening outpatient appointment. which I will use to group the appointments
- if it is an inpatient, which department are they from?

How should I approach it?
My thoughts are that I should make the following tables
tblPatients
tblExamType (morning OP, evening OP, IP)
tblScans (abdomen, neck etc)
tblCons (doctors names)
tblConsStatus (active, inactive) given that people retire, quit etc
tblBookings (patientID, ScanID, ConsID, Date/Time)

Does this make sense? Any ideas on how to display the department for the IP appointments?

Thank you

 

[GaP42]

While you have begun to identify some of the key tables for the business data you need to store, there are some additional considerations: like
- as a department in the hospital do you expect your patient data to be sourced from the patient administration system of the hospital, or will you be expecting to maintain your own copy of this data? (you may be able to employ a patient enquiry request/return message to the PAS: HL7 messaging). The scheduling systems for other departments in the hospital will have similar features you could check.
- will you need to hold information about the patient's health insurance / billing / payment arrangements?
- what patient demographics do you need?
- your doctors also have a schedule indicating availability/ unavailability, and potentially exceptions outside the normal schedule
- bookings/ appointments are allocated a status: Pending, confirmed, cancelled, completed.
- an appointment may involve more than one scan type.
ALSO
Will you need to know about who conducted the scan as opposed to the DR who reviewed?.
Does the ultrasound device need to be identified as used in the scan, does it need to be booked?
Do you need to track the provision of the report of the Consulting Dr to the referring Dr for the scan(s)? And track the referral (or requesting Dept) and purpose/reason?
Some basic technical considerations:
- how many users? how will they be accessing the appointment system (LAN / Web)? Is access control needed? Database support: are you going to be the developer and user support? Will the db be hosted on a department server? What db will you use (Access/jet, Access SQL Server, ...)
- do you need to keep data indicating who created the appointment record and when?

Just a start ...

 

[bilakos93]

Thank you

While you have begun to identify some of the key tables for the business data you need to store, there are some additional considerations: like
- as a department in the hospital do you expect your patient data to be sourced from the patient administration system of the hospital, or will you be expecting to maintain your own copy of this data? (you may be able to employ a patient enquiry request/return message to the PAS: HL7 messaging). The scheduling systems for other departments in the hospital will have similar features you could check.
- will you need to hold information about the patient's health insurance / billing / payment arrangements?
- what patient demographics do you need?
- your doctors also have a schedule indicating availability/ unavailability, and potentially exceptions outside the normal schedule
- bookings/ appointments are allocated a status: Pending, confirmed, cancelled, completed.
- an appointment may involve more than one scan type.
ALSO
Will you need to know about who conducted the scan as opposed to the DR who reviewed?.
Does the ultrasound device need to be identified as used in the scan, does it need to be booked?
Do you need to track the provision of the report of the Consulting Dr to the referring Dr for the scan(s)?
Some basic technical considerations:
- how many users? how will they be accessing the appointment system (LAN / Web)? Is access control needed? Database support: are you going to be the developer and user support? Will the db be hosted on a department server? What db will you use (Access/jet, Access SQL Server, ...)
- do you need to keep data indicating who created the appointment record and when?

Just a start ...

Thank you

You mentioned some indeed very interesting points but my main question is: how do I display on the report the department for the inpatients (medicine, surgery etc)?

Unfortunately the appointments are currently on paper, so no need for HL7 messaging or anything like that.
I'm actually working as a junior doctor and trying to make something useful for the secretaries so that they save time. So this is an internal thing.
The demographics I need are just name, insurance number and phone number. No need to hold anything related to health insurance, billing etc.
The doctors schedule is something that is dealt with prior to registering the appointment. I would love to hear your ideas though about how we could implement that maybe in the future.
I don't think having an appointments status will help at this stage. This database is purely for planning and not retrospective counting etc.
An appointment may indeed involve more that one scan type. How would we go about that?

You also mentioned some very interesting technical considerations.
I was thinking having a single file in a single computer but having something that could be used by different devices would be ideal!
Having a record of the person (and time) creating the appointment would be good but isn't a requirement either. What would you suggest?
From a quick research I just did, I saw that splitting the database and placing the backend in a shared drive would help. Do you have any better ideas? Free of charge of course

Thank you

 

[GaP42]

how do I display on the report the department for the inpatients (medicine, surgery etc)?

You should have some attribute which will indicate the source of the appointment: for outpatients you will have a referral from the GP. For internal, the source will be Hospital Dept. For an appointment of type Hospital Dept you will need a table to identify the Dept as an attribute in the Appointment. It will be null for Outpatient Appointment types, whereas you will have a Referring Dr ID for the outpatient appointments (and the appropriate table to hold the contact details etc of the referring Dr)

For an appointment you may have one or more scans conducted, so in order to appropriately record that requires a table to hold that data related to the scan.

I do think you will need to have an attribute to track the status of the appointment (even if not used at this stage)

Each of the tables can include an attribute that holds CreatedDt, ChangedBy and ChangedDt. Each of these can be automatically assigned a value as the user creates or changes a record (it does not track deleted records)

Access will support multiple simultaneous users with a split db and is a recommended arrangement.

While waiting/ thinking about the subject I drew up the following - the scope can be pared back to what you need and refined as you see fit.

HospitalDept and ReferringDr tables need to be added as per above discussion (relating to the Appointment table) - OR perhaps this might be better related to the Referral entity - where the referral may be internal or external.

 

[bilakos93]

You should have some attribute which will indicate the source of the appointment: for outpatients you will have a referral from the GP. For internal, the source will be Hospital Dept. For an appointment of type Hospital Dept you will need a table to identify the Dept as an attribute in the Appointment. It will be null for Outpatient Appointment types, whereas you will have a Referring Dr ID for the outpatient appointments (and the appropriate table to hold the contact details etc of the referring Dr)

For an appointment you may have one or more scans conducted, so in order to appropriately record that requires a table to hold that data related to the scan.

I do think you will need to have an attribute to track the status of the appointment (even if not used at this stage)

Each of the tables can include an attribute that holds CreatedDt, ChangedBy and ChangedDt. Each of these can be automatically assigned a value as the user creates or changes a record (it does not track deleted records)

Access will support multiple simultaneous users with a split db and is a recommended arrangement.

While waiting/ thinking about the subject I drew up the following - the scope can be pared back to what you need and refined as you see fit.

View attachment 119497
HospitalDept and ReferringDr tables need to be added as per above discussion (relating to the Appointment table) - OR perhaps this might be better related to the Referral entity - where the referral may be internal or external.

hello again!

Thank you for your input.
After having a thought, I created an extra table (tblHospDept: HospDeptID, HospDept), added a field on tblBookings (HospDeptID) and I also put a field on the form underneath the rest of the appointment stuff (date, type of scan etc).
I have changed the visibility of this field to no and added this on the after update of the combobox exam_type_id as well as on the current of the subform:

If Me.exam_type_id.Value = "1" Then
Me.HospDeptID.Visible = True
Else
Me.HospDeptID.Visible = False
End If

So whenever the user chooses Inpatient from that dropdown list the field HospDept will show on the screen.
The report now will group by exam_type and then by HospDept. The only drawback is that there is some blank space underneath the word "Outpatient" (which is the title of the group) as there is no subgrouping there but who cares.

Still need to find something about people with multiple scans at once. For the time I'm using seperate entries for each scan.

 

[Gasman]

That is how it should be. One scan is one record.

 

[bilakos93]

That is how it should be. One scan is one record.

thank you.
Do you think there is a way to make it more friendly to the eye on the report that is then printed?
Maybe group by name also?

 

[Gasman]

On the report I would have header details, name, doctor whatever you need, then a detail with all the records, or a sub report to show all the apps details, removing anything that is duplicated.

 

[mike60smart]

hello again!

Thank you for your input.
After having a thought, I created an extra table (tblHospDept: HospDeptID, HospDept), added a field on tblBookings (HospDeptID) and I also put a field on the form underneath the rest of the appointment stuff (date, type of scan etc).
I have changed the visibility of this field to no and added this on the after update of the combobox exam_type_id as well as on the current of the subform:

If Me.exam_type_id.Value = "1" Then
Me.HospDeptID.Visible = True
Else
Me.HospDeptID.Visible = False
End If

So whenever the user chooses Inpatient from that dropdown list the field HospDept will show on the screen.
The report now will group by exam_type and then by HospDept. The only drawback is that there is some blank space underneath the word "Outpatient" (which is the title of the group) as there is no subgrouping there but who cares.

Still need to find something about people with multiple scans at once. For the time I'm using seperate entries for each scan.

Can you upload a copy of what you have created so far?

 

[GaP42]

Still need to find something about people with multiple scans at once. For the time I'm using seperate entries for each scan.

Yes: as shown with the table structure, a single appointment can have one or more scans (of same or different types).
Your form, displaying the appointment, will allow the addition of one or more scans to be performed. and post the appointment, the scans performed (usually as a subform) at the appointment.

Re making the report friendly to the eye
Report data can easily be ordered and grouped using the criteria you specify - we don't know what the report is that you need. Is it a list of all appointments and the scans scheduled for a date by Dr?
If so, you will require data that selects records by Appt (including Dr ID, Scheduled Date, scheduled time, PatientID) picking up patient Name, Scans (scan type), DrName
Order by Dr Name, Scheduled Date, ScheduledTime (if separate to date).
- For Appt ID - the list of ScheduledScans - order by type
Is this what you mean?