How to design relational database schema?-CodePudding

I have two entities namely Prescription and Medicine.

A Prescription must include one or many Medicines and a Medicine may be included in many Prescription.
Two attributes which are associated with the relationship is Doze_Per_Day (how many to take per day) and Before_After_Meal (When to take the medicine).
Both Prescription and medicine has primary keys prescription_id and medicine_id respectively along with other attributes.

If I add medicine_id in prescription table it will create mupltiple tuples with same prescription_id and if I add prescription_id in medicine table, it does not make any sense as same medicine can be included in many prescription.

Please suggest some good way(s) to create relational database schema for above data.

CodePudding user response：

To create a M:M relationship you need an extra table:

table Prescription_Medicine
---------------------------
fk_prescription
fk_medicine
doze_per_day
before_after_meal