Re: Efficient database design for auto-incrementing, grouped, sets of data?

[Peter Brawley]

I still don't understand what problem all this bookkeeping busywork is meant to solve.

For some reason, this table stores multiple hashes per entity_id. You've not explained what that's for, and perhaps we don't need to know. But a simple query retrieves all hashes for any given entity_id value(s), and another simple query finds the entity_id corresponding to a given hash value, so, in your "workflow" list ...

> *Select all entity_id`s where the hash equals any of the existing hashes

simple query

> *Select the largest entity_id from the `hash` table

simple query

> *If no entity_id's exist from step 1, insert appropriate rows with an entity_id of max(entity_id) + 1

what purpose would that serve?

> *If one or more entity_id exists, change all rows with those entity_id's to an entity_id of max(entity_id) + 1

what requirement is this meant to meet?

> *Perform steps 3 and 4 on any other tables that reference the entity_id, writing most of the logic in PHP

Yikes. Other tables reference these hash values? Why are you editing them at all? Why aren't they just referenced by those tables as foreign keys or as simple lookup values?