Fog Creek Software
Discussion Board

UI design/methodology for a many to one

Given two tables named Supplier (contains only three fields) and Location (contains ten fields)whereby Supplier is a one-to-many to Location, is it still considered common let alone good practise to use two grids (one for each table) in a form view to show the one-to-many relationship, or better to implement a many-to-one relationship using the Location table with lookup fields in a single grid to allow the end user to enter/edit all information at once. Any thoughts or comments regarding this subject matter are most appreciated.

Marty Potokar
Saturday, September 28, 2002

Of course the number one thing here is to get your data structures and designs correct. When you get that data design correct, the application practically writes it self.

Ok, so we will assume that your data structure is good. If we don’t have that right..then what follows does not matter!

Now, which approach best? Well, the answer is very simple:

It depends on the application, and the task the user is doing. The secret to good software is to make it task orientated.

Hence, what is the task the user is doing? If they are on the phone talking to a supplier, then it is likely that they bring up the supplier name, and a combo box would suffice to select the correct location.

However, if you are a person working in distribution, then you might bring up a location screen, and a grid below will display all the suppliers. Hence, the answer is not which GUIway to go..but what is the task at hand? It is the task at hand that dictates the UI...not the database structure!! (because I am assumed we have good designers on the team..and our data structures are going to be of a good design!).

Note that both above example tasks still have the same table structure. You GUI MUST BE dictated by the task at hand. Think of your self as a user...not about database structures.

Hence, if you have a good design, when you add a new Supplier, you can easily select a location. In addition, if you really care about the UI, then the user should be able to add a NEW location with out leaving the supplier screen. This of course means that the combo box that selects the location needs code to handle the not in list situation. (ms-access even has a not in list event for this purpose....VB sure could use that feature).

If you don’t add the feature to add a new location when adding a supplier, then your users will have to back out and bring up a location screen where they can add the new location...and then go back to the supplier screen.

What this means is that you might wind up with both approaches, since you might have two users doing a different task.

Albert D. Kallal
Edmonton, Alberta Canada

Albert D. Kallal
Saturday, September 28, 2002

Combo Box - Supplier Name.
2 labels (Other supplier details).
Grid - Locations.
My 2c.

Sunday, September 29, 2002

Combo Box = Dropdown List I mean.

Sunday, September 29, 2002


First, thank you for the reply. As a Delphi programmer , while I have all the tools that you mention at my disposal (as I am familiar with and often use a 3rd party db LookupCombo with drop down list that includes a Not-in-list event), what do think about using a page control with tabs. For example, one tab is labeled 'Suppliers' and a second 'Location'. This allows the end user to add either a supplier or a new location by switching between tabs. This particular design would allow the end user to view all locations for a particular supplier at a glance, or add/edit a supplier or location without loosing his/her place when moving from/to the Supplier or Location tab/view.

Marty Potokar
Sunday, September 29, 2002

First, I don’t want to get caught up too much in the tools. My only point about using the “not in list” feature is that it is a simply good UI feature to have. You just want to use some tools that make this programming task easy, or you will wind up not adding the feature (and thus forcing your users to back out of a screen to add a value, and then come back). Hence, just make sure you can add this feature to your application(s) with ease, or you will leave it out. This applies to any development environment you work under. You not only want to have this ability in your software bag of tools...but it MUST BE EASY to implement, or you will refrain from offering the ability to “add not in list” to your users.

Now, to this issue to using a tab. I am a big fan of tabs. They are constantly rated *very* high from a usability UI point of view. However, as mentioned, it depends on what the user has to do, and their primary task. It is also going to depend how many suppliers (and locations) your have. How is searching done right now?

Again, your UI must be driven by the task at hand. If users spend all day looking up suppliers by location, then you will do well to give them a nice search screen, and a stepped process that allows this. Hence you would get the following:

Search for location
    Display list of locations
          Select location from list (pick list)
          launch edit form
          Edit or view data..then close from
    Until done editing select list
Until done searching

When you have a sec, I have an example of the above type searching screen at:

Of course, if you only have a few locations, then the above might not apply. If you have hundreds of locations then I suggest the above approach. Again, it is the task at hand that important?. How hard is it to search for a location? Do you need to drill down by Country?, by city?, by state? Again, if you only have a total of 4 locations, then the above approach would be over kill.

Of course, once you do find that location and display all the information, then a tab for the suppliers would be ok. Is the primary task to lookup and view locations, or lookup locations and *then* view the suppliers? If the primary function is to find a location, and *then* view suppliers I would avoid the tab.

I want to state that I am a big fan of what Microsoft calls the ” Inductive User Interface“, or the IUI type approach to software. That is just a fancy word to say break things into logical steps.

Thus, I am of the camp that each process and series of steps a for a user *task* needs to get broken down (that means a series of forms). Thus, you can see I am suggesting 2, or even perhaps even 3 forms to accomplish the above simple task of searching.

I think in your case the tab is ok.

More info on the IUI concept can be found at:

Albert D. Kallal
Edmonton, Alberta Canada

Albert D. Kallal
Monday, September 30, 2002

I'd pretty much agree with Albert in lots of ways though I have a few caveats.

Personally I don't like sticking the same button on multiple rows.  It makes it easier for the user the first few times but its visual ugliness will irritate them after a while and they'll wonder why they can't just click on the row the way they do with other stuff.

So I'd have the same functionality (hit the row you want to look at the details) but hide it visually.  The trick that you double click what you're looking at to get more information is easily learned.

The other point is that I notice that in american (in the continental sense rather than just the USA), db apps there's a tendency to stuff a lot of information into grids.  Now perhaps the average resolution is higher there, or perhaps they are better at taking in a lot of information using a scroll bar but I've found that on the whole only around 5 fields or so are usable on a grid before the user starts to complain.

It used to be that I'd have to write apps for users with 640 x 480 resolutions (higher than that and they'd complain they couldn't read the text).  Now its more frequently 800 x 600 but 1024x768 is still too high a leap.

This is on equipment that is entirely able to display at these resolutions.

Simon P. Lucy
Tuesday, October 1, 2002


Once again, thank you for the feedback. Needless to say, your words are reassuring and it appears that I am on the right track given what I am trying to accomplish. Based upon your recent reply and after having experimented with a number of different UI's, the tab format appears to work best in this case as the number of locations will more often than not never exceed ten and in most cases will only be one to two at most. As there are only three fields in the Suppliers table, the grid works great for viewing and selecting records as the user will be able to see all without having to scroll from right to left or vice versa. If you wouldn't mind, I'd like to forward you several jpegs to illustrate what I've done and to get your opinion on same. In the meantime, I will definitely take at look at the site.

Thanks again,
Marty Potokar

Marty Potokar
Wednesday, October 2, 2002


In answer to your last reply, I forgot to add that the primary task in this particular case is to look up Suppliers but you no doubt know this already given my latest reply.


Marty Potokar
Wednesday, October 2, 2002

*  Recent Topics

*  Fog Creek Home