Want to know best design practices when you want to filter a table with multiple conditions in RDB

Isn't it an honest design to divide it into two tables?

CREATE TABLE sample(
  id integer,
  name text
);

US>CREATE TABLE CONDITION(
  sample_id integer,
  n integer, --conditionN
  v integer -- Value
);

--condition1=2 AND condition9=1
SELECT* FROM sample WHERE id in(
  SELECT sample_id FROM condition WHERE(n,v) = (1,2)
  INTERSECT 
  SELECT sample_id FROM condition WHERE(n,v) = (9,1)
);

-- (condition 1 = 1 AND condition 3 = 1) OR condition 4 = 3
SELECT* FROM sample WHERE id in(
  (SELECT sample_id FROM condition WHERE(n,v) = (1,1)
   INTERSECT 
   SELECT sample_id FROM condition WHERE(n,v)=(3,1))
  union
  SELECT sample_id FROM condition WHERE(n,v) = (4,3)
);

If you do this, you will be able to increase the number of N without changing the DB design.

When I checked the SELECT statement above in http://sqlfiddle.com, it worked in PostgreSQL 9.3 but not in MySQL 5.6, because condition is a reserved word and the INTERSECT feature does not exist.
According to the tag, MySQL is used, so please be careful when selecting.

Validation Data

INSERT INTO sample VALUES
  (1, 'one')
 ,(2, 'two')
 ,(3, 'three')
 , (4, 'four')
;
INSERT INTO CONDITION VALUES
  (1, 1,2)
 ,(1, 9,0)
 ,(2, 1,1)
 ,(2, 3,1)
 ,(2, 4,0)
 ,(3, 4,3)
 ,(4, 1,2)
 ,(4, 9,1)
;

It's hard to say "design."
The conditional expression is AND or OR ( ) and it is too complicated.
(I thought about putting the conditions on a separate table, but it seems troublesome.)

I'm sorry.All I could think of was this pathetic method.
Not the best design.I'm sorry that the design has led to an approach to how to get your search results.

 DELIMITER//
CREATE PROCEDURE SAMPLE()
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION SELECT NULL FROM DUAL WHERE FALSE;
  SELECT* FROM sample_table WHERE<conditions>;;
END
//
DELIMITER;
CALL SAMPLE;
DROP PROCEDURE IF EXISTS SAMPLE;

If there is a mistake in the condition by doing this,
(Example: condition 9 does not exist but condition 9 is specified)
Catch SQLEXCEPTION and SELECT NULL FROM DUAL WHERE FALSE.
The result of SELECT NULL FROM DUAL WHERE FALSE is EmptySet.

Every time I make a procedure and throw it away repeatedly.It's very eco-friendly.

Answer the question "I want to use that column for SQL conditioning in a table with variable length columns.

My answer is how to write Oracle, but please refer to it

First obtain the column name or count number of the table with variable length columns.

select columnn_name from all_tab_columns where table_name='sample_table' and columnn_name like'condition%';

I was wondering if I could build and branch SQL with the program based on the return value

It can also be incorporated into dynamic SQL.
http://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q1297167927
http://docs.oracle.com/cd/E16338_01/appdev.112/b56260/dynamic.htm#CACDDACH

...
If the contents of conditionN are fixed in a single digit integer, as shown in the example, stop the variable columns in the table. Put the contents of conditionN in order in one varchar column to specify the character positioning
I think it would be better to take it out/judge it with substr etc.

 select * from sample_table where (condition 1 = 1 AND condition 3 = 1) OR condition 4 = 3;

You can write with pattern matches like ↓, and you can use regular expressions with regexp_like.

select* from sample_table where condition like '1 ?1% 'or condition like' ???3%';

I misunderstood that it was the Rails design method.
Ignore this answer.

"Since ""I don't even know the clue,"" I thought maybe you don't even know the scope of Rails."

So please check out the feature called scope.

If you search the internet, many sites will be hit.

For example, isn't the code example shown on the following site similar to the question?

http://ruby-rails.hatenadiary.com/entry/20140814/1407994568

You can merge scopes to achieve AND conditions for where clauses.

#app/models/user.rb
class User<ActiveRecord::Base
  scope:inactive, -> {where state: 'inactive'}
  scope:finished, -> {where state: 'finished'}
end

# How to Use
User.inactive.finished
# =>SELECT "users".* FROM "users" WHERE "users". "state" = 'inactive' AND "users". "state" = 'finished'

Also, when creating a search form, it is convenient to use a gem called Ransack.
There is also a lot of information on the Internet.
Here is the link for your reference.

http://qiita.com/LuckOfWise/items/e020e896e71d47d0c6a4

Did you get the clue?
That's all for your information.