Sequence ID generation
I need to generate customer ID's for two different groups in my application and the format they need is as follows
011026040001
First two digits represent group (Shipping, Inventory etc)
next six digits, date, month, year(04)
Critical part is the last four digits is the sequence number (# number of customer's registered) ON a given DAY.
Starting everyday, this last four digits should reinitialized 0001.
I could achieve this on application using static variables to hold counter and reinitialize them using Timer class etc...got my point...This method has some issues, like when you restart server all the static classes are recreated and counter value is lost..
But I want to know if there another way to do this...please provide me your ideas
Thanks.
Rahul
Personally, I think it's a bad idea to put business logic like this into a customer ID (sounds like a primary key). I can see that your app will now have to parse this ID to get info out of it. I think it's breaking first normal form rules to pack what should be three separate table columns into a single value like this.
What if your business rules change? You're going to have to regenerate all your primary keys.
I'd make that customer ID a surrogate key and put those three items as columns in the customer table.
JMO. Sorry that I'm not more helpful with your question.
%
> This method has some
> issues, like when you restart server all the static
> classes are recreated and counter value is lost..
>
Whenever your program start, do not put initial ID=0001. Instead, you need a getInitialID() method
which can query your database to see what is the last ID used for the day.
if ( lastID found)
ID = lastID +1;
else
ID = 0001;
> > I'd make that customer ID a surrogate key and put> those three items as columns in the customer table.And you could use a trigger to generate them as well.
> > This method has some
> > issues, like when you restart server all the static
> > classes are recreated and counter value is lost..
> >
>
> Whenever your program start, do not put initial
> ID=0001. Instead, you need a getInitialID() method
> which can query your database to see what is the last
> ID used for the day.
>
> if ( lastID found)
> ID = lastID +1;
> else
> ID = 0001;
>
Of course that presumes that there is only one application ever running at once.
If more than one runs then that is going to be a problem.
Jschell, you are right. If I have the logic for id generation in application, no more than one application should be running at given instance, otherwise duplicate id's will be generated.
Is there any way to do this something like a trigger + Sequence, sequence generates sequential id's and trigger initializes the sequence start value to 0 everyday.
> Jschell, you are right. If I have the logic for id
> generation in application, no more than one
> application should be running at given instance,
> otherwise duplicate id's will be generated.
>
> Is there any way to do this something like a trigger +
> Sequence, sequence generates sequential id's and
> trigger initializes the sequence start value to 0
> everyday.
If your database supports triggers and depending on the functionality of the triggers. Oracle can certainly do it. MS SQL Server probably can. MySQL can't because it doesn't do triggers at all.
Jschell can you help me with the trigger + sequence idea.
All I need is a sequence number and it should reinitialize to zero every 24hrs, as you said it is not good to do this in application logic, so I thought may be trigger and a sequence or some other database object can do this.
Thanks,
Rahul
One way.
1. Seperate table which will have a single row for each group. Three fields: group, seq num, timestamp.
2. Proc that retrieves the seq num from the table. It checks the current time. If the date has changed then zero the id. Otherwise increment it. You should also consider what happens if the id wraps in a single day (perhaps throw a database error.) The proc takes the group as the input. The timestamp lets it figure out whether the date has changed or not.
3. Your id has three parts: group, date, seq num. Create a proc that takes the the group as input. It uses the above proc to get an id. It gets the date. Then it formats the customer id and returns it.
4. You create a trigger that uses the above. Or you simply call the above from java. Regardless you should use a transaction because you probably don't want the seq num to update unless everything is successful.
