View Full Version : Design question - Searching multiple tables


Steve Bovino
09-18-2009, 09:42 PM
I would appreciate any thoughts on this project. This application presents the user with five different forms which represent 5 different types of employee evaluations. At present I have one table per each evaluation type. It is possible that one individual over the course of time can have several different types of evaluations, perhaps all five. My quandary is this. Users will need to search for all evaluations for a given employee and be able to view the form that was used to enter the data. How might I design the database with this searching requirement in mind? Thanks in advance for your insight and ideas.


Steve Bovino
09-20-2009, 11:01 AM
I did a lot of experimentation and came up with the following solution. I could have used a database set with the parent record being the employee and each type of evaluation being represented as child records. I choose another solution to provide a very clean and easy to use search engine.
Data entry and control is very tightly controlled in this app. When a user saves a record(creates an evaluation) three key pieces of information are appended to a summary table.

The summary table contains a creation date employee name, type of observation and control number for every employee evaluated. Each evaluation table has an auto increment number that is a unique value and uniquely iidentifies records in each table. When it is time to search, the user accesses the summary table presented as a browse. This summary table can be filtered by the user in several ways. Once filtered, when a row is selected I have Xbasic code that reads data (hidden fields on the form containing the browse) and conditoinally selects the appropriate form (type of evalaution) and the exact record.

A button holds this code which lets the user pull up the evaluation form from which the user can make edits or print. This is a networked application that will be placed in 13 schools with as many as three users per school. It is not a heavily used day to day application. Xbasic made it possible and I was able to use examples from Alphasports plus snippets of code from the message board. In the event that the summary table becomes corrupted I am developing code that can rebuild it.

09-22-2009, 12:59 PM

I was going to recommend a set too. Your second solution seems also very good solution. If you use V10 web solutions you might be able to be a lot more creative, because of the Java/Ajax features.

By linking the grids in a tabbed ui. Having your summary table open up to the different child records for view or printing.

Anyway you seemed to talk it out just fine.

Steve Bovino
09-23-2009, 10:34 AM

Thanks for the feedback and suggestions. Much appreciated.