First of all, i hope that this kind of question is accepted meaning by that a reflexion about a problem (that is pretty complex) and a possible solution (even simple ideas are welcomed).
So let's take a ride to the brainstorming world !
Let's suppose i have a table "product" and in it i have two fields called "prd_id" and "prd_num". - "prd_id" is the primary key and is incremented with an Oracle sequence (mySeq.nextval). - "prd_num" is a copy of the "prd_id" and is primary used for display/logic purpose.
The problem is that the "prd_num" is becoming too complex (regarding its use) for the user.
So the idea is to create a new sequence that will increment the field "prd_num" along the incrementation of the "prd_id" (with the old sequence) BUT what's different here is that we would be able to specify a certain patte (maybe using an xml config file, etc..) based on the client using the app (the distinction beetween clients is done via an another field in the table "product".
Exemple : for the client1 we would define the patte "CLT1XXXX" with XXXX being a simple incrementation.
SO => I don't think that Oracle can handle this type of sequences (mixing letters with numbers in a certain patte) so any ideas in Java/hibeate is also welcomed.
Hope to hear some ideas !