ClosureTree
Fix compound unique index for hierarchies_table
related issue: https://github.com/ClosureTree/closure_tree/issues/410
I feel that the unique compound index of ancestor_id and descendant_id, is needed and generations is redundant.
Because there should be only one kind of generations for a set of ancestor_id and descendant_id.
The current table definition allows for the creation of multiple depth parent-child relationships.
example
items
| id | content | parent_id |
|---|---|---|
| 1 | "Parent" | NULL |
| 2 | "First Child" | 1 |
| 3 | "Second Child" | 1 |
| 4 | "First Grandchild" | 3 |
item_hierarchies
| ancestor_id | descendant_id | generations |
|---|---|---|
| 1 | 1 | 0 |
| 1 | 2 | 1 |
| 2 | 2 | 0 |
| 1 | 3 | 1 |
| 3 | 3 | 0 |
| 1 | 4 | 2 |
| 3 | 4 | 1 |
| 4 | 4 | 0 |
Before the refurbishment, this record could also be added to the above.
INSERT INTO `item_hierarchies` (`ancestor_id`, `descendant_id`, `generations`) VALUES (1, 4, 1);
However, If there is a search by ancestor_id, descendant_id and generations columns, considering performance point of view, the disappearance of the ancestor_id, descendant_id and generations composite indexes is not good.
Therefore, I think it needs to be redefined.
