Discussion: How to structure linked columns?

[timwis]

Originally from #1:

We want users to be able to associate a record in one table with a record in another table. Fieldbook takes the approach of always making this relationship many-to-many. My assumption is because that relationship is easiest for less technical users to understand: records are simply "associated" with one another, so why shouldn't a post have more than one user associated with it?

If many-to-many meets the most use cases then I think we should build for that. The question is whether there's a use case that many-to-many can't accommodate?

As far as how to accomplish this, one way (perhaps the only) is to have a joins table with left and right fields containing the record ids. Although what if you want your posts table to have an author field and an editor field, both linking to the users table? We'd need to associate the joins table records with a particular relationship, requiring a relationships table. Too complex?

On the other hand, we want dataface to resemble typical postgres databases, so supporting a basic foreign key constraint would be ideal from that perspective.

Either way, we'll want to know which column from the foreign table to use as the "display field" (instead of the primary key, which is likely an auto-incrementing number). We can store this in the column COMMENT.