I have read that this can be a no-no, but I am wondering if this sounds like ALWAYS the situation. I have got a listing of "dining tables" that may accommodate a range of seats.
You can observe within the image that table_num 12 will accommodate two or three seats.
So, now my current option would be to question all of the table records (based on the user_index for undisclosed reasons) then loop with the results searching for tables that may fit 3 people (if it is three people searching for a table).
I actually do that using the array_implode() method (in php) around the 'seats' column of my return data. I seem like it is really an simpler solution than developing a separate 'seats' table after which setting a table_index and user_index to every seating possibility, after which needing to operate a second query to obtain the table_num within the original 'tables' table.
Essentially, by utilizing array_implode() I can circumvent another query. But, I'm not sure if that's less taxing. Or, possibly I'm passing up on some awesome query language (like relational table lingo?)
Produce a table that's known as something similar to
TableSeats. Inside it, you'd possess a
table_num along with a
seats column. Make use of the
table_num column like a foreign key. Then, to locate tables that chair 3 people, it would be like so:
select * from tables t where exists (select 1 from tableseats s where s.seats = 3)
The miracle this is actually the
exists clause. Certainly, you might do an
inner join, but I'd recommend
exists here, as this enables you to identify any tables that may chair a minimum of 7 people by saying
where s.seats >= 7, or perhaps a table that may chair between 5 and 8 individuals with
where s.seats between 5 and 8.
Correct me should i be wrong, but this doesn't seem a listing. It's a lot more like a variety (like, from 2 as much as 3 seats). So you might have a much better structure by changing the
seats area through the fields
max_seats. The queries are easy then.
Should you removed the
seats column out of this table after which were built with a new
seats table with three posts:
table_index pertains to
index inside your existing table (many-to-one because there might be multiple entires per table in
seats). The you can choose the tables you would like like so:
SELECT tables.* FROM tables INNER JOIN seats ON tables.index = seats.table_index WHERE seats.num_seats = ?