MySql store multiple references for another table inside one cell and select it?

mysql, php, select

Solution

A person can be related to zero, one or more goods. A good can be related to zero, one or more people.

That's a many-to-many relationship.

Normally, we handle this by creating a third table, which is a "relationship" that points to the other two tables

table: goods_owned
people_id goods_id
        1        1
        1        4
        1        3
        2        2
        2        5
        3        1
        3        5
        3        2
        4        5
        4        3
        4        4

The combination of these two columns can be specified as unique, and can serve as the primary key for the table. Each column can be defined as a foreign key to the parent tables.

CREATE TABLE goods_owned
( people_id  INT UNSIGNED NOT NULL
, goods_id   INT UNSIGNED NOT NULL
, PRIMARY KEY (people_id, goods_id)
-- , KEY FK_goods_owned_people (people_id)  -- redundant with PK
, KEY FK_goods_owned_goods (goods_id)
, CONSTRAINT FK_goods_owned_people FOREIGN KEY (people_id) REFERENCES people (id)
, CONSTRAINT FK_goods_owned_goods FOREIGN KEY (goods_id) REFERENCES goods (g_id)
) ;

Another alternative, if the set of "goods" is static and well-defined, and doesn't need to be represented in a table, then you could make use of the MySQL SET datatype, and have a single table. But that approach is only suitable if the set is static (won't need to be changed.)

Problem

I have two tables ``` table: people id name goods_owned 1 john 1,4,3 2 Mike 2,5 3 Sam 1,5,2 4 Andy 5,3,4 ``` ``` table goods: g_id g_name g_class 1 sugar food 2 salt food 3 boat transp 4 house habitation 5 car transp ``` this is simple example of table `goods` its actually very long and each person in `people` can have multiple `goods` assigned to him e.g. car, boat, sugar etc there is no limitation of how much one person can have and its totally random. I could not find better way to store it than coma separated e.g. `1,5,3` I am having the problem making selection i need to have e.g. ``` SELECT people.*, goods.name FROM people LEFT JOIN goods ON goods.g_id = people.goods_owned WHERE name = "Sam" ``` However problem is goods_owned has multiple ID's of goods in cell and they need to be somehow broken down to get answer: ``` 1, Sam, sugar, car, salt ``` If you know better alternative to storing (e.g 100) multiple values coma separated in one cell, then please let me know.

Original source