Hi guys....

Guess you all are thrilled to know what a "non-unique" identifier could be :o)

Well the point is this:

In my DB i need implement a history function, e.g. i got to log every change in a single "entity" with several entrys...

I have a birth-date and a death-date for every entry... and a "recnum" that is unique FOR EVERY ENTITY in the DB, but not for every entry. I need this to connect several entrys to a single time history of a single entity....

e.g.

-------------------------------------------------------------------------------
ID | recnum | Actual Points | birth-date | death-date
1 | 1 | 1000 |10-09-2000 | 11-09-2000
2 | 1 | 1200 |11-09-2000 | null
3 | 2 | 700 |08-06-2000 | null


the above example shows two players... one received 1000 points on 10-09-2000 and another 200 points on 11-09-2000 so 1200 is his point total till the present day...
The other one only received 700 on 08-06-2000 .....

The problem appears when i try to insert a new entity....

I have to find a way to assign a recnum that must be unique in the table...
I cant declare the whole column unique, cause it simple isnt ;o)

So right now i found 4 ways to get this with absolutely no clue which one is the smartest/fastest...

1.) Four-Step... save the entry without a recnum entry - load it - set the recnum = id - save it again....
This way its garanteed that the recnum doesnt exist already, cause id is always unique...
This seems ugly to me, but would be most easy to implement...

2.) Before Saving i make a "Select max recnum from table...." and set the new recnum to that value+1....
This approach seems best to me yet, but it also requires an additional select just to make an insert :o(

3.) I could make another table with JUST an Integer auto-incrementing primary key.... when i want to insert a new entity i make a new "insert" into it and store a foreign key to it as recnum in the other table ....

ugh....

4.) I could let java handle it and make a application-wide "RecnumFactory" as a singleton that gets the maximum recnum of the db at application startup and distributes unique recnums at runtime......


So i ask everyone: Is there a better way and/or which of the approaches above is the smartest/fastest...


Welcome to the discussion :o)

J

you can reuse some aspects of this excellent patttern by Christian
http://blog.hibernate.org/cgi-bin/blosx ... ggers.html