CQMC Database ERD Modeling | Assumptions Business Rules
CQMC Database ERD Modeling list of assumptions made while designing the ER diagram from CQ Medical center is as below
CQMC Database ERD Modeling when an appointment is scheduled it is assumed that the patient may or may not provide the value of Genera Physicians’ unique number. Hence initially it can be null.
When a patient appears for the consult, the attribute “AppearForConsult” value is true and the value is entered into the “GeneralPhysician_Number” attribute.
Appointment Type states the type of appointment made that is the appointment is made by the patient through call, through direct visit or by a general physician.
Treatment Status in the Treatment Records entity helps to understand the result of the treatment type(surgery or test) whether it is positive or negative in case of test and success or failure in case of surgery.
Each Specialist has a different kind of discount rates and they are maintained in the entity called ‘Specialists Discounts’.
- Discounts are given based on the type of criteria of the patients which are maintained in the Criteria Types
- History of the patient is saved in the Patient’s History entity.Each patient can have more than one mode (mode types include medicines, surgeries or allergies-At a time one Mode can be only of these which is identified using the IsAllergy,Is Current Medicine or Surgery columns.)
CQMC Database ERD Modeling | Assumptions Business Rules
Business Rules
The list of Business rules for the CQ medical center is as below
- CQMC is a medical center in which many specialists offer consultations.
- Patients can pre-book appointments to the specialists in CQMC either by a phone call or by a direct visit to the medical center.
- Even the general physicians can book appointment to the specialist consults for any particular patient.
- While booking an appointment, the patient details are stored in the system and each patient is uniquely identified using the PatientID.
- One general physician can refer one patient to a particular specialist any number of times.
- While booking appointments, patients past track of surgeries or any allergies and even the current medicines if any along with the dosage is stored.
- Upon consult, the specialist may conduct some tests and based on the problem can prescribe some medicines or even surgery.
- Each item in the treatment phase carries different fee and the consultation fee is mandatory for any consult.
- Some specialists will offer some discounts to the patients based on the some predefined conditions.
3. Logical Design
The logical representation of all the entities involved in the design of ER for CQ Medical Center is as below
- PatientRecords (PatientID Primary Key,PatientFirstName,PatientMiddleNam, PatientLastName, BloodGroupID Foreign Key References BloodGroups.BloodGroupID PatientAge, PatientPrimaryContact, PatientEmergencyContact, PatientCriteriaID Foreign Key References PatientCriteriaID)
- BloodGroups( BloodGroupID Primary Key, BloodGroupName)
- CriteriaTypes(PatientCriteriaID Primary Key, Criteria)
- SpecialistRecord(SpecialistID Primary Key, SpecialistFirstNAme, SpecialistMiddleName, SpecialistLastName, SpecialistEmailId)
- SpecialistExperience(SPExpID Primary Key, SpecialistID Foreign Key References SpecialistRecord.SpecialistID, ExperienceID Foreign Key References Experience.ExperienceID, Years)
- Special Discounts(Discount ID Primary Key, Patient Criteria Foreign Key References CriteriaType.Patient Criteria, Specialist ID Foreign Key References Specialist Record.SpecialistID)
- PatientAppointmentRecord(PAppointmentID Primary Key, PatientID Foreign Key References PatientRecords.PatientID, SpecialistID Foreign Key References SpecialistRecord.SpecialistID, PAppointmentDate, PAppointmentType, GeneralPhysician_Number, ApprearedForConsult)
- PatientHistory(HistoryID Primary Key, , PatientID Foreign Key References PatientRecords.PatientID, ModeIDForeign Key References TreatmentModes.ModeID)
- TreatmentModes(ModeID Primary Key, ModeName, IsAllergy, IsSurgery, IsCurrentMedicine)
- TreatmentRecord(TreatmentID Primary Key, Appointment Foreign Key References PatientAppointmentRecord.PAppointmentID,TypeID References TypeRecords.TypeID, TreatmentDate,TreatmentStatus)References Specialist
- TypeRecord(TypeID PrimaryKey, TypeName,Type Cost)
- TreatmentMedicine(MedicineID Primary Key, MedicineName, PAppointmentID Foreign Key References PatientAppointmentRecord.PAppointmentID, MedicineCost)
- Payment Invoice(PInvoiceID Primary Key, AppointmentS Foreign Key References Patient Appointment Record.AppointmentS, Treatment,DiscountED Foreign Key References Specialist Discounts.DiscountID, PInvoiceDate,PaymentBy, PaymentStatus)
