1. Hi ,

    This problem statement needs both the expertise of the EJB layer and the datastore(oracle) layer...

    Currently in our system , we generate account numbers for different categories by picking up nextVal from according sequence objects.

    But currently we have more requirementz coming in ...which will require this functionality to be highly flexible for the user..

    This is the deal ....the account numbers could be any range from 000xxxxx to 999xxxxx...and depending on the user and certain other fields he choses , he should be able to type in an account number , or ask the system to generate the account number, reserve account numbers etc...

    Now ... with Oracle seq object I cannot have this kind of flexibility..since the user has an option of picking up the number or rejecting it....and there are overlap in ranges ..I guess I will not have enough control with oracle seq objects and it wud lead to loss of numbers as once taken from dual is lost and cannot be reused...

    Now basically what I need is to store numbers from 000xxxxx to 999xxxxx in a table and depending on user conditions , pick up a valid number(and locking it with a flag till the time the user either rejects it or accepts it so that the same number is not given to another user with similar conditions)...In effect what I need is an "ACCOUNT-POOL" table with 100 million numbers ...

    Now is this a good idea ...? can I manage this kind of volume in oracle....are the queries to this table (lets assume that this will have 3-4 light-duty columns which will be properly indexed)going to be performence hogging ??
    Is there some option where I need to reach 100 Million records but they do not occupy the primary space...sort of a lazy initialization or on demand..?

    Any design approaches to this problem statement is greatly appreciated...

  2. Why not to check if the account is already in database before the user selects it, otherwise - reject.

  3. has just released a book called "EJB design pattern", which has one pattern called "UUID for EJB".
    I hope this pattern would help

  4. Appreciate ur suggestions...

    I cannot see whether account is already in the DB coz I need to create new accounts also ..
    and UUID does not work for me coz itz just not any unique number, but the number to be generated depends on business units etc...and I need to give the user the flexibility of putting in account numbers which is not possible with UUID.

  5. Hi,

    I hope you are using Java with regard to this application as the Language. You can very well create a method call to generate numbers, and another method call to associate it with the business unit.

    Now the question of generating unique numbers is the issue,you take the input from the user and check if it exists and the second option of generating unique numbers using the Random function(java.util) (As this can repeat very occasionally- 1 in a 100 thousand Recrords) and then check if this number exists(which can be the same method call for checking the user input val)

    Regarding storing the numbers in the DB , you can assgin the VARCHAR(Digits)and assign it as a Account Number.

    I hope this will be an helpful solution.
  6. Here is another suggestion.

    1) An Oracle Sequence of account numbers
    2) An account table

    A) New account – pick from the oracle sequence, put into accounts table
    B) Edit account – access accounts table only, modify a row.
    C) Reserve account – same as A, but flag an appropriate column in account table

    This is really not that different from the way any other system of this type works. The only difference is being able to reserve the accounts, as well as added flexibility you need. This, you gotta roll your own based on the framework above. I guess that is why they pay us engineers the big bucks!

    I hope this helps.