## What should be the design implementation for below mentioned scenario?

How to design Schema for the following scenario salesforce.
Hope this post makes some visibility to the right audience here.

Note salesforce dose not allow db level calculation of values.
Like : Community - Re: SOQL - Calculating values - Force.com Discussion Boards

This Question is also posted in the below link

Community - Schema development issue - Force.com Discussion Boards

Is There a better Solution ?.

Custom Object : Math
Custom Field : N1
Custom Field : N2

Calculated fields/Formula fields : ((n3*n2__c)+n1__c) Where n3 is a range of value 10 to 500 in multiples of 10.

Sol 1 :
Math
n1 n2 FF10 FF20 FF30 FF40 FF50 … FF500
2500 10 2600 2700 2800 2900 3000 … 7500
3520 10 3620 3720 3820 3920 4020 … 8520
4510 50 5010 5510 6010 6510 7010 … 29510
2562 45 3012 3462 3912 4362 4812 … 25062

Problems :
1. Increases the count of fields 50 // downside is if we forsee range in multiples of one. we would have 500 fields.
2. n3 is a range entered by the "end user" So system should be able to query shown below

Scenario 1 :
End user searches for n3 = 30
Search_Value < 3800 // This could be any value

[Select n1__c, n2__c WHERE FF30 =earch_Value] ; // Note FF30 is Dynamically Placed as n3 = 30

Scenario 2 :
End user searches for n3 = 40
Search_Value < 5000 // This could be any value
[Select n1__c, n2__c WHERE FF40 =earch_Value] ; // Note FF30 is Dynamically Placed as n3 = 40

Search field should be dynamically changed based on the users input which is not a good practice too.

Sol 2 :
Create a child object for math Say math_subset__c

Math Math Subset
Record 1 n1 n2 n3 value
2500 10 10 2600
20 2700
30 2800
40 2900
50 3000
60 3100
… ######
500 7500

Math Math Subset
Record 2 n1 n2 n3 value
3650 85 10 4500
20 5350
30 6200
40 7050
50 7900
60 8750
… ######
500 46150

Problems :
1. This will increase 50 Child records for each Math Record. if we foresee this n3 in multiples of 1 then 500 records.
Imagine we have 2000 Math records this will increase the record count of subset 2000*500 ie. 2,500,000 records which is not a feasible solution seen.

2. Will reduce the performance of search.

Sol 3 : Hackers Solution
create a string field which holds those precalculated values as shown below.

Math
n1 n2 String Field
2500 10 2600-2700-2800-2900
3650 85 4500-5350-6200-7050

Search as :
[Select n1__c, n2__c WHERE String_Field__C LIKE '%Search_Value%'] ;

It has its own code complexity.