Hospital database design

[vinny1990]

Hi,

I am designing a database for a hospital.

I have to make around 12 tables but if i get the basic structure of the first 2 tables correct, i will be able to do the rest.

basically, i have a patient table which has basic patient details like patient number(unique), name etc. and then there is a disease table which has a patient number field same as the patient table, a few disease fields and a calculated field which simply amalgamates the patient number, date and the site of the disease called the disease_id.

I want a one to many relationship between these 2 tables as one patient can have multiple diseases.

So

1. What field constraints do i have to put.

2. How am i supposed to join the tables.

3. How and in which layout do i make the button which enables me to add a new disease to a patient.

Any help will be appreciated.

[efen]

I want a one to many relationship between these 2 tables as one patient can have multiple diseases.

A one-to-many relationship from patients to diseases will not work if you need to report on diseases.

[vinny1990]

so how do i relate them?

[comment]

A one-to-many relationship between Patients and Diseases would look like this:

Patients::PatientID = Diseases::PatientID

If you place a portal to Diseases on a layout of Patients, you can create new related diseases simply by entering them into the first empty portal row (provided you have enabled this in the definition of the relationship).

a calculated field which simply amalgamates the patient number, date and the site of the disease called the disease_id.

This is neither necessary nor advisable. Define DiseaseID to auto-enter a serial number.

[vinny1990]

the disease id has to be like that as it goes into the national database format as (patient number-date_of_diagnosis-site)

[bcooney]

Vinny,

Please adjust your skill level. I imagine it is not "entry-level" since you have passed the FM7 certification test.

-Barbara

[comment]

the disease id has to be like that as it goes into the national database format as (patient number-date_of_diagnosis-site)

That's fine - just call it something else (at least here) to avoid confusion.

[vinny1990]

right, the logical scenario goes like this

patient>>>>>diseases>>>>>diagnosis details

there has to be a disease id generated every time a patient comes to the hospital with a disease

patient(PK:patient number)

diseases(PK:diseaseID, FK:patient number)

diagnosis is like multiple tables containing the information of the whole treatment which needs to be connected to diseases.

diagnosis(FK:diseaseID)

diseaseID= patientnumber-date_of_diagnosis-site

I think i have drawn the relationships correctly, as ive linked

Patients::PatientID = Diseases::PatientID and Diseases::DiseaseID=Diagnosis::DiseaseID

Where and how do i have to create the button which will enable me to create a new disease and diagnosis details for an existing patient(who will have the details in the patient table as before).