Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Database design depends on month and on days as well...

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

  • Saturday, February 04, 2012 10:21 AM
     
     
    Sign In to Vote
    0

    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
    •  
     

All Replies

  • Saturday, February 04, 2012 11:46 AM
     
      Has Code
    Sign In to Vote
    2

    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
     
  • Monday, February 06, 2012 1:54 PM
    Answerer
     
     
    Sign In to Vote
    1
    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 4:17 PM
     
     Answered
    Sign In to Vote
    1

    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