NHibernate and hilo generator: how to design key-tables?

Question

I'm about to switch some of my entities from identity to hilo id-generator.

I'm don't have a clue how the tables holding the next-high values should be designed.

• should I use a single table for all entities, or for a group of related entities?
• should I use another table for each entity?
• should I use a single table with another row or column per entity?

Are there any best practices? What needs to be considered? Are there any pros or cons for any of the approaches?

Answer 1

I don't think there's a single "best" practice, but it's important to understand how HiLo works:

• The table hilo generator is designed for databases which do not support sequences, which are a more natural fit for this task.
• The Hi value for a class is retrieved and updated the first time you save an instance of that class, or when you run out of Lo values.
• A different Hi value is used for each class.

So, you'll have to consider at least two variables:

• Contention. You might improve performance a little bit by using separate tables
• Range. With a very big database, you have a bigger risk of eventually running out of Hi values.

After considering that, it's just a matter of taste.