how to add/edit/manage related data in multiple tables

Question

I have several tables that i need to create an admin interface for

Table 1
Table 2
Table 3
Table 4
Table 5

each table's contents are reflected of which parent it belongs to in a field...so

table 2, has a field for which row in table 1 it relates to, table 3 has a field for relating to table 2 and so on.

Whats the best way to present this to the user so they dont have to memorize id numbers. Say for instance, they want to add a new entry in table 3, they must select which row in table 2 to link to.

The relationship only extends one level above after table 1. So creating a new set of options in table 5, would involve creating an entry in table 1, new entry in table 2, linking it to table 1, new entry in table 3, linking it to table 2, new entry in table 4, linking it to 3, and finally the new option in table 5.

So my question is user-interface related as to the best way to present this to the user. Alternatively, what is this kind of system called, so i can search for other examples.

Thanks.

Answer 1

From what you're saying, it looks like it's not possible for one entry in a table to exist without the corresponding entries in the other tables, right?

In that case, you could present a wizard like interface which prompts the user to enter the data for each table on each new wizard page, starting from the data for table 1. Then once all the data has been collected, you can fire off a series of update statements where the id of the record in table 1 is reused for the inserts in tables 2-5. (Depending on your table design, you might get this id using LAST_INSERT_ID()).

Or if you don't want to use multiple insert statements, you might use an updateable view (if you're using MySQL 5).

Alternatively, you could have a table structure where a record in table X must have a linked record in table Y (where Y < X). In other words, a record in table 1 may not have a linked record in table 2, but a record in table 2 must have a linked record in table 1.

In this case, you can still use the wizard, but you can start with the table that you actually want to create information for, then have the wizard prompt for data for the previous table, and so on until it prompts for data for table 1. So if you really want data to be created for table 4, have the wizard prompt for that first, then prompt for data for table 3, and so on up to table 1. Then do the data entry as before.

Answer 2

Sounds like a problem ready-made for my old favorite, the master-detail layout. Each table is represented in a separate pane (Panes 1 through 5) displayed in the same window. The panes are arranged top-to-bottom and/or left-to-right for Tables 1 through 5 sequentially. In each pane, there is always exactly one “active” record, which is marked for the user in some way. The contents of each pane are determined by the active records in the panes above/left of it. Thus, Pane N shows the Table N records for the active record of Table N-1 shown in Pane N-1. The active record in a pane is whichever record in the table last had focus (in any field). Thus, for example, the user can display the Table N records for a particular record in Table N-1 by clicking on any field for that record in Pane N-1. Queries for Pane N are launched asynchronously once the active record changes in Pane N-1 (no “Refresh” button, please). All non-read-only fields should be edit-in-place. A Save button or menu item inserts/updates all records of all tables as a batch (alternatively, you can fire an Update whenever a field is edited and focus leaves it, if your bandwidth can handle it).

Thus, to add a new record to Table N, the user places focus on the correct records for Table 1 through Table N-1, then places focus in Pane N (e.g., click anywhere in it) and selects the Add Record menu item. This inserts a new blank record in Pane N for the user to complete. The user can continue to select Add Record to fill Pane N with the desire records for the active Table N-1 record. At any time, the user can use the same process to add Table N+1 records for a newly created Table N record by clicking on Pane N+1 and selecting Add Record. Also, at any time, the user can edit the fields of the records in any pane. (Alternatively, you can have separate Add Record menu items for each table, which saves the user having to shift focus to a pane to add records, but that many menu items can get more cluttering than its worth; another approach is to always have a blank record in each pane ready for the user to fill out, eliminating the need for the Add Record menu item).

This design provides the users with the most flexibility, allowing them to add, delete, and update records in any order convenient for any table at any time. Because the window “remembers” the active records between edits, it’s also very efficient, eliminating the tedious re-selecting of Table 1 through N-1 records to edit a series of Table N records for a particular Table N-1 record (unlike, say, a wizard). It displays all the records for all tables in a single window in an intuitive hierarchical layout that facilitates viewing and exploring the data and minimizes navigation among windows or pages (again, unlike a wizard).

Five panes is a lot for a single window, but not too much. However, it would help if you provided easy ways for users to resize and hide/show each pane. Thus, if the user needs to work on a bunch of Table N records of a record in Table N-1, she or he can hide all the other panes, expanding Pane N to full window size to minimize scrolling. Also, if the user does not ever need to study or edit the records of some of your tables, do not put them in panes of their own, but rather make each appear as a field for the adjacent table in the structure. For example, if the users aren’t allowed to edit Table N, then instead of Pane N you can have a field in Pane N+1 that displays the functional name of the Table N record that each Table N+1 record belongs to. By making it a drop-down list, user will be able to assign/re-assign the Table N record for any Table N+1 record.

Details at http://www.zuschlogin.com/?p=31.

More stuff at Stack Overflow than may be relevant:

Hierarchy Visual Design

UI design pattern for multi level grid

What’s best when inserting into a table view, and add button or a blank line?