Improve Foreign Key Input UX with Descriptive Dropdowns

[vedoa]

Currently, when inputting data into a table with foreign key constraints, users must manually enter the foreign key ID. This can be unintuitive and error-prone, especially when the referenced table contains many rows or when the ID values are not easily memorable. I am here specifically targeting non automated workflows - but rather the quick manual input of data trough the workbench UI.

It would greatly enhance the user experience if the workbench could display a more descriptive value (e.g., a name or label column from the referenced table) in place of the raw foreign key ID during data entry. Ideally, this would be implemented as a dropdown menu that shows values from a specified column in the referenced table, allowing users to select a meaningful label rather than inputting the ID directly.

Example:

If a products table has a foreign key to a categories table, instead of requiring the user to input the category_id, the UI could show a dropdown of category_name values, while still storing the correct category_id behind the scenes.

Allow users/or admins to configure which column from the referenced table should be displayed in the dropdown (e.g., via a UI setting or metadata annotation) would be perfect.

[tbantle22]

Hi @vedoa, thanks for the issue. Can you tell me more about how you're inputting data using the workbench (the spreadsheet editor, sql queries, file upload, etc)? Any screenshots or additional info is helpful

[vedoa]

Hi @tbantle22 , thanks for the fast response.

Setup:

docker-compose.yml

services:
  dolt:
    image: dolthub/dolt-sql-server:latest
    container_name: dolt-server
    ports:
      - "3306:3306"
    environment:
      - DOLT_ROOT_HOST=%
    volumes:
      - ./init:/docker-entrypoint-initdb.d

  workbench:
    image: dolthub/dolt-workbench:latest
    container_name: dolt-workbench
    ports:
      - "3000:3000"
      - "9002:9002"
    environment:
      - DOLT_REMOTE_URL=http://dolt:3306
    depends_on:
      - dolt

init/schema.sql

CREATE DATABASE IF NOT EXISTS shop;
USE shop;

CREATE TABLE categories (
    category_id INT AUTO_INCREMENT PRIMARY KEY,
    category_name VARCHAR(100) NOT NULL
);

CREATE TABLE products (
    product_id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    category_id INT,
    FOREIGN KEY (category_id) REFERENCES categories(category_id)
);

-- Example data
INSERT INTO categories (category_name) VALUES
('Electronics'), ('Books'), ('Clothing');

INSERT INTO products (product_name, category_id) VALUES
('Smartphone', 1),
('Novel', 2),
('T-Shirt', 3);

So adding a new row for the "products" table

or the spreadsheet editor

would be the preferred way from a manual input point of view.

I realized that these kind of features generally increase usability from "non technical" users that sometimes need to make a correction or input simple data. I think this feature would boost their productivity since they don't have to think about database design or what a foreign key is.

Thanks for the effort (and the licensing :) ) great product.

This is a very generic request and i meant it as a nice to have - i myself will stick to plain old bulk imports using any mysql client.