Mysql multiple values in one cell

I am designing an app where I want to search for diseases based on symptoms. I am trying to design the database using MySql, but as a beginner I run into some trouble with the design philosophy.

Basically a disease is going to have multiple symptoms, so on the disease table I wanted to have these columns:

disease_id, disease_name, disease_description, **disease_symptoms** 

The thing is, one disease is not going to have only a single symptom, and putting multiple values in a single cell goes against DB design philosophy from what I found out online.

So the best solution I found so far was to make a table with multiple columns, 1 for disease id and then like 10 columns called: symptom_0, symptom_1, symptom_2, ect. So when I do a SQL query it can return all the diseases that have a specific symptom.

I was just wondering if there is a more efficient way to do this.

Unless each date is a specific property (start date, end date, due date, date created, ...) then storing them as columns is probably bad form.

It is likely that you would be better storing the dates in separate rows like so:

ItemDates
===========
ID     (PK)
ItemId 
Date

so:

ID   Item   Date
 1   1      2018-05-19   
 2   1      2018-04-09
 3   1      2018-08-09
 4   2      2016-01-30
 5   2      2017-05-04
 6   2      2016-06-07
 7   2      2020-05-17
 8   2      2021-06-23  
 9   3      2001-05-06
10   4      2018-11-05
11   4      2013-06-09

Or if the dates will be unique per item, you can do without the surrogate key (ID) and declare ItemId,Date to be the primary key.

If you are not careful this could turn into a property bag (the Entity/Attribute/Value or EAV pattern, which is often considered an anti-pattern) though. For more specific advice for your use case, you should update the question with a bit more information about what the date values represent in the system that you are modelling.

In SQL, for  matching multiple values in the same column, we need to use some special words in our query. Below, 3 methods are demonstrated to achieve this using the IN, LIKE and comparison operator(>=). For this article, we will be using the Microsoft SQL Server as our database.

Step 1: Create a Database. For this use the below command to create a database named GeeksForGeeks.

Query:

CREATE DATABASE GeeksForGeeks

Output:

Step 2: Use the GeeksForGeeks database. For this use the below command.

Query:

USE GeeksForGeeks

Output:

Step 3: Create a table CARS inside the database GeeksForGeeks. This table has 3 columns namely CAR_NAME, COMPANY and COST containing the name, company and cost of various cars.

Query:

CREATE TABLE CARS(
CAR_NAME VARCHAR(10),
COMPANY VARCHAR(10),
COST INT);

Output:

Step 4: Describe the structure of the table CARS.

Query:

EXEC SP_COLUMNS CARS;

Output:

Step 5: Insert 5 rows into the CARS table.

Query:

INSERT INTO CARS VALUES('INNOVA','TOYOTA',10000);
INSERT INTO CARS VALUES('CAMRY','TOYOTA',20000);
INSERT INTO CARS VALUES('CIAZ','HONDA',30000);
INSERT INTO CARS VALUES('POLO','VOLKSWAGEN',50000);
INSERT INTO CARS VALUES('BENZ','MERCEDES',100000);

Output:

Step 6: Display all the rows of the CARS table.

Query:

SELECT * FROM CARS;

Output:

Step 7: Retrieve the details of all the cars belonging to the companies TOYOTA and HONDA.

Note – Use of IN for matching multiple values i.e. TOYOTA and HONDA in the same column i.e. COMPANY.

Syntax:

SELECT * FROM TABLE_NAME WHERE COLUMN_NAME IN (MATCHING_VALUE1,MATCHING_VALUE2);

Query:

SELECT * FROM CARS WHERE COMPANY IN ('TOYOTA','HONDA');

Output:

Step 8: Retrieve the details of all the cars whose name starts with the letter C.

Note – Use of LIKE for matching multiple values i.e. CAMRY and CIAZ in the same column i.e. CAR_NAME.

Syntax:

SELECT * FROM TABLE_NAME WHERE COLUMN_NAME LIKE 'STARTING_LETTER%';

Query:

SELECT * FROM CARS WHERE CAR_NAME LIKE 'C%';

Output:

Step 9: Retrieve the details of all the cars having cost greater than or equal to 30000.

Note – Use of comparison operator >= for matching multiple values i.e. 30000, 50000 and 100000 in the same column i.e. COST.

Syntax:

SELECT * FROM TABLE_NAME WHERE COLUMN_NAME >=VALUE;

Query:

SELECT * FROM CARS WHERE COST>=30000;

Output:

How can I store multiple values in one cell in MySQL?

How do I put multiple values in one cell in SQL?

How do I store multiple values in one column in SQL?

How do I put multiple values in one column?