Database Design for Easy Queries/Filtering

Help and Feedback Scripting Support
, ,
#1

Hello. I’m working on a system where I want to be able to filter data by certain properties. (1 or more). I might want to do a query where I filter “state” within “type” or “type” by “state”.

Say I had a food menu. I might want to say… give me all burgers that have lettuce… or I might want to say give me all items that have lettuce… okay out of how may of those are burgers.

I’ve worked with SQL in the past for web development but as this is a small-scale project I don’t want to seek an external solution yet and am curious how to solve this data issue for easy filtering.

I’m looking for algorithms/data structures that will make this possible. Feel as if it has something to do with a hash table but not sure how to design it in a way that make easy querying possible.

#2

the way third party systems make that work, is by giving each item a type, so lets say we have a menu again, and theres a veggie burger, and then a regular burger, you would give each burger different properties, like veggie burger could have the types of: “burger”, “vegan”, “meal”, and etc, now when you filter by type, and put type, lets say “vegan” it will show the veggie burger but not the other because it doesnt have the same type.

thats how it works simplified, basically each thing is a different type, and when you filter it, it only shows things that have the same type as you selected