Storing comma-delimited data in MySQL

Question

I've read that this is a no-no, but I'm wondering if this is ALWAYS the case. I've got a list of "dining tables" that can accommodate an array of seats.

You can see in the image that table_num 12 will accommodate 2 OR 3 seats.

So, now my current solution is to query all the table records (according to the user_index for undisclosed reasons) then loop through the results looking for tables that can fit 3 people (if it happens to be three people looking for a table).

I do that by using the array_implode() method (in php) on the 'seats' column of my return data. I feel like this is an easier solution than creating a separate 'seats' table and then assigning a table_index and user_index to each seating possibility, and then having to run a second query to find the table_num in the original 'tables' table.

Basically, by using array_implode() I am able to circumvent a second query. But, I don't know if that is less taxing. Or, possibly I am missing out on some awesome query language (like relational table lingo?)

Answer 1

Create a table that's called something like TableSeats. In it, you'd have a table_num and a seats column. Use the table_num column as a foreign key. Then, to find tables that seat 3 people, it'd be like so:

select
    *
from
    tables t
where
    exists (select 1 from tableseats s where s.seats = 3)

The magic here is the exists clause. Certainly, you could also do an inner join, but I'd recommend exists here, because this allows you to find any tables that can seat at least 7 people by saying where s.seats >= 7, or even a table that can seat between 5 and 8 people with where s.seats between 5 and 8.