Determine Primary and Foreign Key for two tables

Question

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

Answer 1

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

Answer 2

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.