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?

Devilment

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.

Judiejudith

So you would consider to use separate tables? Contention: do you mean concurrent transactions? How should a single table affect performance? By locks on it? On the other hand, when using many tables there are more selects and updates, one for each affected table. A mix could be useful: a table for each group of related tables. – Devilment

Yes, I'd use separate tables, because of the locks. The amount of selects/updates is the same, because generator values are not shared among classes, even if you use the same table. Again, keep the key concept in mind: this is just using a table as a sequence generator. – Judiejudith

Why is the amount of selects / updates the same? When I have a single table, why should it select and update the same table for each class it needs ids for? – Devilment

Because they are different TableHiLoGenerator instances which just happen to be using the same table name. They could use different fields, different max_lo values, etc. But you're free to create your own HiLo implementation that uses a single table/sequence for all clases... – Judiejudith