Database structure (2 Viewers)

bilakos93

Member
Local time
Today, 09:57
Joined
Aug 25, 2023
Messages
31
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
 
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 ...
 
Last edited:
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
 
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.

Hospital Ultrasound ERD.jpg

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.
 
Last edited:
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.
 
That is how it should be. One scan is one record.
 
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.
 
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?
 
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?
 
Last edited:
I also put a field on the form underneath the rest of the appointment stuff (date, type of scan etc).
I missed this earlier: If this is all you have to support the scans to be performed at an appointment then you will only be able to indicate one type of scan for each appointment. Earlier you indicated that you can have more than one scan scheduled at an appointment. If so then this is a fundamental flaw in the design (a failure to apply appropriate normalisation rules). You should add the scan table (scanID (as PK), ScantypeID (FK to scan types table), ApptID (as FK to the Appointment) and scan notes/details: bodycomponent, laterality etc - as needed. The date of the scan is the date/time of the appointment.
If you expect to never need to count the scans of a type over a date interval then perhaps you can simply have a long text field to record the scans needed at the appointment in a data clump - but do not expect to analyse on the basis of that content - it will not be standardised.

Suggest you post up the Relationships diagram from your Access db for review by others \ further input

An area to explore is Dr Availability when making appointments. Drs may have various standard booking times - a table to support these might have a structure like: AvailabilityID, DrID (FK to dr), AvailableDate, Fromtime, ToTime, which gets populated periodically as new periods for making appointments become available. Dates may be removed for leave periods (or you might have a flag to indicate unavailable/ unavailable reason). This table, and the appointment table are used to determine if the Dr is available and if they are not already booked for an appointment timeslot.
 
Last edited:

Users who are viewing this thread

Back
Top Bottom