0

I'm designing an application that retrieves data from a (currently simple) .csv file and stores it's raw content. The application is intended to offer the user the possibility to define filters on the data and thus define sub-groups of data based on these filters. By filter, I mean a set of conditions that could be applied on the raw data. So I started thinking about the best way to model and store any filter in order to display those filters in the user interface once the application has started.

My first approach was to define what a condition really is. I thought of it as a set of 2 operands and an operator which would give something like "Operand_A Operator Operand_B" once injected in dynamic SQL.

Since an operand can either be a constant, a variable (property) or another condition, I set up this model :

filter class model

I could create a filter class pointing to a set of conditions, but since a condition can contain several conditions, I thought it would be pointless...

So my question is : would you change anything to this model ? Have I missed anything ?

I know inheritance ins't really fitted for relational databases... but I'll deal with it later. I also read some people suggesting that it would be simpler to store the filter query itself, but this is not what I'm looking for.

I'm using SQL Server (LTS version).

Improve this question
7
  • Are the filters intended to be used over an actual table in the database? Or are they filters over some application-side data? If the former, it might make sense to build up some actual SQL (programattically, not using user input directly) and then just store that. Then you can execute it dynamically.
    – Charlieface
    Sep 29, 2022 at 10:10
  • Your current model doe not take into account the recursive nature of expressions, and it doesn't allow functions to be used
    – Charlieface
    Sep 29, 2022 at 10:12
  • @Charlieface Good question. I intend to use it on a specific table in the database, but i'm considering making it applicable on any relevant table. For the recursion, since a condition can involve an operand, which can be a condition itself, does that not make the job ? But I should guarantee that a condition should not be able to involve itself, which would create an endless recursion.
    – Axel Samyn
    Sep 29, 2022 at 11:23
  • @Charlieface You are right, I did not think of functions. I will try adding this notion.
    – Axel Samyn
    Sep 29, 2022 at 11:25
  • Food for thought: "i'm considering making it applicable on any relevant table" - Any table you make it applicable to will need the same schema (i.e. columns) to ensure the expression in the filter (as you said it can contain a property) is still valid for that table.
    – J.D.
    Sep 29, 2022 at 12:18

1 Answer 1

Reset to default
0

o my question is : would you change anything to this model ? Have I missed anything ?

Yes. You should probably not create a detailed relational model for the conditions. Instead store the expressions as snippets of code, or as the JSON representation of a data structure you manipulate in the application.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.