Solved

"lookup tables" in SQL

Posted on 2011-03-07
10
325 Views
Last Modified: 2012-05-11
Hi

I need some advice on adding a certain table into an SQL/Windows client. The table looks something like this:

                          1        2       3       4      5       6
1<x<=2              4.0     2.0    5.0    3.4   6.7    4.7
2<x<=5              3.0     2.3    4.5    3.4   5.1    6.0

The table works in this way. Suppose the variable is y on the top of the table i.e. y = 1,2,3,4,5 or 6. If I have two values y = 3 and x = 1.3, then the table would give me the answer 5.0

My questio comes in two parts, firstly, what is the best way for me to add this type of table to an SQL database. Secondly, what would be the best way to interogate the database to get the answers for a given value of x and y?

Thanks in advance

John
0
Comment
Question by:IssacJones
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 25

Expert Comment

by:reb73
ID: 35059137
(a) I would recommend having two columns for the x attribute, min_val and max_val respectively. Ensure the values here are unique in that if the max_val is 2, the min_val for the next record is 2.01 or something slightly higher than 2

(b) You could simply pass the x attribute as a parameter and use a double where clause looking for min_val >= parameter and max_val <= the passed x attribute parameter ..

Does this make sense?
0
 
LVL 13

Accepted Solution

by:
LIONKING earned 84 total points
ID: 35059177
You can create a table with columns for Y, minX, maxX and value.
The data would look something like

Y    minX    maxX    Value
1    1       2       4
2    1       2       4
3    1       2       4
1    2       2       4
2    2       2       4
3    2       2       4

You could query this table with a condition similar to:
WHERE Y=myYValue AND myXValue BETWEEN minX AND maxX

You just have to be careful with the minX and maxX values, so they don't overlap and return only one value for every X number.

Regards,
0
 
LVL 15

Assisted Solution

by:derekkromm
derekkromm earned 83 total points
ID: 35059180
If either of the axis can be fairly long, I'd suggest the following 3 tables:

XAxis
XAxisID int identity
XAxisMin numeric
XAxisMax numeric

YAxis
YAxisID int identity
YAxisMin numeric
YAxisMAx numeric
(or, if they will be distinct integer values, get rid of the min/max and just use YAxisValue int)

AxisIntersect
AxisIntersectID int identity
XAxisID int
YAxisID int
IntersectValue numeric

Then, if they pass in an X and Y, you can do this:

select IntersectValue from
AxisIntersect ai
inner join XAxis x on ai.XAxisID = x.XAxisID
inner join YXaxis y on ai.YAxisID = y.YAxisID
where @x between x.XAxisMin and x.XAxisMax
and @y = y.YAxisVal
0
 

Author Comment

by:IssacJones
ID: 35059184
Hi reb73

Yes, that makes sense to me. I'd have three columns in the table relating to x and y i.e. x_min, x_max and a column for the y values.

Could you give some example SQL code which I could use to interrogate the database?

John
0
 
LVL 15

Expert Comment

by:derekkromm
ID: 35059190
The 1st 2 solutions provide an easy method if there are a small amount of columns. However, if the # of X-axis values or Y-axis values can grow/vary greatly, I think my solution is a lot more robust.
0
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 

Author Comment

by:IssacJones
ID: 35059222
Hi derrekromm

Your suggestion looks promising and probably more generic. However, I'm only learning SQL so I don't follow everything. In particular, I'm not entirely sure how the intersect of the two tables works. I'm also not too familiar with the "inner join" part of the code.

It looks very interesting though! I'll have to get some reading done I think!

John
0
 
LVL 15

Expert Comment

by:derekkromm
ID: 35059262
the inner join does what it sounds like, it joins the 2 tables together on a column (or group of columns)

in this case, it connects the Intersect table with the X-axis table and the Y-axis table based on the ID values

your data would look like this

X-axis:
ID Min Max
1 1 2
2 2.01 5
...

Y-axis:
ID Val
1 1
2 2
3 3
4 4
5 5

Intersect:
ID XaxisID YaxisID Val
1 1 1 4
2 1 2 2
3 1 3 5
4 1 4 3.4
5 1 5 6.7
6 1 6 4.7
7 2 1 3
...

the "ID" fields are simply internal identifiers that are easier for the database system to query/join on
0
 
LVL 25

Assisted Solution

by:reb73
reb73 earned 83 total points
ID: 35059290
If you go for the simple one table approach, you will probably need 4 columns, the extra one for the result/answer with the query being somewhat like this -

select xyresult  from yourtable
where @x between x_min and x_max
and @y = y_val

You will need x_min, x_max and y_val to be the composite primary key in this case..

But derekkromm has posted a normalized structure which would be more efficient and scalable although there is an extra overhead - populating x, y and answer values in 3 different tables.
0
 

Author Comment

by:IssacJones
ID: 35059537
Thanks guys - excellent information from you both!

I hope you don't mind if I share the points equally between you both. You have both helped me enourmously.

Many thanks

John
0
 

Author Comment

by:IssacJones
ID: 35059606
sorry, "both" should have been "all". put it down to a long day!
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now