People.Friends is actually a good example where it would make sense to have editable reverse-lookup in the single column of the same table, yeah, but only from the “ease of use” perspective.
What would be normally done in traditional databases is a separate table of Friendships with columns Person A, Person B, and any extra column that one would potentially need, e.g. the date when the friendship started or some other friendship-level metadata. Then People.Friends would be a non-editable lookup formula from that other table.
Yeah, you wouldn’t be able to add a row through “add a row” button, or directly edit values in those cells. But I’m advocating against that anyway, and suggest using buttons and helper tables instead (see Part 3 here from ~40:00, and sorry for broken audio). This approach gives much greater flexibility that you’ll most likely need down the road. Besides, the more friendships you add, the slower the formula will get, so you’d most likely want to just stamp values instead (i.e. use an input column instead of a formula column) and you’ll need a single trusted place where on-demand recalculation happens (again, a helper table that updates only relevant people whenever any friendship changes, as a part of the “save friendship” button action perhaps.)
Every time you present a different way to do the same thing, you introduce one more risk to mess up the data.
- You add an “Estimate pricing” task to Project A, but turns out it’s already a task in Project B. You weren’t looking at the Tasks table and couldn’t tell that it was the wrong one — all you saw was a display column value (most likely task name). You just wrongly moved the task or made a single task assigned to both projects.
- On Employees table, you select one more Task to assign to them. Do you know immediately what happened? Was the task unassigned from any other employee and reassigned to this one? Or is it now assigned to both? You wouldn’t know for sure without checking on the Task row; you’d have to examine it anyway. You could just change the assignee from there after all.
You personally would bother to double-check what have changed. Many people in your team wouldn’t.
P.S. Why do you think I’m building all these workarounds and button-based systems for real clients out there and have this consulting practice even though “everyone can be a maker”? Because that’s exactly what I have to deal with: making docs that are as human error safe as possible, as intuitive and bulletproof to use as possible, by making all these design decisions.