Database design with a single entity with many different units

Question

I'm new to database design and I am working on a project that requires the use of a single entity (medication) that could be tied to any number of patients and each patient could have a different dosage. What would be the best way to layout a table for this type of situation. I could use a single table and just store each individual medication and dosage and tie that to the unique patient. But that would give me duplicate entries in the medication table (same med with just different dosage).

What I would like is to have a single entry for each medication name and have each patient have a unique dosage for that particular med. Of course a single patient could also have many different medications so I would have to be able to have a unique dosage for each med for different patients.

I using entity framework model first approach. Would I use a single table T_Patient_Medication and use each of the two table IDs as the primary key combo and then use a dosage field for that combination? If so how would I create the association to tie this table to the other two. Any suggestions?

Answer 1

Off the top of my head:

-a medication table(MedicineId, MedicineName, etc).

-a patient table(PatientId, PatientName, etc)

-a patient-medicine table(MedicineId, PatientId, Dosage, date, notes etc).

In other words, the medication table contains on row per unique med, a patient contains one row for each unique patient.

The patient-medicine table is where these two things meet: it contains a patientId, a medicineId and then anything else unique about that patient getting that medicine (i.e. Dr. name, dosage, date started etc). Personally I would make each row in the patient-medicine table also have its own unqiue ID separate from the combination of the patientid and medicine id (what are you going to do when the same patient goes back on the same medicine at a different time, if your primary key is Patientid+Medicineid). Each record should have its own unique id in my way of thinking.

There would be foreign keys between the tables to enforce this relationship: i.e. you can't add a row to the patient-medicine table unless the patientid exists in the patient table, and the medicine exists in the medicine table; and equally important prevent you from deleting a rows from tables where there are dependent records in other tables. If you take the time and setup all those foreign keys (relationships), it will be a breeze in EF to navigate the related records.

It is without a doubt more complicated than this, but that is the basics idea of a relational table.