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

Question

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.

Answer 1

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.)