I have been working on a database for my office and put together a few tables on the fly (big mistake #1). Then I went to add a new table and realized that I think I did it backwards.
I have the following table setup:
PATIENT
ID
first
middle
last
suffix
etc...APPT
ID
date
time
typeID
statusID
note
reminder
soapID
scanID
historyccID
patientexamID
pmtIDSOAP
ID
datetime
subjective
objectiveID
assessment
planOBJECTIVE
ID
objective
APPT_STATUS
ID
status
APPT_TYPE
ID
typeSCAN
ID
datetime
prepost
constants
scanname
progression
legcheckID
adjusted
notesPATIENT_EXAM
ID
datetimeexamID
postive (yes/no)
reflexID (lookup)
muscleID (lookup)
detailsHISTORYCC
ID
datetime
cc
location
mechanism
onset
site
etc...LEG CHECK
ID
legcheck
PMT
ID
duedate
dueamt
paiddate
paidamt
pmttype (lookup)
noteEXAM
ID
name
description
regionID (lookup)
typeID (lookup)
There is an ID key in patient that relates to SOAP, scan, historycc, exam, pmt patientID field as a one-many relationship.
There is a date time field in SOAP, scan, historic, exam, and pmt with a default value set to now(). I want to know when the record is added to the database.
My issue comes from not creating the appt table until AFTER I had the other tables created.
I want to be able to create an appt record with patientID, date, time, appt_type, and appt_status (and have a form where I can do that).
The issue comes from the following:
There may be more than one exam, scan, history, or pmt during each visit and I will want to pull up all of the information related to a visit later on. I was thinking I could somehow populate my appt table with information from the scan table (using only the first scan for each visit) and updating the appt table with the patient ID and date. But my tables are not linked properly. I have them linked by scanID.
I want to be able to display all scans separately from the soap notes, etc. - but I also want to be able to gather all that information based upon the date of the visit.
I am thinking that maybe what I need to do is add an apptID field to the other tables, as they will always belong to an appt but not necessarily the other way around, and that I don't need a soapID, historyccID, patientexamID, pmtID in the appt table if I do that.
Any thoughts on if this would work or what might fix it?
Help is MUCH appreciated. It's NOT really like riding a bike. I used to be good at this stuff.