Re: Help - Optometric Practice DB Design

I've anotated your details with some comments you may find useful. Its generally worth making a rough entity relationship design diagram to be sure that every table has attributes which make for a unique relationship with another table at least in one direction. If the relationship is "many to many" then you may need to introduce extra tables such as one to record an event.

Good luck,
Ken_Hansen

In article <393F5FEF.12F553E_at_vodacom.co.za>,   Hoosain Madhi <madhih_at_vodacom.co.za> wrote:
> I am involved in designing a database for my wife's practice, and will
> be
> in real trouble if I do not get something done by the end of the week.
>
> What I need to do is conceptually simple. However I am having problems
> with the relationship between the tables.
>
> So far I have defined
>
> Patients Details
> -Patient ID (Unique Number for each Patient)
> -Patient Name
> -Patient Date of Birth
>
Patients Details joins to MedicalAid on PatientID, similarly, Consultation

> MedicalAid
> -PatientID
> -Medical Aid Name
> -Medical Aid Number
> -Principal Member (The owner of Medical Aid Account)
>
> Consultation
> -ConsultID(Autonumber)
> -PatientID
> -Date of Consult
>
In order to join the above tables to Dispense you must either add an attribute to Dispense in respect of Consultation (Does every Dispense result from a Consultation ? If not or you might have many Dispenses for one consultation, or vice versa, then introduce an extra table : Dispense_event including patient_id, optional consultation, the date and any other required details.

> Dispensing consists of an Optometric examination, a frame, a right
 lens,
>
> left lens, General Additions, Procedure, and Other so therefore:
>
> Dispense
> -DispenseID
> -ConsultID
> -Optometric Examination
> -Frame
> -Right Lens
> -Left Lens
> -General Additions 1
> -General Additions 2
> -Procedure 1
> -Procedure 2
> -Other
>
Will you want to add status e.g. ordered/collected/paid etc

> Tarrifs
> -Code
> -Description
> -Price
>

If this is general sales price then you might want to relate this to dispense and also record any discounts etc.

> Can someone please explain to me how to relate these tables. Thanks
>
> Hoosain Madhi
> Johannesburg - South Africa
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu Jun 08 2000 - 00:00:00 CEST