Database: `One to One` vs `One to Many`

Question

Note: This is not an inventory controlling system. I am just trying to map which medication given to which patient. I am not considering how many medication packets etc. Just a single medication event

I am having a sudden confusion with database relationships, even after working with them for years. Below is my situation.

I have a table called patient where it will hold the information of patients. I have another table called medication where it will hold the medicines prescribed for patients. I want to find the relationship, so I asked the below questions from me.

1. Can one patient have many medicine prescribed? - Answer: YES
2. Can one prescribed medicine have many patients? - Answer: No (ex: you can't give a patient a paracetamol to drink, take it out and give it to someone else)

I need to create the foreign key of patient in medication table. I'm confused, because my answer for 1st question tell me it is one to many relationship while the answer for 2nd says me it is one to one relationship.

What is the exact relation when I am planning to add the foreign key of patient in medication table?

Below is my structure

Answer 1

It somewhat depends on the kind of structure of your tables.

Example 1

Patient:

PatientID Name
--------- ----
1         John
2         Matt

Patient_Medication:

PrescriptionID PatientID Name
-------------- --------- ------------
1              1         Antacid
2              1         Paracetamol
3              2         Asthma inhaler

You are in a one to many relationship. Patient John can have multiple medications in prescription table.

Example 2

Patient:

PatientID Name
--------- ----
1         John
2         Matt
3         Katie

Medication:

MedicationID Name
------------ ----
1            Antacid
2            Paracetamol
3            Asthma inhaler

Patient_Medication:

ID  PatientID MedicationID
--- --------- ------------
1   1 (John)        1 (Antacid)
2   1 (John)        2 (Paracetamol)
3   2 (Matt)        3 (Asthma inhaler)
4   3 (Katie)       2 (Paracetamol)
5   3 (Katie)       3 (Asthma inhaler)

This situation is a many-to-many relationship where many patients can have many medications and vice versa. Usually Patient_Medication is called a junction table.

Answer 2

I think the relationship should actually be many-to-many. A given patient record could point to several different medications, and similarly a given medication record could point to several different patients.

One way to implement this would be to create a third table which maps patients to medications (or medications to patients, if you prefer to think of it that way). This table might look like this:

id | patient_id | medication_id | date
1  | 1          | 1             | 2016-12-19
2  | 1          | 2             | 2016-12-18
3  | 2          | 2             | 2016-12-18

The above data would imply that patient 1 took medications 1 and 2, and medication 2 was also being taken by patient 2. I also added a date, which might be a proxy for a given patient visit.

The medication_id could be a unique identifier for a given pack of medication delievered. In another table, each unique medication would be related to a parent table for that medication.

Update:

Your current schema does not look far off, except that the table you labelled medication is actually a bridge table between patients and their medication dosages. You would need a third table which stores the metadata for each medication. This metadata would be constant for all medication dosages, e.g. type of drug, cost, etc.

Answer 3

Could your confusion be the result of not having defined what the medication table actually represents. It seems to me that your are confusing type of medicine and actual packets.

So what kind of relationship are you trying to model? Are you doing a system that can do inventory of how much medicine you have, or are you doing a patient system that can tell you how many patients are getting a particular medication.

I think your answer to question two is wrong, many patients can be on the same medication. The number of packets you have in stock should be handled in a separate table where you could hold information on things like how many packets you have, what their location is and so on.

So you need at least three tables

patient - holds the patient medication - holds the types of medication patient_medication - holds the information on what types of medication the patient is on

you can then add things like another table to hold information on how much of a medication you have and where it's stored if that is relevant to the system.

Answer 4

It's not wrong to say that the association is one-to-many in one direction, and one-to-one in the other direction. When planning a database, I often advise people to write out the associations in both directions:

• Each patient can have zero or more medications
• Each medication belongs to one and only one patient

This helps to determine the cardinality of the relationship and clarify functional dependencies. When only one direction is specified, it can be difficult to distinguish one-to-many from many-to-many associations.

When talking about the relationship as a whole, we take an "overhead perspective" and ignore the perspective of individual entities, so we would call this example one-to-zero-or-more, or commonly just one-to-many.

Many-to-many relationships look like two one-to-many associations when you view it from the perspective of individual entities on either side.

Answer 5

Your second question:

2. Can one prescribed medicine have many patients? - Answer: No (ex: you can't give a patient a paracetamol to drink, take it out and give it to someone else)

I guess here you have assumed that prescribing a medication and actually consuming that mediation (actual tablet) in real world is the same thing.

The medication table is just a name holder for the medication.

Your answer would have been correct if your table "Medication" would be storing actual instances of medication.

E.g.

Medication

Id Name

1 Paracetomol 25mg Instance 1

2 Paracetomol 25mg Instance 2

3 Paracetomol 25mg Instance 3

Now here, table is actually containing medication instances which can not be consumed by two patients. And here your answer "No" is, I guess, correct.

The other thing is, as you said you are not working on inventory system, and just trying to map medication, you are still attached to real world inventory item which cannot be consumed by two patients.

Here you are mixing inventory item in a system where inventory item is not required.