While working on a search/filter page for a .NET application, I have a requirement to be able to save/share the user's filter. Currently they can search on about 30 different fields across numerous tables at the moment, however everybody knows requirements can change and often do, I need to keep the solution flexible. My thoughts on this is to set up 2 tables.

First table would be something like the filter ID, Name, Owner's ID, Parent FilterID(to determine if filter is saved with someone else), and maybe a couple of date/time fields to determine when it was created/modified/shared.

Second table would be consist of an ID, Table1.FilterID, table.field being searched on, value.

The only issue I see with this approach is in the 2nd table the value column datatype would have to be a varchar/nvarchar to accept a wide range of possibilities. Since the actual tables/columns the users will be searching on can be anything INTs, DATETIME, VARCHAR, BITs, etc, I would need to do cast/convert in the stored procedure when doing the actual filters.

I know I'm not first to do have saved filter criteria from a .NET application, so I'm looking to see how others would accomplish a similiar task.

Submitted under: Microsoft .NET · Microsoft SQL Server · DBA · DATABASE DESIGN ·