1

Background

I am attempting to design an interface for users to browse data in a relational database. The features are similar to PhpMyAdmin but is intended to be simpler and more user-friendly for DB noobs.

Tables are automatically linked when column names match. So if two tables both have a column 'employee' then the browse function will automatically join the two tables before displaying. This simplifies the user's experience.

Problem

Consider a common scenario. A database with 10 tables with a total of 120 columns has 15 index fields that interconnect the database. Automatically joining the entire dataset will display results with 120 columns, which is excessively wide. I want to provide a method for selecting which tables to include in the results using a simple control (e.g. list) that shows users which (pre-joined) tables they are including, and how many columns they get. If the control was very effective, it might include column names, and basic info about each column (e.g. % null, avg value in a tooltip).

Question

What design pattern offers the best UX for selecting from a list of joinable database tables?

8
  • Trying to grock what you're doing here. You're just picking from a list of tables, then picking from a secondary list of tables to join on that table?
    – Zelda
    Commented Apr 6, 2012 at 19:16
  • @BenBrocka i'm trying to think of the most intuitive way to help users make an informed choice. It might mean hiding/burying/sorting-out some of the less likely options. It could be done with a list, 2 lists, a tree, a grid, etc. Commented Apr 6, 2012 at 19:24
  • So you're trying to visualize the relations between the tables to help them or provide suggestions or what?
    – Zelda
    Commented Apr 6, 2012 at 19:41
  • 1
    that question shows the opposite end of the visual complexity spectrum than i'm going for, about pretty much the same kind of data. i want to simplify the user choice as much as possible. so yes definitely text-based. Commented Apr 6, 2012 at 20:52
  • 1
    I am trying to grasp at what the user's goal is here, are they managing the database or are they querying information? If managing databases then they should know a thing or two about databases (sorry if that sounds a bit condescending). If they are querying information perhaps you could create a view which would have all of the columns available (ie. a report table) and on the UI side create an interface that allows people to choose columns to return?
    – lifeofmle
    Commented Apr 9, 2012 at 17:35

1 Answer 1

1

You appear to be creating a data visualization tool... all you need to do is add some more logic for auto-summarization (pie chart based on row type, bar charts, etc). If you weren't thinking of going this route, think about it... it will add a lot of value for a very moderate amount of work.

But back to the task. If you have the luxury of a graphical interface, I would recommend a directed graph. When you select a node (table) the graph shows all the tables that link into or out of that table. When you select a second node, the system adds all the tables that link into that second node as well. The directed graph should display directional arrows, and you could optimize many-to-many tables out so they just display a <--> link (and the linking table is invisible).

The complexity is managed by only displaying tables that are relevant (linked) from already chosen tables. The starting tables can also be pruned... you will never have people selecting from the States table as a starter, since it is just used to populate the Address table... which you also would likely skip in favor of the Customer or User table. So the initial list of tables to start from can be pruned. Once they have a starter table, of course you would allow the underlying tables to be selected (User -> Address -> State).

Each node of this graphical interface could display the column count. You could also have three choices... 'basic', 'common', and 'full'. Basic columns would just include identifiers and maybe the most important item (ID, Username). Common might include a limited set of the most common columns (ID, Username, First, Last), and Full would include every column in that table that is not proscribed (perhaps not Password, for example). This selection could be made with a right-click.

A circular reference chain could highlight the links that form the circle, and the user can select the one to 'break' so that the query is not circular (highlight links participating in the circular refernence, scissors cursor, change link to a dotted line on hover, fade it on selection)

The downside to this method is that it would not be easy to deal with the same table used in two different contexts... a common example being User -> User relationships (ie, manager vs direct report). However, there are ways to deal with this issue as well. For example, if you start with Employee, you could display the links in this fashion:

Direct Report -> Employee -> Manager

Starting with the middle item selected, you have people who report to that person, and you have the person they report to. This type of reference can be extracted from basic column information (fk_manager); the incoming reference would need additional meta-data (such as a reverse translation table with a row manager:direct report). All three tables displayed are the same Employee table, but in a different context. This also allows you to display the same linked tables in each context... for example, the above node graph would have three Address tables, one linked to each of the three Employee contexts.

Unless your schema is quite simple, you may also want to dynamically only display linked tables on hover... so only the most recent selected node you hover on will display the linked nodes. Make sure not to move the item under the cursor when animating the change, to avoid animation jitter.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.