conan conan - 4 months ago 9
MySQL Question

Which is the better way to create my mysql table?

Based on my situation, should I combine all symptoms into one row as my method 1 below or method 2 that create more rows. The reason I don't like method 1 is that I have to use - to separate each symptom, and later I need to use php explode('-') to separate them and use LIKE to match them.

Method 2 will create more rows, and I think I will create more table to separate them.

Method 1:

disease symptoms
HIV pain-cough-hair loss
Flu cought-running nose-fever
cacer lose weight-fever-fatigue


Method 2:

disease symptoms
HIV pain
HIV cough
HIV hair loss
... ...
... ...

Answer

Out of your two methods, method 2 would be preferred. As @JNevill notes, storing multiple pieces of data in one column becomes a nightmare when searching or filtering data.

My full recommendation would be to use option 3 however. Take a look at the below design:

Table 1: DISEASES

+------+-----------+
|  id  |  name     |
+------+-----------+
|    1 | HIV       |
|------|-----------|
|    2 | FLU       |
|------|-----------|
|    3 | Cancer    |
+------+-----------+

Primary Key:

  • id

Table 2: SYMPTOMS

+------+-----------+
|  id  |  name     |
+------+-----------+
|    1 |  pain     |
|------|-----------|
|    2 | cough     |
|------|-----------|
|    3 | hair-loss |
+------+-----------+

Primary Key:

  • id

Table 3: DISEASES-SYMPTOMS

+-------------+--------------+
|  diseas_id  |  symptom_id  |
+-------------+--------------+
|          1  |           1  |
|-------------|--------------|
|          1  |           2  |
|-------------|--------------|
|          1  |           3  |
+-------------+--------------+

Primary Key:

  • (disease_id, symptom_id)

Foreign Keys:

  • DISEASES.id -> DISEASES_SYMTPOMS.disease_id
  • SYMTPOMS.id -> DISEASES_SYMTPOMS.symptom_id

Establish your base tables DISEASES and SYMPTOMS. Then establish a 3rd table representing a JOIN of the first two tables. This normalization of the data will simply the structure of your application and prevent duplication of data since each disease can have multiple symptoms and each symptom can belong to multiple disease.

SAMPLE QUERY (MySQL):

SELECT
`d`.`id`,
`d`.`name`,
`s`.`name
FROM `DISEASES` as `d`
INNER JOIN `DISEASES_SYMPTOMS` AS `ds` ON `d`.`id` = `ds`.`disease_id`
INNER JOIN `SYMPTOMS` AS `s` ON `ds`.`symptom_id` = `s`.`id`;

SAMPLE QUERY RESULT:

+------+----------------+----------------+
|  id  |  disease_name  |  symptom_name  |
+------+----------------+----------------+
|   1  | HIV            |  pain          |
|------|----------------|----------------|
|   1  | HIV            |  cough         |
|------|----------------|----------------|
|   1  | HIV            |  hair-loss     |
+------+----------------+----------------+