Exactly. ;-)

Stored procedures are usually the best option because they are 'closer'
to the database and can usually be optimised. If you were going to use
CMP beans you would still need to generate or lookup the key before you
inserted your rows - if I remember correctly it isn't done for you.

Your application could generate the ids, either a combination of
machine name, location, timestamp etc, combinations of name, surname,
idno, date of birth - thought these are not always possible or
appropriate.

For the application I currently work on I set up a "sequence" table,
which is just a table with a varchar(10) and an int column, because the
database we use doesnt support sequences itself.

I then wrote a Sequencer class with one method getNext(String
sequenceName) that returns an int value representing the next value
(The method does a query and an update on the row without the rest of
my application knowing the logic - I'll replace it if we change
database or I find a better solution).

I then use this id for numerous inserts into 2 different tables. I't
may not be pretty but it does work.

That's part of the puzzle, but you also need to request that generated keys be returned
by using extended forms of executeXXX() in java.sql.Statement or prepareStatement() in
java.sql.Connection.

A question of form here: Do you need to seperate this information out?
As it all pertains to a user record surely then it should be in the
'user' table?

We currently have at least 5 tables which pertain to a user object,
which to be fair is just confusing. The tables were created to match
the user object which is split into a similar number of parts. This was
clearly a good idea when we designed our system but we're not realising
that this idea is not so great. Our next major release will involve
migrating all user "profile" data into one table.