"ABZ123" wrote in
oups.com:

95% of the time the treatment does
not change and 99.9% of the time the Practice that the patient goes to
does not change. Usually, the doctors too remain the same.

Only over the short term. Over the several years, practically all the
doctors will change; and over the course of decades most of the patients
will have changed practice. It all depends on your viewpoint and how long
you expect your app to run. Bear in mind that about a year ago I had to
upgrade an Access app that I created on version 1.1, and it had not been
changed in the meantime!


Patients - Orders - OrderDetails - Products
v v
| |
Practices -------------- Doctors

IMO, this seems a bit safer, and will give you a more reliable way of
finding the Dr responsible for a script. Thinking about it though, in an
Order corresponds to a prescription, and an OrderDetail corresponds to
items on a script, then presumably a whole Order will relate to one Dr,
so perhaps the FK belongs there.

For example, Brenda goes to Dr Allen for FP, Dr Bardsley runs the
practice asthma clinic, and Dr Charles does the diabetics. She therefore
has three Orders, consisting of (Microgynon), (Ventolin, Becotide), and
(Actrapid, Mixtard, Syringes and needles, BM Stix), with three different
doctors. Yes, I know that most patients will have one Order with one
Detail, but it's not the 99% that sinks databases, it's the 1%.

All the best


Tim F