I think I put the keys backwards in my database design.

[Melaniecarr23]

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 pmtID	SOAP ID datetime subjective objectiveID assessment plan	OBJECTIVE ID objective APPT_STATUS ID status APPT_TYPE ID type
SCAN ID datetime prepost constants scanname progression legcheckID adjusted notes	PATIENT_EXAM ID datetimeexamID postive (yes/no) reflexID (lookup) muscleID (lookup) details	HISTORYCC ID datetime cc location mechanism onset site etc...	LEG CHECK ID legcheck
PMT ID duedate dueamt paiddate paidamt pmttype (lookup) note	EXAM 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.

[Melaniecarr23]

No worries. I fixed it all by myself with excel.