3

I am working on a simple PHP and MySQL college level clinic management system. There 4 main table which relation with foreign key. I'm little bit confusing multiple foreign key in table .. what would be MySQL query according tables specially Fee Transaction table ... help will appreciated...thanks

I did try this:

CREATE TABLE patient_appointment
(
appointment_ID int NOT NULL,
Date int NOT NULL,
time int,
PRIMARY KEY (appointment_ID),
CONSTRAINT fk_P_id FOREIGN KEY (patient_id)
REFERENCES patient(patient_id)
)

Double level . please check its right or wrong

CREATE TABLE patient_treatment
(
patient_treatment_no int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (patient_treatment_no),
CONSTRAINT fk_P_id FOREIGN KEY (Patient_id)
REFERENCES Patient(Patient_ID),
CONSTRAINT fk_t_id FOREIGN KEY (treatment_id)
REFERENCES treatment(treatment_id)
)

enter image description here

CREATE TABLE IF NOT EXISTS `patient` (
  `p_id` int(11) NOT NULL AUTO_INCREMENT,
  `fn` varchar(100) NOT NULL,
  `ln` varchar(100) NOT NULL,
  `father_name` varchar(100) NOT NULL,
  `contact` varchar(100) NOT NULL,
  `sex` varchar(20) NOT NULL,
  `DOB` varchar(100) NOT NULL,
  PRIMARY KEY (`p_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;

=====================

CREATE TABLE IF NOT EXISTS `patient_treatment` (
  `p_t_no` int(11) NOT NULL AUTO_INCREMENT,
  `treatment_Fee` varchar(200) NOT NULL,
  PRIMARY KEY (`p_t_no`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS `treatments` (
  `t-id` int(111) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) NOT NULL,
  `fee` varchar(20) NOT NULL,
  PRIMARY KEY (`t-id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Fee Transaction
Contains record of all the fee transactions carried out for each appointment by each patient.
-   Transaction_ID
-   Appointment_ID (Foreign Key)
-   Patient_ID (Foreign Key)
-   Treatment_ID (Foreign Key)
-   Treatment_Fee**

I don't know how to make table... help me please

0
3

Run the script below (in order). Take a look at the image.

You don't need a transaction_fee table - this information is already in the treatment and medical_procedure tables.

A few words of advice.

You should take a look at Open Source medical practice and/or hospital management software. You may get some ideas there (maybe even better than mine!) - you can pick and mix which ideas you choose to copy/adopt/modify.

The recommendations below about naming are my preferences - the ABSOLUTELY CRITICAL thing is to have a naming convention and STICK to it!

Keep all table names lower case, that way they are easy to distinguish from SQL using upper case.

Separate words using underscore_case - i.e. order_no.

Keep table names singular - tables are collections, so a singular title is appropriate. This one can provoke (almost religious) debate - as above, just pick one (singular or plural) and stick to it. The only reason to breach this rule is for SQL keywords!

If some appointments are with a nurse, you could have a table called medical_professional with a link to a staff_table_id - didn't have time for that.

CREATE TABLE IF NOT EXISTS `users` -- didn't pick "user" - it is a singular SQL keyword!
(
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(100) NOT NULL,
  `password` varchar(100) CHARACTER SET utf8 NOT NULL,
  `status` int(11) NOT NULL,
  `accessLevel` varchar(100) NOT NULL,
  `fk_id` int(11) DEFAULT NULL,
  `p_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `p_id` (`p_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

CREATE TABLE IF NOT EXISTS `patient` 
(
  `patient_id` int(11) NOT NULL AUTO_INCREMENT,
  `fn` varchar(100) NOT NULL,
  `ln` varchar(100) NOT NULL,
  `father_name` varchar(100) NOT NULL,
  `contact` varchar(100) NOT NULL,
  `sex` varchar(20) NOT NULL,
  `DOB` varchar(100) NOT NULL,
  PRIMARY KEY (`patient_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;



CREATE TABLE IF NOT EXISTS doctor
-- every appointment must have a doctor (or nurse - see above)
(
  doctor_id int not null auto_increment,
  doc_name VARCHAR(50) NOT NULL,
  speciality VARCHAR(100),
  PRIMARY KEY (doctor_id)
);


CREATE TABLE treatment
(
  treatment_id INT NOT NULL AUTO_INCREMENT,
  treatment_fee INT NOT NULL,
  treatment_name VARCHAR(200),
  PRIMARY KEY (treatment_id)
);


CREATE TABLE appointment 
-- an appointment must be with a patient
-- appointment_patient_ is redundant.
(
  appointment_id int NOT NULL,
  doctor_id int NOT NULL,
  patient_id int NOT NULL,
  Date int NOT NULL,
  time int,
  PRIMARY KEY (appointment_id),
  CONSTRAINT fk_ap_pa_id FOREIGN KEY (patient_id)
  REFERENCES patient(patient_id),
  CONSTRAINT fk_ap_do_id FOREIGN KEY (doctor_id)
  REFERENCES doctor (doctor_id)
);

CREATE TABLE medical_procedure  -- and not patient_treatment, English is easier.
(
  medical_procedure_id int NOT NULL,
  order_no int NOT NULL,
  patient_id int NOT NULL,
  doctor_id INT NOT NULL,  -- each treatment must have a doctor (at least supervising)
  treatment_id int NOT NULL,
  PRIMARY KEY (medical_procedure_id),
  CONSTRAINT fk_mp_pa_id FOREIGN KEY (patient_id)
  REFERENCES patient(patient_id),
  CONSTRAINT fk_mp_tr_id FOREIGN KEY (treatment_id)
  REFERENCES treatment(treatment_id),
  CONSTRAINT fk_mp_do FOREIGN KEY (doctor_id)
  REFERENCES doctor(doctor_id)
);

image of database schema

1
  • Comments are not for extended discussion; this conversation has been moved to chat.
    – Paul White
    Aug 29 '17 at 9:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.