How to implement Auto_Increment per User, on the same table?

Question

I would like to have multiple users that share the same tables in the database, but have one auto_increment value per user. I will use an embedded database, JavaDB and as what I know it doesn't support this functionality. How can I implement it?

Should I implement a trigger on inserts that lookup the users last inserted row, and then add one, or are there any better alternative? Or is it better to implement this in the application code?

Or is this just a bad idea? I think this is easier to maintain than creating new tables for every user.

Example:

table
+----+-------------+---------+------+
| ID | ID_PER_USER | USER_ID | DATA |
+----+-------------+---------+------+
|  1 |           1 |       2 | 3454 |
|  2 |           2 |       2 | 6567 |
|  3 |           1 |       3 | 6788 |
|  4 |           3 |       2 | 1133 |
|  5 |           4 |       2 | 4534 |
|  6 |           2 |       3 | 4366 |
|  7 |           3 |       3 | 7887 |
+----+-------------+---------+------+

SELECT * FROM table WHERE USER_ID = 3
+----+-------------+---------+------+
| ID | ID_PER_USER | USER_ID | DATA |
+----+-------------+---------+------+
|  3 |           1 |       3 | 6788 |
|  6 |           2 |       3 | 4366 |
|  7 |           3 |       3 | 7887 |
+----+-------------+---------+------+

SELECT * FROM table WHERE USER_ID = 2
+----+-------------+---------+------+
| ID | ID_PER_USER | USER_ID | DATA |
+----+-------------+---------+------+
|  1 |           1 |       2 | 3454 |
|  2 |           2 |       2 | 6567 |
|  4 |           3 |       2 | 1133 |
|  5 |           4 |       2 | 4534 |
+----+-------------+---------+------+

Answer 1

If you can guarantee that there will only be one session per user, then it would be pretty safe to do. If a user can have more than one session then whether you do this in a trigger or in the application code you will need to take an exclusive table lock to make sure that the session you are in is the only one to get that next number.

But don't go for a table per user. That would make your sql really ugly and prevent any sort of sql plan sharing.

You may be better served by using a timestamp instead of a serial number.