Designing a Database

Hello everyone. I would like your comments and suggestions regarding the following application. This is how the present (non-computerized) system works which I must duplicate using INFORMIX-4GL: - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - A form is passed from office to office in a hierarchical manner from the lowest-level to the highest-level individual. Each office is responsible for reviewing and approving the form *before* it is given to the next office in the chain of command. If an office has not yet reviewed the form, then it is not passed on to the next office (which means that the individual in the higher- level office would have no knowledge that a form is being worked on by a lower- level office). The forms originate from managers throughout the organization. This means that the computerized system must restrict each manager to viewing and updating only those records that belong to him/her. After the highest-level office approves the form, copies are made so that it can be used to generate other forms. Then it is filed away. - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - I'm looking for suggestions regarding the design of the 4GL database. Any suggestions would be most helpful to me. I have thought about this for a little while and have come up with this design (using one table for the form): 1. Store the login id's of all managers & office users in a separate table. 2. Check who is using the application by selecting from the above table WHERE tablename.user_name = USER and storing the result in a program variable. 3. Based on the value of the variable, when the user does a CONSTRUCT only his/her records will be retrieved. When a new record is added the login id of the manager will be stored in a column of the table. (Office users do NOT add records; they only update records.) Processing By the Offices 4. The processing of the form seems to be the most difficult. How do I prevent an office from selecting a record that has not yet been approved by the lower-level offices (other than checking the value of some sort of "management approval" field in the form)? At the same time I must prevent the lower-level offices from selecting the records they have already processed. Should I use two menu options for this, one for new records and one for old? After a record has been processed, it must be removed from the table and stored in a history file for future use (to do end- of-year reports etc.). What's a good way to handle this part? In some ways, this application resembles E-mail. A letter is sent to one office, then to the next office and so on. When it reaches its final destination, it is printed on paper and stored in the history file. Should I use several (duplicate) tables, one for each office and attempt to run INSERT and DELETE commands on each table every time an office does an Update of a record (when a manager adds a record, it would need to be moved from the public users' table to the table for the first office)?? Would this be easier to code (and maintain) than keeping track of the value of the user_name column (considering the complicated CONSTRUCTs and IF statements)??? What about processing speed? Which would be faster? Please post your comments to the Informix List. Many Thanks in Advance =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= John Baker U.S. Army Information Systems Command - Lex Lexington - Blue Grass Army Depot Lexington, KY 40511-5109 Phone: (606) 293-3644 or 293-3743 E-mail: jbaker@lexington-emh2.army.mil DSN: 745-3644 or 745-3743 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=