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

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_NUM

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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Not really because I don't see much difference in performance but maintainability of the application is more than important.

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..

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_

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.precisio

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

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

ASKER

reports is once a while kind of thing..

its not generated on daily basis..

does it change your decision?