How to implement Auto_Increment per User, on the same table?
- by Jonas
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 |
+----+-------------+---------+------+
