Link to home
Start Free TrialLog in
Avatar of irodov
irodov

asked on

Java-Sybase design help and advise please...

Experts -
I have a design question..
I have an application where core api is in Java and interacts with sybase at backend..

In several requirements, we need to enhance our code to include Sybase cols as for example:

Calculaiton Method can be of many types for examples:
NOE
Simple etc.
and based on the fields we get from sybase DB, we apply appropriate calculation method to our calculations..

Now there are 2 ways to achieve this req..
1). we enhance to include col in table which has int in sybase --
NOE= 1, Simple = 2,  etc and then from java -- we get 1,2 or 3 and based on this we pick the appropriate class to apply calculation method..
downside -- we need to remember that NOE =1 , Simple =2, maintain in Constants class or somewhere and always refer to that if we forget or even preparing a report say for example ..
in sybase, we need to pick all the rows which are set for NOE .. Now we will need to pick all rows where calculation method = 1 and then in excel change to NOE to send to business correct?

Good thing in this approach  is -- better performance since we are just storing int's in sybase..

other way of doing this..

in sybase -- add a col (varchar) which stores NOE, Simple  (exact strings).. and based on strings apply the calculation method --
downside - I guess it will not perform so good compare to 1..
good thing -- more descriptive, easy in case of prod suppport or report prepartion..

Similarly we have req for precisions --
currently we have only one col of precision where we store:
-1 means 1 Truncate
1 means 1 Round

But now we have to start storing --
precision value and precision type
where precision value = 1,2,3....
precision type = ROUND, TRUNCATE, ROUND_UP, ROUND_TO_NEAREST_WHOLE_NUMBER etc...

so Not sure what should be the best way to achieve this..

Please advise ideas or alternate suggestions of what is the best way of doing this..
thanks
ASKER CERTIFIED SOLUTION
Avatar of Mayank S
Mayank S
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of irodov
irodov

ASKER

well, the application is extremely perf critical, so whatever changes we make, we have to report to technical architect so ensure we are not doing anything that can impact performance

reports is once a while kind of thing..

its not generated on daily basis..

does it change your decision?
Not really because I don't see much difference in performance but maintainability of the application is more than important.
Avatar of irodov

ASKER

Ok, got it, and I guess sounds good for one requirement of calculation method.

But I am not not sure how to implement precision..
let me explain me why there is a confusion.
currently in table, we have
price precision
BP precision
comm precision
and the way it works is
+ value tells us round  -- so value of 2 would mean 2 decimal places ROUND
- value tells us truncate - so value of -2 would mean 2 decimal places TRUNCATE

and then we have one value of -9999999 means 0 decimal places TRUNCATE
while 0 means 0 ROUND

but now requirement is to implement ROUND_UP, ROUND_UP_TO_NEAREST_WHOLE_NUMBER etc...

and also to add more precisions like we have for price, BP, Commission, now there will be precisions for Charges (Sales Tax precision, Stamp precision etc.. in total 2 right now )

now there are several ways to implement
Approach 1). increase our table and drop 3 columns but add 10 columns as below
PRICE_PRECISION_VALUE | PRICE_PRECISION_TYPE
BP_PRECISION_VALUE | BP_PRECISION_TYPE
COMM_PRECISION_VALUE | COMM_PRECISION_TYPE
ST_PRECISION_VALUE | ST_PRECISION_TYPE
etc...

where PRECISION_VALUE = 1,2,3..... PRECISION_TYPE = ROUND_UP, ROUND, TRUNCATE .... etc..

Good thing -- very descriptive and easily understandtable..
Bad -- whenever more charge precision has to be added, 2 new cols has to be added .. currently table has 8 cols, after this table will have 15 cols.. and it may increase going in future.. , bad performance may result..

Approach 2).
we DROP THE OLD COLS AND add 5 new cols:

PRICE_PRECISION
BP_PRECISION
COMM...
etc.

and then we decide that for
ROUND -- VALUE starting from 1000000 where last 3 digits reflect precision
so 1000002 would mean 2 ROUND
1000003 would mean 3 ROUND
1000010 would mean 10 ROUND
etc.

TRUNCATE will be 2000000 (again last 3 digits tells precision value)

and similarly with rest..

Good thing -- compact cols, not too many cols in table, better performance.
Bad -- difficult to remember and understand. whenever more charge precision has to be added, 1 new col has to be added ..How to map if somebody wants a report is a challenge..

Approach 3) -- normalization of tables..

so we drop all the precisions cols from table
we have another table
PRECISION
which will join with above table with id and has following cols
precision_id
precision_value
precision_type

and then one more table say
PRECISION_CATALOG
which will have cols like
PRECISION_NAME (PRICE, BP, COMM, ST etc....)
precision_id

so original table (General) will have query like

General.id = PRECISION.general_id
and PRECISION_CATALOG.precision_id = PRECISION.id
etc..

Good thing -- descriptive, flexible
Bad -- more joins, so will impact performance drastically.. the query already has 5 joins, so adding 2 more joins will have huge impact on performance..

Please help me take  wise and best decision here..

since again as this is exteremely performance critical appl., so design decision on this should be taking into the consideration both the things -- performance and flexibility .. keeping these 2 things in mind and balancing.. what should be the best approach?

thanks
>> But I am not not sure how to implement precision..

I would say that you should implement the precision or formatting at the Java end while displaying the values. Have a look at this:

http://www.exampledepot.com/egs/java.text/FormatNum.html