Como implementar auto increment com escopo?

Back-end
1

Considerando o seguinte requisito: um usuário pode ter muitos pedidos, e cada pedido possui um número incremental com o escopo de cada usuário, exemplo:

order_number    user_id
1               1
2               1
1               2

De que forma isto poderia ser implementado? Especificamente em JPA? A solução que encontrei é apenas no lado do banco, pois não gostaria de criar entities e repositories para contadores, já que são detalhes de implementação.

A solução que escolhi no momento é esta: possuo uma tabela order_sequence que referencia user e possui uma coluna counter , com isto tenha uma function que insere/atualiza order_sequence e retorna o próximo número do pedido, no final uso um trigger para quando order for inserido no banco, gerar o order.number .

Outra solução que encontrei seria usar algo como SELECT MAX(order_number)... , mas isto iria repetir os números de pedidos deletados.

PS: order_number não é uma primary key.

2

Cara, isso pode dar errado em muitos níveis, principalmente concorrência.
Tirando os problemas de concorrência, a solução de ter essa tabela controlando o último salvo é interessante.
Dependendo do banco de dados você pode criar triggers para fazer o update do registro com base no último registro adicionado, aí você precisa de um campo com a data de criação.
Também daria para fazer essa consulta com JPA antes de inserir o registro.

Esse é um problema legal para se resolver quando está estudando. Se for para colocar em produção, você terá muitos problemas.

Eu não sou especialista em banco de dados, pode ser que tenha outro modo.