You're imagining files belong to comments, comments belong to items, items belong to lists, lists belong to projects, and changing the list's foreign key re-parents the whole hierarchy.

Now consider that Basecamp allows comments on messages, todo items, and milestones. What does your schema look like now? Add a feature to show a user all recent comments across all of his projects. What does your query look like? How does it perform? Get all of this (not to mention the other features) working at scale.

Maybe you can build something like Basecamp that works at Basecamp's scale without resorting to denormalization, sharding, and/or partitioning. But I doubt it.

I don't see how this is a problem. The todolist messages still point to a todolist instead of at a project. Can you elaborate?

Looking at just comments on items in todo lists: comment's have an item_id, items have a list_id, and lists have a project_id. You want to get all comments across all projects a user is a member of, so you end with a small handful of joins. Remember that these are reasonable large tables. When you consider the fact that comments can appear on nearly anything, you end up with more joins. Are you certain this will stand up under Basecamp's load even before you consider all of the other features you need? This is all before we run into sharding or partitioning as a scaling strategy.

No, but what is the fast schema that you have in mind that makes it hard to move messages? Perhaps fast and easily movable messages are not exclusive.

I missed this reply, but in case you come back to see it: the common approach to this issue is denormalization. That is, instead of needing to join comments to todo items to todo lists to get the project_id, you duplicate the project_id on comments and you can just get all the relevant comments with very few joins. Obviously, this makes moves need to hit the comments table.

That's a small piece of the picture. With partitioning, a move could entail removing data from one table and inserting it into another. With sharding, a move could mean removing the data from one database and inserting it into another, which probably means manually ensuring consistency because your typical transaction won't span databases.