brainstorming: Is a good UI for editing join tables possible?

[cldellow]

Imagine:

CREATE TABLE post(
  id integer primary key,
  title text
);

CREATE TABLE tag(
  id integer primary key,
  title text
);

CREATE TABLE post_tag(
  post_id integer references post(id),
  tag_id integer references tag(id),
  primary key(post_id, tag_id)
);

Or:

CREATE TABLE post(
  id integer primary key,
  title text
);

CREATE TABLE post_tag(
  post_id integer not null references post(id),
  tag text not null,
  primary key(post_id, tag)
);

Is there a way this can fit into the editing model such that the user sees a list of tags when looking at /db/post/1 ?

The currently proposal would have the user go to /db/post to create the post, go to /db/tag to create the tags, then go to /db/post_tag and create new records. It works, but is very clunky.

[cldellow]

Proposal: special-case this scenario for base tables (but not views).

Render a UI that lets you add/delete tags. It will feel a little jarring, as it will get tacked on outside of the main table with the other controls. That's probably worth it.

Why not views: because the principle of views is that you explicitly opt some columns in. But currently, views have to draw from a single table to be editable.

Maybe long term we can expand VIEW support to recognize and permit things of the shape:

SELECT id, title agg(tag)
FROM post
LEFT JOIN post_tag ON post.id = post_tag.post_id
GROUP BY 1, 2

Then we'd:

• render an appropriate multi select UI
• create appropriate INSTEAD OF triggers

Tedious, but not technically complicated. But also, no back compat challenges, so doesn't have to be part of v1