My design contains a table where almost every entry has a parent it has to point to, within the same table (differently put, self references).
/* scenario executions */
CREATE TABLE `boteval`.`scenario_executions` (
`scenario_id` BIGINT NOT NULL COMMENT 'connects to a scenario id',
`id` BIGINT NOT NULL AUTO_INCREMENT COMMENT 'execution id',
`parent_id` BIGINT NULL COMMENT 'execution id of parent executor (null would mean no parent, i.e. a top level execution)',
`started` DATETIME(6) NOT NULL,
`ended` DATETIME(6) NULL COMMENT 'can be null while the execution has not yet ended',
PRIMARY KEY (`id`),
UNIQUE INDEX `id_UNIQUE` (`id` DESC));
The use case being one where tree structures need to be incrementally recorded into the table, first the parent, than children, children of children, etc, as the program unfolds.
Using an auto-incrementing key for this table, I have reason to believe it would be extruciatingly slow if every insert needed to happen only after its predecessor had already returned its database-assigned auto incremented key.
On the other hand, I think that batching this workload would be way beyond naive, as a sql that strings together the returned ids would become extremely nested for each buffered batch, not to mention the dependencies between batches.
I have several solutions to this, but I wonder if there are very specific design patterns you would recommend. Maybe one of my design constraints would need to be relaxed: maybe the auto-incrementing, if I can safely supply a unique key from my code. In a way, this might not even really be a good fit for a relational database like MySQL.
Thanks in advance for any comments on good patterns for this kind of scenario!