Solution to the problem with ERD

[9749]

Good day
I have a problem with designing database. I'm learning access but I have to start from ERD. Could anyone help me with this one. All I need is the tables for example
Doctor (Doctor ID, Name, Specialization,Spouse). I have tried draw it but still lost and every solution looks ok.
Thanks in adwance

The clinic has several consultation rooms attended by doctors. A doctor visits clinic couple of sessions in a week. A typical session is between 9 am and 1 pm; 4 pm and 8 pm.

Patients meet a doctor with prior appointment. The receptionist fixes appointments for a doctor. During the appointment the doctor provides diagnoses. If the patient is willing, then treatment is also prescribed.Some doctors are GP’s while others are specialists. Fees charged are same for all GP’s; but for others varies according to their specialisation, however is more than the fees charged by a GP. A specialist can perform many duties, which a GP cannot do – for example surgery, interpret radiographs, etc. A patient can meet a specialist only with a referral from a GP. A referral is valid for 6 months, after which the patient must obtain a new referral to meet a specialist. A referral is always given to a specific specialist.Payment can be made either by cash, or cheque or through credit card. Cheque or Credit card details are recorded in the system. Doctors are required to pay a percentage from each consultation to the medical clinic for using consultation chambers. Doctor spouse details are also required in the system only if the spouse is also a doctor.

 

[lespaul]

From your description it seems to me that you need the following tables:

Doctor
DoctorID, FirstName, LastName, MiddleI, Address, DrCode

DoctorSpouse
DoctorID, SpouseFName, SpouseLName

DoctorCodes
CodeID, Description, Fee
1 General Practicioner $20
2 Speciality #1 $20

Session
SessionDate, SessionType, DoctorID

Appointments
DoctorID, PatientID, ApptDate

Referrals
DoctorID, PatientID, ReferralDate, ReferredTo

This should give you a good start. You mentioned that every solution looks ok, that's because a database design can be done in many different ways. Without knowing more about what you're doing, this is about all I can offer.

HTH



Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[Transcend]

Also perhaps

Patients
PatientID, PatientName, Address, phone

PatientPayments
PatientID, CreditCardNo, Cheqno

...

lespaul is right, there is usually no one right answer when designing a database. There can however be many wrong ones.

Transcend

 

[9749]

It is really great help. Thanks a lot. I sat last night and looked into it and well let's see o this determines this one this that err.. I think you were right saying that there many solutions but not all are work ok. Thanks for your help. Now I know how to start.

 

[lespaul]

The thing you want to watch out for is duplication across tables. My best example is the dog trick database. You could set it up like this:
DogID
DogName
Trick#1
Trick#2
Trick#3
Trick#4 etc.

but then you have to say, well what if the dog knows more tricks that there are fields? So the better way is:
DogTable
DogID
DogName

TrickTable
TrickID
TrickName

DogTrickTable
DogID
TrickID

That way only information about the DOG is in the dog table only information about tricks is in the trick table and all the tricks that a dog can do are in the DogTrickTable.

Hope you are making some progress!


Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!