Post reply

Table design

  • March 2, 2009 at 12:46 pm

    #155352

    Hi,

    We will have states table with fields satename,statecode

    statename statecode

    Alabama AL

    Newyork NY

    Based on the input value we need to calculate the grade w.r.t statename i.e

    If Alabama

    If input value is between 0-50 - .5% of input value

    between 50-100 - .10% of input value

    between 100 - 150 - .15% of input value

    so on... upto 1000

    For NewYork

    If input value is between 0-50 - .2% of input value

    between 50-100 - .4% of input value

    between 100 - 150 - .9% of input value

    so on... upto 1000

    There will be 54 rows in states table and each table will have different method of calculation based on the Input value.

    Can you please help with designing the table.

    Thanks,

    Naren

  • March 2, 2009 at 8:26 pm

    #952203

    sorry post/expectation does not make any sense.

    ---- [font="Tahoma"]Live and Let Live![/font] ----

  • March 2, 2009 at 8:37 pm

    #952205

    If the "brackets (ie. 0-50, 50-100, etc.) are always the same for the different states then these could be added to the state table with a different multiplier for each state, eg.

    State Code Bracket1 Bracket2 ...

    Alabama AL 0.2 0.5

    .

    .

    .

    If the brackets differ as well then you'd be best with a foreign-key relationship to a separate table, eg.

    Code BracketLower BracketUpper Multiplier

    AL 0 50 0.1

    TX 0 20 0.05

    .

    .

    .

  • March 3, 2009 at 11:35 am

    #952625

    Thanks for your reply.

    Let us think that brackets 0-50, 50-100 are not going to change. Your suggested design is...

    StateName StateCode Bracket50 Bracket100.... Bracket700

    Alabama AL 0.2 0.5 10

    In the above design, based on the the input value we need to hardcode. i.e we need to write IF conditions - -

    if inputvalue is between 0-50 then select bracket50 ELSE if input value is between 50-100 then select bracket100....

    I personally don't prefer hardcoding. I have another type of design in my mind. Please make sure whether the below design is acceptable or not...

    StateName StateCode BracketRangeFrom BracketRangeTO Percentage

    Alabama AL 0 50 0.2

    Alabama AL 50 100 0.5

    ......

    Alabama AL 650 700 10%

    Newyork NY 0 50 0.4

    .......

    Newyork NY 650 700 20%

    Please let me know the best design among the two OR it would be great if you can suggest other type of design.

    Thanks,

    Naru.

  • March 3, 2009 at 12:37 pm

    #952667

    A lookup table like you are suggesting with one row per state per bracket range makes sense.

    Thorbjorn Kvam,
    Project manager and DBA (design) at Payex (http://www.payex.com)

  • March 3, 2009 at 12:57 pm

    #952672

    Note: It is many rows per state and one row per bracket.

    Thanks,

    Naru.

  • March 3, 2009 at 1:05 pm

    #952676

    Narendra (3/3/2009)

    Note: It is many rows per state and one row per bracket.

    Thanks,

    Naru.

    Depends on how you see it I guess, but

    Alabama AL 0 50 0.2

    Newyork NY 0 50 0.4

    What I'm trying to say is that you need one row per combination of state and bracket. I'm sure we agree, I'm probably just expressing myself a bit weird. 🙂

    Thorbjorn Kvam,
    Project manager and DBA (design) at Payex (http://www.payex.com)

  • March 3, 2009 at 3:31 pm

    #952757

    I was intending that the select become a case statement, as per the following code for the single-table (fixed bracket boundaries) design:

    [font="Courier New"]create table #LookupTab (ID int, Bracket1 float, Bracket2 float, Bracket3 float)

    go

    insert into #LookupTab values (1, 0.2, 0.5, 1)

    insert into #LookupTab values (2, 0.1, 0.25, 0.4)

    go

    declare @Lookup int

    set @Lookup = 15

    select ID,

    case

    when @Lookup < 20 then Bracket1

    when @Lookup between 20 and 50 then Bracket2

    else Bracket3

    end

    from #LookupTab

    set @Lookup = 25

    select ID,

    case

    when @Lookup < 20 then Bracket1

    when @Lookup between 20 and 50 then Bracket2

    else Bracket3

    end

    from #LookupTab

    set @Lookup = 55

    select ID,

    case

    when @Lookup < 20 then Bracket1

    when @Lookup between 20 and 50 then Bracket2

    else Bracket3

    end

    from #LookupTab[/font]

    That should be readily adaptable to the multi-table situation too.

  • March 6, 2009 at 10:43 pm

    #955410

    So, Narendra... are you all set?

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply