How to create a database schema for storing product search filters

Question

I am creating a simple website for searching for products where a user can filter the trading products that he has laid down based on price, free delivery, name, store, etc.

I am looking to implement the functions of custom search filters where a user can create a search filter and then save it for later use. Search filters look something like this:

• price below $ 1,000
• price from $ 1000 to $ 2000
• free shipping / not available
• amazon / walmart / ... store
• the name contains "christmas".

Can someone give me an idea of ​​a database schema for storing such search filters in a database. Should I store mysql sentences in a database such as "WHERE price <1000", "WHERE free_delivery = 1" ... or can create such fields.

• (price, name, free_ shipping)
• value (1000)
• expr (less, more)
• between (1000)
• and (2000)
• contains

Answer 1

You should not store SQL statements in a database. I would recommend you do something like this:

CREATE TABLE store ( id INT NOT NULL PRIMARY KEY, name VARCHAR(100) ); 

Examples of lines: (1, "Amazon"), (2, "Walmart"), ...

 CREATE TABLE search_filter ( id INT NOT NULL PRIMARY KEY, user_id INT NOT NULL, name VARCHAR(100), price_min INT, price_max INT, store_id INT, has_free_delivery BOOLEAN, keyword VARCHAR(50), FOREIGN KEY (user_id) REFERENCES user(id), FOREIGN KEY (store_id) REFERENCES store(id) ); 

Example line: (1, 43, "amazon christmas", 1000, 1999, 1, True, "christmas")

In the above example, a filter called "amazon christmas" will be saved associated with user 43. It indicates that the user wants to search for products with prices between $ 1,000 and $ 1,999 in Walmart with free shipping and contain the keyword "christmas." "