How to write a query, with multiple foreign key in mysql

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)
)

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

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)
);

StackExchange Database Administrators Q#101969, answer score: 3