Can you elaborate a bit? Are you using an ORM, eg. Hibernate? If so have to considered other strategies like pooled-lo, IDENTITY or recursive CTEs that offer the same benefits but make the sequence values match the database values and reduce "id loss"?
I'm also interested in some elaboration of this scheme. While I'm less interested in ORMs etc, the advantages/disadvantages would be useful to compare to some recent thoughts I've had regarding the use of databases.
Professionally I have little use for RDBMSs, however recently I've been working on some side projects with a focus on flexibility and modularity that have changed my perspective on how databases should be used. Formerly, I (and I assume many newcomers) had a database driven approach where the database structure came first, and code grew around that. This reduces flexibility because your data is all coupled together, which will force refactoring of data when your storage configuration changes. Data refactoring sounds like a thing of nightmares, but ability to easily migrate data between a RDBMS-backed module to a redis-backed module with minimal fuss is the kind of flexibility I'd like to have.
The alternative that I've settled on is to begin development with the notion of a generic 'data store' and later fitting a database around that. Though I am yet to see the scheme come to reality, I believe this will reduce data coupling, increase testability and debuggability for both code AND data associated with a given module, and increase migratability of the data. This has also pushed me toward the exclusive use of integers for IDs as they can be considered universally supported with minimal effort, which has me curious about further benefits of different ID generation schemes.
Anything with atomic increments can do this, we use a simple table called idsequence with a single column and row. When an application starts up, it upserts 100000 and gets the new value. This gives both the min and max values that the app can then use as ids for any entities with no overlap with anything else.
It's actually not Hi/Lo but similar. You can also add another column to that table if you want multiple "sequences" but we like to keep it simple and with bigints, you'll never realistically run out.
If you're using an ORM, you might need some extra work but most of the time they are smart enough to use an existing ID if the object already has one rather than try to get one from the database.
>> This means plenty of ID space, maintains rough numeric ordering, allows ID creation without a roundtrip for every insert, is easily portable across different databases, and produces unique IDs for every row in the database which greatly simplifies everything from caching to replication.
Can you elaborate a bit? Are you using an ORM, eg. Hibernate? If so have to considered other strategies like pooled-lo, IDENTITY or recursive CTEs that offer the same benefits but make the sequence values match the database values and reduce "id loss"?