I am designing a table schema for a solution that will track "punishments" issued by multiple clients. Each client represents a group of people of various sizes. We will be using PostgreSQL and according to our current numbers I do not expect this table to exceed 500k-1mil rows (of course, it would be nice to be able to scale this up if we suddenly take off and get a lot more clients).
Normally, I would just store the punishments with an auto generated sequence as the primary key. However, we would like to share the punishment IDs with the clients. In the ideal case, each client ID has its own sequential punishment ID, so that when they open the dashboard they are greeted with a monotonically increasing sequence of punishment IDs corresponding to punishments that they issued. If I were to use an auto incremented sequence for every row, it could be that a client sees IDs e.g. 1, 4, 8, 12, 13, 32. However, this is not human friendly to the client and our clients would prefer that, if we use a number as an ID, this would increase and not make it appear like there are "gap".
For further context, this is how a client would interact with punishments:
- They can query all punishments they have issued.
- For a specific punishment ID, they can query all the details, some of which are omitted in the general query above.
- Given a specific person, they must be able to see all punishments issued to that person (within the client's group, not globally).
- They may be able to issue a new punishment, which would ideally generate a coherent punishment ID safely (i.e. no race conditions). Additionally, the client-friendly ID must be returned at the end of the query so it can be displayed to them (think "Created punishment #53").
Example table of an ideal solution:
Here, querying for client
1's punishments would give the ones with
2 looking up
foo for punishment
1 would return
fizz. The next
punishment ID for clients
3 would be
Solution 1: Tables/Views
A very naive solution would be to make a table per client, and then to just use the correct table. However, there are thousands of clients and I feel like this may not be a particularly great solution (mostly since I have not heard of it in university nor in the field).
Another solution I had was to create views. Perhaps a view per client? I admit I'm not particularly familiar with views, are there any sort of restrictions you should put on creating views? This looks like something that could be useful. Here, I would then make the ID displayed to the client the row number when querying the particular view. A concern I have though is that when inserting a new punishment, determining the row number could be quite tricky to do efficiently, especially after reading this.
Solution 2: Auxiliary Table
I would essentially keep a table of the last known punishment IDs per client and then do something like:
start transaction upsert punishment ID for client, if fail, abort transaction insert into punishments with newly created ID, if fail, abort transaction commit transaction
Is this a good practice though?
Solution 3: Non-integer IDs
Something else I had in mind was to use non-integer IDs (think Git commit hashes). An automatic alphanumeric string would work for this. However, I would want to keep these IDs short (preferably not exceed 6 characters) and for my RDBMS to manage collisions for me (if this is possible, if not, I can also do it myself). In this case, the clients would just see some random letter/number spam and not panic because of gaps in the sequence. I know that UUIDs are a thing and I have used them before, but in this context they are not client friendly enough to be used.
What is the "best" solution here? Or even better, what are the pros/cons of each? How can I decide on the best tradeoff between me the developer&dba who wants the database to perform well and the client who wants their output to be idiot friendly?