none
Database design depends on month and on days as well...

    Question

  • There are few rules which i want to come out from database itself...like it should be configured according to month or according to days ....but database should support both...one at a time.

    ....Suppose i have registration date 1 jan 2012 now one rule can be like they want to provide 40% discount for first 30 days from the registration date then first 60 days after registration date they want 50% discount ....or one rule can be in jan month they want discount of 20% then in Feb 30 % ...

     


    • Edited by glimpse_s Saturday, February 04, 2012 12:59 PM
    Saturday, February 04, 2012 10:21 AM

Answers

  • I see two ways over there.

    1. Every DiscountPolicyType reflected as a separate entity:

    Dicsount_By_days_from_registration(days (PK), discount)

    Discount_By_calendar_month(month(PK), discount)

    2. Generalized table DiscountPolicy(type, paramValue,dicsount )

    plus metadata  DiscountPolicyType() about paramValue meaning depending on type.

    I choose the second because  a kind of run-time configuration was requested. which may imply adding more rules , etc.


    Serg
    Monday, February 06, 2012 4:17 PM

All replies

  • For example

    create table #DiscountPolicyType (
    id int primary key
    ,descr nvarchar(100)
    ,paramName nvarchar (100)
    )
    go
    
    insert into #DiscountPolicyType 
    values (1,'By days from registration', 'Number of days')
    	, (2 ,'By calendar month', 'Month number')
    
    create table #DiscountPolicy (
    id int primary key
    ,typeId int foreign key references #DiscountPolicyType(id)
    ,paramValue int
    ,dicsount decimal(2,2)
    )
    go
    
    insert into  #DiscountPolicy
    values (1,1,30,0.4)
         , (2,1,60,0.6)    
         , (3,2,1,0.2)
         , (4,2,2,0.3)
         , (5,2,3,0.25)
         , (6,2,4,0.15)
         , (7,2,5,0.33)
    go 
    
    create procedure DiscountOptions (@regDate datetime, @purchaseDate datetime)
    as
    begin
    select t.descr, t.paramName,  p.paramValue, p.dicsount
    from #DiscountPolicyType t
    left join 
    (
    (select top 1 p1.* from #DiscountPolicy p1 
           where p1.typeId=1 and p1.paramValue >= DATEDIFF(day,@regDate, @purchaseDate) 
           order by p1.paramValue)
    union
    select * from #DiscountPolicy p2
           where p2.typeId=2 and p2.paramValue = MONTH(@purchaseDate)
    ) p
    on p.typeId=t.id 
    end   
    go   
    
    DECLARE @RC int
    DECLARE @regDate datetime
    DECLARE @purchaseDate datetime
    
    set @regDate=GETDATE()
    set @purchaseDate= GETDATE()+4
    
    EXECUTE @RC = [dbo].[DiscountOptions] 
       @regDate
      ,@purchaseDate
    GO
    
    drop table #DiscountPolicyType 
    go
    drop table #DiscountPolicy
    go
    drop  procedure DiscountOptions 
    go 
    
    


    Serg
    Saturday, February 04, 2012 11:46 AM
  • Serg, that is a possible implementation. Though the code is right there, i found it confusing to follow. Please explain what the approach is.
    Monday, February 06, 2012 1:54 PM
  • I see two ways over there.

    1. Every DiscountPolicyType reflected as a separate entity:

    Dicsount_By_days_from_registration(days (PK), discount)

    Discount_By_calendar_month(month(PK), discount)

    2. Generalized table DiscountPolicy(type, paramValue,dicsount )

    plus metadata  DiscountPolicyType() about paramValue meaning depending on type.

    I choose the second because  a kind of run-time configuration was requested. which may imply adding more rules , etc.


    Serg
    Monday, February 06, 2012 4:17 PM