Database structure

[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?

 

[GaP42]

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.

 

[LarryE]

You may be able to use a design like this:

An query example of a patient with two separate scan appointments would look like this:

Mr. Starkey has a head scan ordered by Dr. Jones in the Orthopedics department at 3:00 PM on 4/23/2025 and a back scan from Dr. Smith in Oncology at 4:00 PM.

Is this something like what you need?
Do you also want to know which front desk assistant entered the data and when?

 

[GaP42]

@LarryE - the design also needs to include the sonographer/radiologist who is being engaged for the appointment, not just the Dr from the Department who ordered the ultrasound (the Dr ID in ScanTime can't be used for both - and it is the schedule for the Dr performing the scan that is the critical element for the app). This also raises the question for the OP - for an appointment, is the Dr engaged (scheduled) for that appointment or for the individual scan? Does the scheduled Dr perform all scans at the appointment? Are there exceptions? The answer will affect the relationship of scheduled Dr to an Appt or to a ScanTime
Also patients are not just inpatients. There is the assumption that the OP made that certain sessions are for Outpatients and others for Inpatients, however I suspect that is a rule that can be broken, so a flag to indicate an inpatient/outpatient appt is needed - altho that can be derived from the fact that when an appointment is requested, if the order comes from a Dept Dr then it must be an inpatient, and all outpatients require a referral from their Dr (external practice) to make the appt.
I would think the scan would also require some detail about what scan(s) are to be performed: the part of the body, the laterality.

 

[LarryE]

@bilakos93:
In addition to the questions asked in post #12, there is the issue of accidental double bookings for the scan date and time. Also, if you have more than one scan machine, then there needs to be a table that holds scan machine or specific scan room location information. So I will include the ability to specify scan room location. Even if you only have one machine now, the hospital might have multiple machines in the future and perform scans in multiple rooms.

We still need to know if specific user at the front desk is important information you need.

 

[bilakos93]

Hello everyone

I'm attaching the database after having removed any sensitive information.
Just a few points to mention
- the main point now is to have multiple scans under one appointment. I'm guessing this will mean creating a new table . My main worry is that I have to make it presentable because the people using the database have very little knowledge on using computers
- the ReferringDr is something that is NOT been used. So, no need to add anything related. There are only Doctors performing scans and I refer to those as Cons. There are no sonographers in my country.
- the frmPatients is where the users book the appointments. There is some vba code behind some of those buttons/fields. When selecting "Inpatient" from the combo box, the hospDept combobox appears. Otherwise it is hidden.

Another thing that I think would be useful is a searchbox (with search while typing) and then being able to select this patient straight from there somehow and then booking an appointment. Scrolling through a big amount of data is a hassle and will discourage people to do so when a patient returns for a scan after sometime. Users will instead create duplicate patient entries unfortunately.

 

[GaP42]

A quick review:
You have a table tblScans - which describes the types of Scans. The FK to scantype in tblBookings means you can only have one scan type for a booking. As multiple scans are possible for a single booking you must have a table tblScanBooking which lies between these two tables: 1:M relationship tblScan to tblScanBooking and 1:M for tblBooking to tblScanBooking.
The tblScanBooking has ScanBookingID (autonumber), ScanID (FK to tblScan), BookingID (FK to tblBooking)
- then you will need a subform on the booking form that will list the scans scheduled for the appointment

- You have an ExamTypeID field in tblBooking - but does not link to anything - this is unneeded if the above is used, and unneeded unless there is something not shown yet.


For a search as you type - suggest you incorporate the FAYT (find as you type) function as published by @MajP on this site:

Find (Filter) as You Type Controls (Combobox, Listbox, Form)

Here is an Find/Filter as You Type (FAYT) class. This allows you to find/filter lists of comboboxes, listboxes, and continuous forms. The class helps create the capability with a single line of code to instantiate the class. You need to import the class module. Then instantiate a FAYT...

www.access-programmers.co.uk

Incorporate this in a Search combo (unbound) on the form where you expect the users to search for a patient. It can include more than one field in the FAYT: firstname lastname, amka, phone ..

You may still want to add 3 attributes to each table: CreateDt (date type), ChangeDt (date type), ChangeBy (text)
ChangeDt - in the table design set default to Date()

In the Before Update event of each form where a record is updated, use the following vba to capture the datetime the record was updated and who changed the record. The referenced controls can be hidden.

Me.changedt = Now()
Me.changeBy = environ("Username")

this can give some traceability as to who did the last change and when - which can help pinpoint who needs some further training if data quality issues are found/persist in the data.
(minimum apply to tblPatient, tblbooking and tblScanBooking (in addition to those suggested above))

 

[GaP42]

Quick question @LarryE
- why have an inpatientDept in the patient record? (no relationship to Dept - perhaps it needs the Patient-Department table to resolve a M:M relationship of patient and Department that is not shown?)
- the relationship of Scanlocation to Department to Doctor allow the "cons" (consulting dr) performing the scan to be assigned to the scan in a room/location. It will not support the Dr or Department referring the patient for the scan as an inpatient. Important info.
- scanTypeId is a FK in ScanTime will be supported by a ScanType table

 

[LarryE]

why have an inpatientDept in the patient record?

@GaP42 :
According to the OP, that is where the designation belongs. A patient may be an Inpatient or not. If they are, you select a department for that

the relationship of Scanlocation to Department to Doctor allow the "cons" (consulting dr) performing the scan to be assigned to the scan in a room/location. It will not support the Dr or Department referring the patient for the scan as an inpatient. Important info

Correct. That was also what the OP wanted.

scanTypeId is a FK in ScanTime will be supported by a ScanType table

Yes, but no RI is needed here. Same with the Doctor ID. These are not required by ACCESS, but the hospital procedures may require them. That is up to the OP to determine. The ScanType is an interesting thing. I am now wondering if it really should be just a field to be completed when the scan order is input and not a separate table at all. I even removed it from the relationships window. Maybe the OP should determine this.

After further review a more realistic design is probably like this:

We can still do scans in multiple locations at the same time and still test for duplicate scan times.

I deleted my old post with the file attached. I don't think the design was quite right.

 

[GaP42]

@bilakos93
Taking your database, I modified the schema as shown here (taking account of the suggestions made earlier) to support multiple scans per appointment, and to allow the location for the appointment where the scans to be conducted for the appointment to be recorded. The location would be the bed/room where the scan is to occur. Further enhancement could be needed to provide info about the scan equipment in use. Mobile scan equipment might also need to be considered.

If multiple locations are needed for one appointment then the location relationship would need to point to the BookedScan.

I am assuming ExamtypeID is a simple flag for inpatient/outpatient.

Note the "red-tape" fields to log creation/changes to records in key tables.

Cons_ID identifies the Dr assigned to the appointment whereas HospDeptID is the department that requested the scan. There is no Dr name associated with the Dept so this data will not tell you who to send the report back to. That is your business process I assume.

 

[LarryE]

@bilakos93:
If you are interested, I have a scan schedule model that incorporates FAYT. I can attach the file for you to test if you wish. It also detects and prevents duplicate appointments for the same scan location on the same day at the same time.