The composite might be worth looking into. Thanks for the link, I'll take a look maybe I can use some concepts.
Tree structures do not lend themselves well to relational databases.
I'm not sure if we are dealing with a real tree here. But I do understand now how it's all very awkward to fit my objects into the tables of the normalized db. Sometimes I long back to my nice little spreadsheets which would be so easy to use
And can you imagine that the save() is only one method? What about update? Then I have to repeat the whole mess of logic again.
And with update it gets even more complicated. Because at first, you have user A who saves climb A (route X in Area Y and Country Z). So the climb is saved to the climb table, the route X is saved to the routes table, the Area Y to the areas table and country Z to the countries table.
But now user B comes by, adds a climb, and coincidentally for the exact same route! No problem, I have build in checks for that in the save methods. So after saving the climb for this user, we end up with 2 rows in the climbs table (one for the climb of user A, one for the climb of user B), and 1 row in the routes table, 1 in areas and 1 in countries. All happy.
But now user A comes back and wants to update his climb. He made a mistake and wants to change the name of the route. Now I have to make sure that the original route does stay in the table, and a new one is added, because the climb of the second user must stay intact.
And these are just a few issues, there's probably some more situations I haven't thought about yet. Arghh... never would have thought this would be so complicated...