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 > =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= John - I think you would find this database design *much* simpler if you were to build a logical data model first. The difficulty you are encountering is due to confusion between the following two issues: 1. What is the data I am concerned with, and what relationships will need exist among the various pieces of data (at a conceptual level)? 2. What is a workable relational datbase design (ie. tables, columns, etc.) that is consistent with my conceptual understanding, and is also workable from a practical perspective? For trivial cases, it isn't that hard to solve both of these problems in one step. However, trying to solve more complex cases in one step can be very confusing, and lead to a poor design (eg. a design that doesn't work for all cases and/or is very difficult to modify when requirements change). Building a logical data model is generally just the process of understanding (and usually diagramming, using an Entity-Relationship Diagram) the following: 1. What are the data ENTITIES of interest in this problem? (ENTITY: A class of people, places, things, or concepts having characteristics of interest.) In the problem statement above, it seems that the obvious entities would be the following: FORM OFFICE USER 2. What are the RELATIONSHIPS that must be understood among the ENTITIES? eg. One USER is within one OFFICE. One OFFICE is subordinate to another OFFICE. One USER is currently working on FORM. 0 or more FORMs have been approved by 0 or more OFFICEs. The idea, at this point, is to TEST THESE STATEMENTS AGAINST REALITY - is each statement true? Are all the statements relevant? Are relevant statements missing? If you wish, you can represent these statements in an E-R diagram. This is a better, more intuitive communication tool than a bunch of sentences, but is not absolutely necessary. 3. What are the ATTRIBUTES of each ENTITY? Which attributes UNIQUELY IDENTIFY each ENTITY? eg. ENTITY: USER ATTRIBUTES: Last Name First Name OFFICE **Employee Number Phone Number . . . (Employee Number uniquely identifies one user from another) At this point, there has been no discussion of tables & columns. The whole idea is that the logical statement of what the data needs to look like is kept separate from the physical design. This allows you to keep questions like: Is there really exactly one OFFICE associated with one USER? separate from questions like: Should I use one table or multiple tables for OFFICEs? Having completed a logical model, (ie. verifying that it contains a complete set of true, relevant statements & that all the data you will need to store to support your application is represented somewhere in the logical model) the next step is to translate the logical model into a physical database design: 1. For each ENTITY in the logical model, create a TABLE in the database desig