Database Design Pharmacy | Database Design Help | Realcode4you

The pharmacy database consists of six main entities: Patient, Doctor, PharmacyBranch, Medication, Prescription, and Inventory. The relationships among these entities are represented through primary keys and foreign keys in the database design.

1. Doctor to Prescription (One-to-Many)

• Cardinality: 1 : M

• A doctor can write many prescriptions, but each prescription is written by only one doctor.

• This relationship is implemented through the foreign key: Doctor_Id in the Prescription table, which references: Doctor(Doctor_Id)Example: One doctor may prescribe medication to many different patients over time, but each prescription record is associated with only one doctor.

2. Patient to Prescription (One-to-Many)

• Cardinality: 1 : M

• A patient can receive many prescriptions, but each prescription belongs to only one patient.

• This relationship is implemented through the foreign key: Patient_Id: in the Prescription table, which references: Patient(Patient_Id)Example: A patient may visit a doctor multiple times and receive multiple prescriptions, but each prescription is issued to only one patient.

3. Medication to Prescription (One-to-Many)

• Cardinality: 1 : M

• A medication can appear in many prescriptions, but each prescription record refers to only one medication.

• This relationship is implemented through the foreign key: Medication_Id in the Prescription table, which references: Medication(Medication_Id)Example: The same medication, such as Paracetamol, may be prescribed to many patients, but each prescription record contains one specific medication.

4. Pharmacy Branch to Prescription (One-to-Many)

• Cardinality: 1 : M

• A pharmacy branch can process or fill many prescriptions, but each prescription is associated with only one pharmacy branch.

• This relationship is implemented through the foreign key: Branch_Id in the Prescription table, which references: PharmacyBranch(Branch_Id)

• Example: A single pharmacy branch may fill many prescriptions for different patients, but each prescription is picked up from only one branch.

5. Pharmacy Branch to Inventory (One-to-Many)

• Cardinality: 1 : M

• A pharmacy branch can have many inventory records, but each inventory record belongs to only one pharmacy branch.

• This relationship is implemented through the foreign key: Branch_Id in the Inventory table, which references: PharmacyBranch(Branch_Id)

• Example: A pharmacy branch may store many medications, and each stock entry in the inventory belongs to one branch only.

6. Medication to Inventory (One-to-Many)

• Cardinality: 1 : M

• A medication can appear in many inventory records, but each inventory record refers to only one medication.

• This relationship is implemented through the foreign key: Medication_Id in the Inventory table, which references: Medication(Medication_Id)

• Example: The same medication may be available in multiple pharmacy branches, and each inventory entry represents that medication at one branch.

Many-to-Many Relationship in the Model

7. Pharmacy Branch to Medication (Many-to-Many)

Cardinality: M : N

The relationship between PharmacyBranch and Medication is many-to-many.

·       A pharmacy branch can stock many medications 

·       A medication can be stocked in many pharmacy branches 

Since relational databases do not directly implement many-to-many relationships, this relationship is resolved using the Inventory table.

Relationship Resolution: The Inventory table acts as a bridge entity between:

·       PharmacyBranch 

·       Medication 

This allows the database to store:

·       which medication is available at which branch

·       stock quantity

·       expiry date

Example: A medication such as Amoxicillin may be stocked at several pharmacy branches, and each pharmacy branch may carry many different medications.

Doctor to Patient Relationship (Indirect Relationship)

8. Doctor to Patient (Many-to-Many, via Prescription)

Cardinality: M : N (through Prescription)

The relationship between Doctor and Patient is indirectly many-to-many through the Prescription table.

·       A doctor can prescribe medications to many patients 

·       A patient can receive prescriptions from many doctors 

This relationship is not stored directly as a separate table because it is already captured through the Prescription entity.

Example: A patient may visit different doctors, and a doctor may treat many patients.

Patient to Pharmacy Branch Relationship (Indirect Relationship)

9. Patient to Pharmacy Branch (Many-to-Many, via Prescription)

Cardinality: M : N (through Prescription)

The relationship between Patient and PharmacyBranch is also indirectly many-to-many through the Prescription table.

·       A patient may pick up prescriptions from different pharmacy branches 

·       A pharmacy branch serves many patients 

This relationship is captured through the Prescription table because each prescription identifies the branch where it is filled.

Example: A patient may collect one prescription from a downtown branch and another from a different branch.

Summary of Cardinalities

Relationship	Cardinality
Doctor -> Prescription	1 : M
Patient -> Prescription	1 : M
Medication -> Prescription	1 : M
PharmacyBranch -> Prescription	1 : M
PharmacyBranch -> Inventory	1 : M
Medication -> Inventory	1 : M
PharmacyBranch <-> Medication	M : N
Doctor <-> Patient	M : N (via Prescription)
Patient <-> PharmacyBranch	M : N (via Prescription)

Conclusion

• The database design uses both one-to-many and many-to-many relationships to model a real-world pharmacy system.

• The Prescription table plays a central role in connecting doctors, patients, medications and pharmacy branches, while the Inventory table resolves the many-to-many relationship between pharmacy branches and medications.

• This design ensures that the database can accurately record prescriptions, stock levels and branch-level medication availability.

For any database design help you can contact us(realcode4you@gmail.com).

We are providing complete and quality work with proper explaination.