0

I have 2 tables, that is

Patient Treatment

Datetime Doctor Patient Complaint Description Medicine Address
1/1/22 17:05 Name A Patient K X X X X
1/1/22 17:11 Name C Patient L X X X X
1/1/22 17:27 Name B Patient M X X X X
1/1/22 17:41 Name A Patient N X X X X
2/1/22 08:38 Name A Patient K X X X X

Doctor Personal Data

Doctor Specialist Join Date Degree Hour Work Phone Number Address
Name A X X X X X X
Name B X X X X X X
Name C X X X X X X
Name D X X X X X X

I want to create a relation for both tables. The doctor's name in the Patient Treatment table refers to the doctor's name in Doctor Personal Data to retrieve the doctor's personal data. The Doctor Personal Data table has a unique column (name of doctor). However, I read in several questions on this stackexhange that the primary key being a string is not a good way to go because it's too slow. The two tables don't have an ID number like in most SQL tutorials. What is the best way to determine the primary key and foreign key for both tables?

This is my DDL query

CREATE TABLE `patient_treatment` (
      `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
      `Datetime` timestamp NULL DEFAULT NULL,
      `Doctor` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
      `Patient` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
      `Complaint` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
      `Description` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
      `Medicine` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
      `Address` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
      PRIMARY KEY (`id`),
    ) 

CREATE TABLE `doctor_personal_data` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `Doctor` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `Specialist` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `Join Date` timestamp NULL DEFAULT NULL,
  `Degree` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `Hour Work` timestamp NULL DEFAULT NULL,
  `Phone Number` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `Address` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`id`),
) 
5
  • Please share your query so far. Dec 21, 2022 at 12:20
  • Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer.
    – Community Bot
    Dec 21, 2022 at 12:20
  • "The two tables don't have an ID number" - Your DDL shows that they both do have ID number fields.
    – J.D.
    Dec 21, 2022 at 12:58
  • @J.D. i mean no ID number field for raw data
    – Herza Ryo
    Dec 21, 2022 at 13:00
  • Not sure I understand what you mean by raw data, is that a 3rd table of which you didn't provide DDL for? In any case, just add the ID field from one table to the other table to be your foreign key, like Niyaz's answer demonstrates.
    – J.D.
    Dec 21, 2022 at 13:05

2 Answers 2

0

You can use FOREIGN KEY instead of the name (Doctor). Follow the steps to change your tables:(Backup your database before starting)

Step1: Add the FOREIGN KEY column and connect with the Primary Key

ALTER TABLE `patient_treatment` ADD doctor_id bigint(20) UNSIGNED NOT NULL DEFAULT 0;
ALTER TABLE `patient_treatment` ADD CONSTRAINT fk_id FOREIGN KEY (doctor_id) REFERENCES `doctor_personal_data`(id);

Step 2: Update new column doctor_id in patient_treatment table:

UPDATE `patient_treatment` 
SET `patient_treatment`.`doctor_id` = (
    SELECT 
        `doctor_personal_data`.`id`
    FROM 
        `doctor_personal_data`
    WHERE 
        `doctor_personal_data`.`Doctor` = `patient_treatment`.`Doctor`
);

Step 3: delete the Doctor column. (You should be careful about this step, only run this query if the previous steps are successfully implemented)

ALTER TABLE `patient_treatment` DROP COLUMN `Doctor`;

Now you can join them easily like below:

SELECT *
FROM `patient_treatment`
INNER JOIN `doctor_personal_data`
ON `patient_treatment`.`doctor_id` = `doctor_personal_data`.`id`;
2
  • If i update data for table patient_treatment, i must do step 2 again? is there a way to be more efficient when the data is very large?
    – Herza Ryo
    Dec 21, 2022 at 13:24
  • Based on my understanding of your question, It is a one-time process. Afterward, you add data to the doctor_id column instead of the Doctor column. I think it takes a couple of minutes to run those queries. If your sample is more than 1M, then adding a temporary index for the Doctor column may be helpful. After the update, you can delete the index.
    – Niyaz
    Dec 21, 2022 at 14:58
0

has a unique column (name of doctor)

No. It is really possible that two doctors can have the same name. It should be an INDEX, but not UNIQUE. It is even more likely that two patients can have the same name.

Hence, the table doctor_personal_data needs a surrogate id as you have done.

patient_treatment should use doctor_id, not the doctor's name. Ditto for patient_id. These become FOREIGN KEYs to doctor_personal_data.id and patient_data.id.

You list medicine, but there are likely to be many medicines in a single "treatment". This implies that you might need a table that talks about one treatment for one person, with a many-to-many mapping from that table to an other medicines table.

Oh, and you probably need to say which of several doctors prescribed that medicine.

Summarizing: You have these "Entities", each with an id:

Patients
Doctors
Ailments (one patient being treated for one injury/disease)
Medicines

And then lots of relationships. There are two important type of SQL relationships:

  • 1-to-many -- an id in one table pointing to the other (no extra table)
  • many-to-many -- needs an extra table with 2 ids

Once you have that mapped out 'correctly', the FKs are rather obvious.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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