CODEWITHSUNDEEP
phpmysqldatabase
Howard Zoopaloopa
Howard Zoopaloopa

Reputation: 3822

Storing comma-delimited data in MySQL

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.
enter image description here

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

Upvotes: 3

Views: 643

Answers (3)

Max Spencer
Max Spencer

Reputation: 1719

If you removed the seats column from this table and then had a new seats table with three columns: id, table_index, and num_seats, where table_index relates to index in your existing table (many-to-one as there may be multiple entires per table in seats). The you could select the tables you want like so:

SELECT tables.* FROM tables
INNER JOIN seats ON tables.index = seats.table_index
WHERE seats.num_seats = ?

Upvotes: 2

linepogl
linepogl

Reputation: 9335

Correct me if I am wrong, but this does not sound a list. It is more like a range (like, from 2 up to 3 seats). So you could have a better structure by replacing the seats field by the fields min_seats and max_seats. The queries are straight forward then.

Upvotes: 4

Eric
Eric

Reputation: 95123

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.

Upvotes: 4

Related Questions