What is the Best practice for calculations involving reference tables

[OzzieOzzum]

Am trying to incorporate a Framingham risk score calculator into a solution. The calculator generates points by looking up tables. The question is whether to create custom functions to lookup values or use relationships to wade through tables.
 
For example, for the table listing cholesterol for age range, the table is represented as 

Cholesterol              Age in Years (Men)
                   20-39    40-49    50-59
   <160              0        0
  160-199            4        3
  200-239            7        5
  240-279            9        6
  280-999            11       8

The question is,  Is it better to create custom functions to calculate the point score Or use a relationships between tables to isolate the record for a value of cholesterol between say 239 & 200 and Age between 20 & 39 and then use a calculated field to get the point score?

Framingham.Men.Cholesterol(Age,cholesterol)=


If(IsNumberInRange ( Age; 39; 20 );

Case(
 IsNumberInRange(cholesterol; 160; 0)	;	0;
 IsNumberInRange(cholesterol; 199; 160)	;	4;
 IsNumberInRange(cholesterol; 239; 200)	;	7;
 IsNumberInRange(cholesterol; 279; 240)	;	9;
 IsNumberInRange(cholesterol; 999; 280)	;	11;
0);

If(IsNumberInRange ( Age; 49; 40 );

Case(
 IsNumberInRange(cholesterol; 160; 0)	;	0;
 IsNumberInRange(cholesterol; 199; 160)	;	3;
 IsNumberInRange(cholesterol; 239; 200)	;	5;
 IsNumberInRange(cholesterol; 279; 240)	;	6;
 IsNumberInRange(cholesterol; 999; 280)	;	8;
0);

If(IsNumberInRange ( Age; 59; 50 );

Case(
 IsNumberInRange(cholesterol; 160; 0)	;	0;
 IsNumberInRange(cholesterol; 199; 160)	;	2;
 IsNumberInRange(cholesterol; 239; 200)	;	3;
 IsNumberInRange(cholesterol; 279; 240)	;	4;
 IsNumberInRange(cholesterol; 999; 280)	;	5;
0);

If(IsNumberInRange ( Age; 69; 60 );

Case(
 IsNumberInRange(cholesterol; 160; 0)	;	0;
 IsNumberInRange(cholesterol; 199; 160)	;	1;
 IsNumberInRange(cholesterol; 239; 200)	;	1;
 IsNumberInRange(cholesterol; 279; 240)	;	2;
 IsNumberInRange(cholesterol; 999; 280)	;	3;
0);

If(IsNumberInRange ( Age; 79; 70 );

Case(
 IsNumberInRange(cholesterol; 160; 0)	;	0;
 IsNumberInRange(cholesterol; 199; 160)	;	0;
 IsNumberInRange(cholesterol; 239; 200)	;	0;
 IsNumberInRange(cholesterol; 279; 240)	;	1;
 IsNumberInRange(cholesterol; 999; 280)	;	1;
0);

0))))) 

 

[Wim Decorte]

That kind of data needs to live in tables.  If you don't want to burden your graph with relationships, use ExecuteSQL to pick up the relevant data.

[OzzieOzzum]

Thanks for reminding about ExecuteSQL.