Re: A simple Q from a beginner

From: Steve Long <nospam_at_hotmail.com>
Date: Sun, 1 Oct 2000 15:27:11 -0400
Message-ID: <8r82vr$9r3$1_at_bob.news.rcn.net>

O{Physician -> Patient}= {1,m} [A physician has 0 or more patients]
O{Patient -> Physician}={1,m}  [A patient has one or more physician]
O{Patient->Prescription}={0,m}[A patient has 0 or more prescription]
O{Prescription -> Patient}={m,1}[A prescription belongs to 1 patient]

 O{Physician -> Prescription}={1,m}[A physician writes 0 or more]

Since the relationship between physician and patient is many-to-many, a junction entity is required to resolve this mapping, so something like a PatientPhysician table is required with at least PatientID and PhysicianID as foreign keys to their respective base tables Patient and Physician.

It also appears you want a relationship between Physician and Prescription, but in this case you need only add PhysicianID to the Prescription table since a prescription is written by exactly one physician (and for exactly one patient).

How you choose to normalize or denormalize really depends on the application, ie is this OLTP or DSS or ODS. In this case, there is no right or wrong, other than in the context of what is required by the app.

"Hugo" <damoncwk_at_hotmail.com> wrote in message news:8r7tui$13mu3_at_hkunae.hku.hk...
> Dear All,
> If a doctor can prescribe several drugs for a patient. And a
> patient can take drugs from several doctors . How is the ER Diagram look
> like if every prescription of the same drug from the same doctor for the
> same patient are to be recorded, i.e. not only the latest prescription is
> recorded?
>
> thx a lot.
>
>
Received on Sun Oct 01 2000 - 21:27:11 CEST