how to create a database schema for storing product search filters

Question

I am creating a simple product search webapp where user can filter the shopping products he has bookmarked on the basis of price, free delivery, name, shop etc.

I am looking to implement custom search filters functionality where user can create a search filter, then save it for later use. Search filters are something like this:

• price is under $1000
• price is between $1000 to $2000
• free delivery available/not available
• shop is amazon/walmart/...
• name contains "christmas"

Can somebody give me an idea of the database schema for storing such search filters in database. Should I store the mysql clauses in database like "WHERE price < 1000", "WHERE free_delivery=1"... or may be create fields like this..

• field (price, name, free_delivery)
• value (1000)
• expr (less than, greater than)
• between (1000)
• and (2000)
• contains

Answer 1

You shouldn't store SQL clauses in the database. I would recommend you do something like this:

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

Example rows: (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 row: (1, 43, "amazon christmas", 1000, 1999, 1, True, "christmas")

The above example would save a filter called "amazon christmas" associated with user 43. It specifies that the user wishes to look for products with price ranging from $1000 to $1999 at Walmart, with free delivery, and containing the keyword "christmas."