Table design

Question

Post reply

Table design

Naren-274001

Ten Centuries

Points: 1033
More actions
March 2, 2009 at 12:46 pm

#155352

Hi,
We will have states table with fields satename,statecode
statename statecode
AlabamaAL
NewyorkNY
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

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

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

jagadishrashmi SSC-Addicted Points: 496 More actions · Answer 1

sorry post/expectation does not make any sense.

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

Glenn Dorling SSCertifiable Points: 7878 More actions · Answer 2

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

.

Naren-274001 Ten Centuries Points: 1033 More actions · Answer 3

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.

one-1016367 Old Hand Points: 393 More actions · Answer 4

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)

Naren-274001 Ten Centuries Points: 1033 More actions · Answer 5

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

Thanks,

Naru.

one-1016367 Old Hand Points: 393 More actions · Answer 6

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)

Glenn Dorling SSCertifiable Points: 7878 More actions · Answer 7

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 @Lookupint

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.

Jeff Moden SSC Guru Points: 1004405 More actions · Answer 8

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)