General Questions and advice (1 Viewer)

Alright, see that makes sense and is one of the things that was confusing me when I had it all laid out. I was just trying to link everything with the appropriate ID. Example being the Cmeds and Cmeddiagnosis. The reason I did that one is because some medications may be used for multiple diagnosis, so I thought having the one table specifying which it was for would be needed.


The lookup table makes perfect sense, and would really eliminate a lot of what I felt were extra tables.



I also wondered about the emp links, and the client links as well actually. It would kind of make sense to have the clientinformation be the link to the meds, diagnosis, etc rather than the contact list because only the clients will have that extra information. I would probably link empinfo rather than payroll though.

You do run into a rather unique situation that I'm not sure how your business model will address;

Client is seen.
Diagnosis is made.
Script of meds is written.

In my view, the important item would be the appointment in this case. One or more diagnosis will be made. Zero or more medications will be prescribed. Each prescription will have a quantity and expiration date. As such, a client can receive the same medication multiple times even if it is not for the same diagnosis.

This would imply a link between visit and medication that has an expiration date in it. Likewise you would go through visits to see what medications a patient is receiving. This aids tremendously when you are reporting on these things as the date of their visit becomes the "Starting" date for a prescription while each has its own ending date. This also avoids confusion when more than one diagnosis is given that would require the same medication.