Posted By: Anonymous
I have two tables in MySQL. Table Person has the following columns:
id | name | fruits
fruits column may hold null or an array of strings like (‘apple’, ‘orange’, ‘banana’), or (‘strawberry’), etc. The second table is Table Fruit and has the following three columns:
____________________________ fruit_name | color | price ____________________________ apple | red | 2 ____________________________ orange | orange | 3 ____________________________ ...,...
So how should I design the
fruits column in the first table so that it can hold array of strings that take values from the
fruit_name column in the second table? Since there is no array data type in MySQL, how should I do it?
The proper way to do this is to use multiple tables and
JOIN them in your queries.
CREATE TABLE person ( `id` INT NOT NULL PRIMARY KEY, `name` VARCHAR(50) ); CREATE TABLE fruits ( `fruit_name` VARCHAR(20) NOT NULL PRIMARY KEY, `color` VARCHAR(20), `price` INT ); CREATE TABLE person_fruit ( `person_id` INT NOT NULL, `fruit_name` VARCHAR(20) NOT NULL, PRIMARY KEY(`person_id`, `fruit_name`) );
person_fruit table contains one row for each fruit a person is associated with and effectively links the
fruits tables together, I.E.
1 | "banana" 1 | "apple" 1 | "orange" 2 | "straberry" 2 | "banana" 2 | "apple"
When you want to retrieve a person and all of their fruit you can do something like this:
SELECT p.*, f.* FROM person p INNER JOIN person_fruit pf ON pf.person_id = p.id INNER JOIN fruits f ON f.fruit_name = pf.fruit_name