END-USER Database interface 1

[ThatFella]

Hi all,

I'm looking for some pointers or examples on creating a graphical interface for the non-technical end-user. Meaning, I would design and implement the database and an easy-to-use interface through which a non-technical user would be able to run detailed queries. Ideally (though unlikely), the GUI would allow the user the same power as if he knew the database structure and used SQL. This seems to me like it would be a common problem, but I'm finding trouble finding information on it.

I was looking for any good examples on which I could model my interface, or atleast get some ideas. Screenshots would work, as would just suggestions on how to approach this problem.

Thanks.

--
ThatFella

 

[Golom]

How "non-technical" are these users?

The basic problem is that building complex queries is ... well ... complex! Even if you know the database structure and are a certified SQL guru.

One example of such an interface is the query designer in MS/Access. It removes some of the complexity in building SQL but it's hardly intuitive and I wouldn't expect that someone with zero database expertise would be able to build much more than a "data dump" sort of query with it.

Another option is in a Microsoft product called RMS (Retail Management System). The designers have created a suite of reports and the user interface is confined to specifing filters and sort order for those reports. The basic report design (i.e. Fields, Joins, etc.) are hidden however.

 

[SQLSister]

Well, it's an interesting question. And somewhat it depends on how complex a search you will allow them to have. You will have to consider such things as whether they can choose the fields in the results set or only the qualifiers in the where clause. Will you allow them to filter on more than one item? Will you allow only them to use OR as well as AND if they filter on multiple items? Can they select the tables or even the database? How are you giving them rights to the datbase objects? If you create dynamic queries, then you have to issue rights at the table level not the stored procedure level in SQL Server which is much less secure.

One thing I highly recommend is finding out from the users the 5-10 most common queries they run and creating a stored procure for each and a form in the user interface that allows then to select each of these and fill in the pertinent parameter(s) for that query. Then have a button to an advanced form which will allow more dynamic creation of queries.

I suggest this because the dynamic creation of queries is never effcient and in most places there are really only a few queries that are run often. By allowing then users to do these easily you make them happy and you can optimize the queries for efficiency - something you can't do if you only have one page that allows a wide variety of advanced searches. Then you still have the advanced search for the power users. Remember most users have no idea how to query data - if they must search using a complex, dynamic form for even the simple searches, they will panic. And likely they will query incorrectly and get the wrong results because they don't understand what they are doing.

AirTraveler Search

The link above will take you to a complex search we did. The first page of it has the most common values to search on, then it has another way to search by distance, and advanced search with 30 different possible variables and finally a graphical map search.

For another project, the users could only search on one field at a time, so they got a pull down menu with all the field names and the rest of the search form changed dynamically depending onthe data type of the field they selected. String data types got a text box and radio buttons to fill in whether the user wanted to search for the exact phrase, anywhere in the in the field or the prefix or suffix. Date fieds got two formatted text boxes to choose beginning and end dates, bit fields got a yes no pulldown, etc. The user could also search from a list of proposal names (by far the most common search.) and results could be sorted on a field after getting a result set. We did this by making the column headers for the result set buttons instead of text, one click to sort in ascending order and a double click to sort in descending order.

Questions about posting. See faq183-874

 

[ceh4702]

Depends how complex the database is and what kind of databse it is. Most report writers I have seen are far from simple. Usually you have to make up some configuration files even before you use a report writer.

For instance Microsoft has some software for Access that lets you design reports that uses VB as a programming interface and wizards of various kinds. The more complex kind of reports you want the harder it gets. As a rule most reports I have worked on for our education system get on the complex side. They always want something like Statistics on several groups by Quitiles based on income parameters and special groups.

I have found that one size does not always fit all in the IT World. With a database you will have to configure a set of views so the report format is more or less set and then the user can select how to select the records. Then you have restrictions for secutity about who has access to what data.

If you give us some specifics someone may have a possible solution or two. For instance what kind of organization is what for, what does the database contain, what kind of databse.

I have heard of some tools like easy trieve, Decision Analyzer, etc. We are working on a new ERP that is a combination of sub-packages sold by an outfit called Datatel, but they specialize in the Education/Higher Education Field.

You will probably have to develop your own hybrid applications based on whatever product you use. IT keeps getting more and more complicated.

If you do not like my post feel free to point out your opinion or my errors.