What I created was a medical lien database and everything has been working great, as it was limited in the complexity. I'm now trying to expand the functionality and have run into a hiccup... How do I know if I am properly relating the table occurrences? I am running into problems with circular references due to my lack of experience. If you look at the APPOINTMENTS T.O. I made an attempt at trying to work-around the circular reference issue. I don't think I did the right thing though...

I'm thinking a few different things... but I just want to get some clarification as to why my design is or is not correct.

I've attached a cloned database without any records due to regulations I couldn't put actual data in it. It's contained in the zip file with a password on it. The account name is: dbforums & the password is: pass1234

I would appreciate any constructive comments.

EDIT:
Here is the outline of my logic:

- A patient can have many liens, but a lien may only have 1 patient. A patient does not have to have a lien to exist. A lien cannot have 0 patients or it will not exist. (Many to One)
- A lien can have multiple procedures, a procedure can be on many liens. A lien does not have to have a procedure to exist. A procedure does not have to have a lien to exist. (Many to Many)
--------> To Solve the Many to Many relationship problem, I created a join table called "lines".
- A lien can only have 1 provider, but a provider can have many liens. A lien cannot have 0 providers or it will not exist. A provider does not have to have a lien to exist. (One to Many)


Where I am confused... is how to I would link an appointment into the equation. As I would like the "appointment" to eventually create the "procedure" but an appointment does not 100% of the time create a procedure. (Patient's missing the appointment, rescheduling, canceling, etc.) Is there any method I can use to better understand how I would figure this out?

Nullable foreign key from Lines to Appointments, allows multiple procedures per appointment.
Now, this does not prevent an appointment from having procedures on two or more liens. If you are worried about this and want to prevent it, then you add the Lien ID to the appointment table and use both the LienID and the AppointmentID as foreign keys in your Lines table.

I really appreciate your help Blindman. I think I understand it a bit better now.

Now I'm just trying to figure out if I am removing all of the relationships to the APPOINTMENTS T.O. in order to connect to the LINES T.O. or if I leave all of those relationships intact and connect to the LINES T.O. as well.

I believe it would make sense to have a "Nullable foreign key". A nullable foreign key is basically a relationship that is allowed to have the "null"/"" value? Therefore not being related to a specific T.O.

What I'm thinking I should do is:

1. Remove the existing relationships (LINES_APPS, PROCEDURES_APPS, PHYSICIANS_APPS, PROCEDURES_APPS) to the APPOINMENTS T.O.
2. Add a FK to LINES T.O. then place a relationship from APPOINTMENTS PK to the new LINES FK.
3. Add new relationships to PROCEDURES, PHYSICIANS & PROCEDURES from the APPOINTMENTS T.O.

My concern is this is going to give me the "Circular Reference" error. So should I leave the X_APPS (Where X = Entity Name) intact and not delete them?

Been on vacation for the Holidays.

Did your solution work?