Understanding of cardinality it ERD models

Question

I have huge trouble with understanding the cardinality.

Let's say that there Doctor and he can perform heart surgery on just one patient. Surely there are many doctors and many patients, but should the model be just like this?

PACIENT-|----------|- DOCTOR   meaning 1:1?

Does it not need to reflect that there are many doctors and many patients? Or this describes relationship between two actual instances?

Another example: Hospital runs an experiment and can have many patients in drug trials. Each trial tests just one drug and no patient can appear in more than one trial.

TRIAL |- ------<-PATIENT (MANY TO ONE - more patients in trials are allowed)

PATIENT|------|-DRUG (1:1 if one patient can only take one drug)?

But how to indicate that the same patient cannot appear in any other trial? Or is it implicit by the relations between Trial and Patient where there is 1 at the side of Trial?

Answer 1

In terms of actual entity representation, it would work something like this:

• If you have a 1 to 1 relation, then only one of the entities will have a corresponding table, the other will be included in the corresponding record.
  Example: 1 Patient has only 1 Hospital Bed, and there can only be 1 Patient in 1 Hospital Bed. In that case, Hospital Bed should be represented by a column (bed number) or a collection of columns in the Patients table.

• If you have a 1 to Many relationship, then you create a Foreign Key relationship between the first(1) entity (which will be the primary key entity) and the second(M) entity.
  Example: 1 Patient can take only 1 Drug, but 1 Drug can be taken by Many Patients. In that case, DrugId would be a foreign key in the Patients table.

• Finally, in the case of a Many to Many relationship, the typical technique is to create an association table which will contain two foreign keys, one from each entity, and perhaps some other columns.
  Example: 1 Doctor can work in Many Hospitals and 1 Hospital employs Many Doctors. Therefore, we should have an association table, say MedicalJobs, which will have foreign keys HospitalId and DoctorId and perhaps Salary, JobId etc.

Concerning your Trial - Patient question, if one trial has many patients but a patient can only participate in one trial, then it is a 1 to many Trial-Patients relationship, therefore, all you need is a foreign key TrialId in the Patients table.

I hope this clears it out. If you need additional explanation, please comment on this post.