Matching delimited string to table rows

Question

So I have two tables in this simplified example: People and Houses. People can own multiple houses, so I have a People.Houses field which is a string with comma delimeters (eg: "House1, House2, House4"). Houses can have multiple people in them, so I have a Houses.People field, which works the same way ("Sam, Samantha, Daren").

I want to find all the rows in the People table corresponding to the the names of people in the given house, and vice versa for houses belong to people. But I can't figure out how to do that.

This is as close as I've come up with so far:

   SELECT People.* 
     FROM Houses 
LEFT JOIN People ON Houses.People Like CONCAT(CONCAT('%', People.Name), '%')
    WHERE House.Name = 'SomeArbitraryHouseImInterestedIn'

But I get some false positives (eg: Sam and Samantha might both get grabbed when I just want Samantha. And likewise with House3, House34, and House343, when I want House343).

I thought I might try and write a SplitString function so I could split a string (using a list of delimiters) into a set, and do some subquery on that set, but MySQL functions can't have tables as return values.

Likewise you can't store arrays as fields, and from what I gather the comma-delimited elements in a long string seems to be the usual way to approach this problem.

I can think of some different ways to get what I want but I'm wondering if there isn't a nice solution.

Answer 1

Likewise you can't store arrays as fields, and from what I gather the comma-delimited elements in a long string seems to be the usual way to approach this problem.

I hope that's not true. Representing "arrays" in SQL databases shouldn't be in a comma-delimited format, but the problem can be correctly solved by using a junction table. Comma-separated fields should have no place in relational databases, and they actually violates the very first normal form.

You'd want your table schema to look something like this:

CREATE TABLE people (
   id int NOT NULL,
   name varchar(50),
   PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE houses (
   id int NOT NULL,
   name varchar(50),
   PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE people_houses (
   house_id int,
   person_id int,
   PRIMARY KEY (house_id, person_id),
   FOREIGN KEY (house_id) REFERENCES houses (id),
   FOREIGN KEY (person_id) REFERENCES people (id)
) ENGINE=INNODB;

Then searching for people will be as easy as this:

SELECT  p.* 
FROM    houses h
JOIN    people_houses ph ON ph.house_id = h.id
JOIN    people p ON p.id = ph.person_id
WHERE   h.name = 'SomeArbitraryHouseImInterestedIn';

No more false positives, and they all lived happily ever after.

Answer 2

The nice solution is to redesign your schema so that you have the following tables:

People
------
PeopleID (PK)
...


PeopleHouses
------------
PeopleID (PK) (FK to People)
HouseID (PK) (FK to Houses)


Houses
------
HouseID (PK)
...