In a PostgreSQL (9.3) database, I have a one-to-many relationship between Accounts and Customers, so within the Customers table, I have a globally-unique id for the primary key of each Customer, plus the corresponding Account id. But I would also like to assign each one an Account-specific id - just a number starting at 1 for each Account's first Customer and incrementing for each additional Customer - simply as an easy way for users in an Account to reference a Customer in the UI.
I can think of two possible solutions:
Use the
max()
aggregate function to obtain the last Customer id used for the Account and increment it- Pros
- Relatively simple (conceptually, at least)
- Cons
- Can result in an id being reused if the newest Customer is deleted
- Need to handle concurrent inserts calculating the same id for the new records
- Pros
Use a
sequence
- Pros
- Specifically designed to handle incrementing the value
- Remembers the last-used value, even if the corresponding record is deleted
- Cons
- Requires a separate
sequence
to be created for each new Account somehow (and then be deleted if/when the Account is deleted), otherwise each Account would end up with non-contiguous values in its Customer id sequence
- Requires a separate
- Pros
UPDATE
To clarify the last point, some gaps in the sequence of ids are always going to occur; as soon as a Customer is deleted, for example, but those are to be expected. Aside from that though, a user adding a couple of new Customers would generally expect them to have consecutive ids even if they were added days apart.
OTOH, using a single sequence
for all Customers could almost certainly see regular, unexplained gaps introduced by the ids assigned to the Customers created for other Accounts in the meantime.
I'm not a Postgres expert, so I'm concerned about how robustly I could implement either of these, especially as I've struggled to find any examples to follow.
Is there a generally accepted way of doing this?