Re: Another simple Q from a beginner

In article <8ravpb$c1c$1_at_nnrp1.deja.com>, geoff_miller_at_my-deja.com writes
>In article <39D857B7.3D55DEE8_at_b.com>,
> Snorkel <a_at_b.com> wrote:
>> Prescription have already a datefield to keep them apart.
>>
>> Hugo wrote:
>>
>> > Dear All,
>> > Here's a simple Q about the a database design :
>> > 1)every patient has a primary physcian. every physcian can have
 several
>> > patients.
>> > 2)physcian prescribe drugs for patients.a physcian can prescribe
 one or more
>> > drugs for several patients and a patient can obtain prescriptions
 from
>> > several doctors. each prescription has a date and a quantity
 associated with
>> > it.
>> >
>> > So could u pls tell me how to draw the ER disgram or tableif a
 doctor
>> > prescribes the same drug for the same patient more than once,
 several such
>> > prescriptions have to be recorded.
>> >
>> > thx a lot !!
>>
>>
>
>Even if a doctor wrote multiple prescriptions for the same patient on
>the same day there shouldn't be a problem.
>
>Patient Table
>
>Doctor Table
>
>Prescription Table
> Sequential number as key, automatically generated
> Doctor
> Patient
> Date
> Drug
> Quantity
> ...
>
>This also makes the assumption that each prescription is only for one
>drug, unless you're fortunate enough to be using a sensible DBMS which
>handles multivalued fields.

Or better, add a scripline table with a compound key based on the prescription and a line number, or possibly the drug. If you *know* that prescriptions can never include more than one instance of the drug then prescription+drug is a good key. Otherwise add an automatically generated line number and use prescription+line as the key. Of course if you merely *believe* that prescriptions can't contain the same drug twice then use the line numbers anyway.

-- 
Bernard Peek
bap_at_shrdlu.com
bap_at_shrdlu.co.uk