locked
Save filter criteria on a SQL database RRS feed

  • Question

  • Hello,

    I have a Products table as follows:

    create table dbo.Product ( 
      Id int not null
      Name nvarchar (80) not null,
      Price decimal not null
    )

    I am creating Baskets (lists of products) as follows:

    create table dbo.Baskets ( 
      Id int not null
      Name nvarchar (80) not null
    )
    
    create table dbo.BasketProducts ( 
      BasketId int not null,
      ProductId int not null,
    )

    A basket is created based on a Search Criteria using parameters:

    1. MinimumPrice;
    2. MaximumPrice;
    3. Categories (can be zero to many);
    4. MinimumWarrantyPeriod

    I need to save these parameters so later I know how the basket was created.

    In the future I will have more parameters so I see 2 options:

    1. Add MinimumPrice, MaximumPrice and MinimumWarrantyPeriod as columns to Basket table and add a BasketCategories and Categories tables to relate a Basket to Categories.

    2. Create a more flexible design using a Parameters table:

      create table dbo.BasketParameters ( BasketId int not null, ParameterTypeId int not null, Value nvarchar (400) not null )

      create table dbo.ParameterType ( Id int not null Name nvarchar (80) not null )

    Parameter types are MinimumPrice, MaximumPrice, Categories, MinimumWarrantyPeriod, etc.

    So for each Basket I have a list of BasketParameters, all different, having each on value. Later if I need for parameter types I add them to the ParameterType table ...

    The application will be responsible for using each Basket Parameters to build the Basket ... I will have, for example, a Categories table but will be decoupled from the BasketParameters.

    Does this make sense? Which approach would you use?

    Thank You,

    Miguel

    Friday, April 21, 2017 1:37 PM

Answers