mysql saving and selecting many options

I’m developing an auctioning website. Each auction has a lot of options, that i want to be able to filter on the front-end.

I was wondering what is best practice in handling/storing these options (mostly booleans/checkboxes).

• Is it best practice to store them all in the same row ?
• Or would it be better to store them all in one “auction_options” table?

I guess the last option is the best one..but how would a query look like to: select all auctions that have:

• state ohio
• state utah
• some other option

I just can't get my head around this. Is this even possible ?

EDIT --> the reply to comment field was not enough

I created 3 tables:

• auction table -> a_id | a_desc | etc
• a_options table -> a_o_id | a_o_name | a_o_value
• a_options2auction table -> id | a_id | a_o_id

when i run this query:

SELECT * FROM auctions as A INNER JOIN a_options2auction as B ON A.a_id = B.a_id INNER JOIN a_options as C ON B.a_o_id = C.id WHERE c.value 开发者_运维知识库= 'groningen' OR c.value = 'utrecht'

it returns 2 rows that are both the same auction, that should not be possible :)

---

You can use an options table that is connected M-N to the auctions table.

Between these two tables you should build another table to connect them.

Auctions 
    |
Auctions_options
    |
Options

So a query would be like this

SELECT * FROM Auctions as A 
INNER JOIN Auctions_Options as B ON A.id_auction = B.id_auction 
INNER JOIN Options as C ON B.id_Option = C.id_Option 
WHERE C.Option_name = 'State' AND (B.value = 'ohio' OR B.value='utah' OR B.value = '...')

This solutions allow you to add infinites options to an auction and store their value (each auction can have different options from the others too)

---

The second option is better - there is no need to fill unnecessary fields. It will store only the options nedded for an auction.

As per the query:

SELECT
  *
FROM 
  Auctions
WHERE state_id in (1, 2)

The state_id will come from some check-boxes.

---

For your second question ("how would a query look like to..") this should be what you're looking for:

SELECT * FROM auctions WHERE state=ohio OR state=utah

I don't really understand the first question though..could you please be clear? Anyway given a table 'auctions' I would store every single auction with all of its data (so I guess this is what you mean for booleans/checkboxes) in here..

but it all depends on what kind of 'options' you are storing ,quantity and you taste!If for example you need to store auction 'participants' option then go for creating another table 'partecipants' linked with foreign-keys id-fields to your 'auctions' table.

You may want to use INNER JOIN