Please help a newbie surgical trainee build a relational database!

[medbeej]

I'm not sure if this is the right place to post these questions - apologies if I'm being inappropriate, but I'm new to all this!

I'm a junior paediatric surgical trainee and i'm trying to set up a filemaker database to keep track of patients I've seen and whose care I've been involved in. I have just purchased Filemaker Pro 12 and have downloaded the associated Filemaker Go for iPad app.

I have identified the following fields (arranged into tables that I think make sense, but am happy to be corrected):

Demographic Table

• Patient ID (this is the unique patient identifier)
  
• UR Number (the hospital's pt identifier, but given i'll be working across multiple hospitals I will need to allocate my own identifier)
  
• Last Name
  
• First Name
  
• Date of birth
  
• Age (calculated from date of birth)
  
• Street
  
• Suburb
  
• State
  
• Country
  
• Postal Code
  
• Mobile phone number
  
• Home phone number
  
• Parent1 Last name
  
• Parent1 First name
  
• Parent2 Last name
  
• Parent2 First name
  

Admission Details Table

• Patient ID (the same unique identifier as above)
  
• Admission ID (do I need this field? - each patient needs to be able to have multiple admissions. I'm thinking of trying to make a one (patient ID) to many (admission ID) relationship)
  
• Admission date
  
• Discharge date
  
• Ward
  
• Bed number
  
• Diagnosis
  
• Admission history
  
• Birth history
  
• Past history
  
• Family history
  
• Social history
  
• Physical examination
  
• Investigation results
  
• Progress
  
• Plan
  
• Tasks
  
• (NB: from "Diagnosis" to "Tasks", these are all just simple text fields)
  

Operation Details Table

• Patient OR admission ID ?? (see next point)
  
• Operation ID (do I need this field? - each patient needs to be able to have multiple operations in the one admission. I'm thinking of trying to make a one (patient or admission ID) to many (operation ID) relationship)
  
• Procedure region (body region of operation)
  
• Operation
  
• Role in operation
  
• Details of operation
  
• Operation date (date field)
  
• Time since operation (calculated from operation date, preferably displayed in days and weeks/months and years)
  
• Complication
  
• Complication severity
  
• Complication details
  
• Photo field (photo placeholder)
  
• (NB: other than those indicated above, the other fields are text fields or simple drop down menus)
  

I am getting stuck on the building of relationships between the tables. Essentially I need to have one patient be able to have multiple admissions, and have each admssion for a given patient to be capable of having multiple operations during that admission. Each admission ID needs to be assigned to a patient ID, and each operation ID needs to be assigned to an admission ID (and hence connected to a patient ID). Am I being too ambitious?

In the end, I would like to be able to use the database on my iPad at work, so I would like to create a single user interface (layout). In the interface I envision having the Demographic Table details displayed permanently in the upper part of the layout, while Admission Details and Operation Details tables in the lower part and (preferably) in a tabbed arrangement to allow alternating between the two.

If I can achieve that initial set up I would be absolutely thrilled and any help with pointers in the right direction would be greatly appreciated.

Other goals for the database, but perhaps I can build these in later:

• I would also like to be able to identify which patients are current inpatients (I'm assuming this would be based on their discharge date)
  
• In the admission and operation tabs of the user interface/layout, I would like to be able to click through admissions & operations (in order of most recent to most distant; presumably by sorting records by date)
  
• Be able to create reports of that include fields from all three tables (eg: list of current inpatients, operations during a particular time frame)
  

I have had a crack at setting this database up, but just keep running into things I can't figure out, and surgical training unfortunately makes getting past these troubles hard!! I really appreciate any help that can be offered.

[efen]

Does the attached give you a start?

PatientsOperations.zip

[Tom R.]

Hi Medbeej -

I came up with a table occurrence graph that may not be exactly suitable but hopefully should point you in a good direction. In the graph, the 3 pronged end of the line represents the "many" end of the relationship.

You would need the following tables:

People

Addresses

Phone Numbers

Notes (with a field for type of note, e.g. patient history, procedure complication, family history, etc.)

Attending Staff

Admissions

Family Relations

Procedures

You would have multiple instances of the People table in your relationship:

Patients

Parents

Staff

Multiple instances of Notes:

Patient Notes

Procedure Notes (where you could enter notes regarding complications, etc.)

Attending Staff would be a join table between Staff and Procedures. You would put your fields for the Role of the person in the operation here.

Admissions - you would want fields for the hospital, patient id for that particular admission, start date & end date of admission, diagnosis

Instead of, or in addition to, the Procedure Notes, you may want to have a separate table just for Complications, with fields for type, description, severity, etc.

Hope this helps!

Tom

patient care TOG.pdf

[Dr. Evil]

Does the attached give you a start?

Love the demo file!