I have been looking at the dual-table solutions for localising a database. I understand the approach, but have been researching alternate solutions.
The thought struck me that creating a table to support a tree structure might be an alternative solution. Adjacency List causes recursion and would be a slow solution, but it’s easier to use the concept to explain my thoughts. Rather than using a “parent_id” column, call the recursion column “source_id” and use it to select the record containing the source content to translate. For Adjacency List, this could probably be handled by hacking Paulzi’s Adjacency List behavior to support a source_id column instead of a parent_id column. I would still need a language lookup table. The source_id and language_id columns would need to have a combined unique key to prevent translation duplicates.
Using Nested Sets would be a faster solution, but Adjacency List is easier to understand for most people. .
Curious to know if anyone has tried to handle translations this way and what problems they may have encountered. Any advice would be welcome.