Click above to get retro games delivered to your door ever month!

   The most critical activity of this phase is in no doubt the definition of
   the relations between files and the corresponding referential integrity
   rules. Relations, establishing the connection between the various files of
   the database, are the backbone on which the whole application is built.
   They are indispensable for the realisation of a database management
   application.

   A relation expresses the dependence between records that belong to two
   involved files. There are different types of relations: N:1, 1:N and 1:1.

N:1 Relation

   The N:1 (Many-To-One) relation, also called LookUp relation, allows to
   relate one file to another file using one or more common fields. The child
   file (1) contains the common field plus additional fields for the
   information used to complete the data in the parent file (N), which
   contains only the common field.

   For example, let's suppose to have an Articles file that contains
   information relative to the single articles and a Categories file that
   contains general information, relative to the whole category of articles.
   The 2 files contain a common field, called CatCod, that identifies each
   category uniquely. The Category file also has a primary index on this
   field.

   Setting a relation between the two files on the CatCod field will assure
   that one record of the Article file correspond to one record of the
   Category file. Because of the fact that many articles can be part of one
   category, a N:1 relation is created, making it possible to reach the
   information regarding that category.

   When the application is designed, the N:1 relation causes a set of
   controls to be performed automatically. Using the above example, the
   system can verify the existence of the entered code in the Article's
   data-entry; it can provide a data-entry facility when inserting non
   existing category codes; it can create a pick-list window to select a code
   from.

   Let's now suppose that we are to create a Data Dictionary containing a
   certain number of files that all share the CatCod field. The CatCod field
   will have the same significance in all the files, i.e. it represents the
   article's category. It is also logical to assume that an N:1 relation is
   necessary between each of these files and the Category file.

   dBsee gives the possibility to copy the relation created on more than one
   file automatically. The procedure used is as shown below:

     . When an N:1 relation is created, dBsee will automatically
       add the same type of relation between all the other files that contain
       that field and the lookup file. In the above example, a N:1 relation
       would have been created between all the other files that contain the
       field CatCod and the Category file.

     . If a new file is created, and a field is added on which a
       N:1 relation already exists, the N:1 relation between that file and
       the lookup file will be automatically established. This procedure will
       also take place if the field is added to an already existing file.

   This behaviour can be turned off, so that this automatic procedure will
   not be executed. Inside the Service/User environment, the Automatic
   relations options sets the way dBsee manages N:1 relations. If it is set
   to 1, the N:1 relation will be automatically inherited. If set to
   0, this will not happen.

   To take advantage of this process, we suggest that, as the first thing,
   you create the different files with their respective fields, then set the
   necessary N:1 relation, leaving it up to the system to automatically set
   the other relations. Later, if needed, the undesired relations can be
   removed.

   Another mechanism that N:1 relations allow (together with all other kinds
   of relations) is to use inherited fields. This mechanism gives the
   possibility to assign to a file's field, values coming from a lookup file.
   Let's consider the following example:

   Between the Customers and City files, a N:1 relation is defined on the
   City field. The following inherited fields have been set:

     CusCit := City->City
     CusSta := City->CodSta
     CusPro := City->CodPro

   Each time a new record is inserted in the Customers file:

   1.  the name of the city will be searched for in the City file

   2.  if the entered City name is not found, the system will ask if the new
       value has to be inserted. If this is the case, the City field, in the
       City file, will be assigned the new typed-in code.

   3.  the CusSta and CusPro fields in the Customers file will inherit the
       values of the CodSta and CodPro fields, respectively, found in the
       City file.

1:N Relation

   A 1:N (One-To-Many) relation is established every time one record in a
   parent file is linked to many records in a child file. Examples of 1:N
   relations can be found in practice: details of an invoice or order, the
   employees of a company, the companies of a holding, etc. 1:N relation are
   a common topic in database design.

   Defining a 1:N relation in dBsee is quite simple. It is necessary to
   specify the parent file, the child file, and the index of the child file
   on which the relation is to be set. The child's index file should be
   indexed on the common field(s) of both files. Each of the common fields
   will automatically become an inherited field (see above) in the relation.
    Let's consider the following example:

   Let's also suppose that the database contains the following information:

   Setting a 1:N relation between Co and CoPer on index CoPer1,
   and defining that key field CoCod is linked with field Co->CoCode
   and that field PerCod is automatically increasing, will determine the
   following mechanisms:

     . The automatic and complete handling for all types of data
       entries where many records of the CoPer file must be displayed for the
       associated one header record corresponding to the Co file.

     . During insertion of new records in the CoPer file, the
       system will automatically copy the contents of the CoCod field of
       the Co file into the CoCod field of the CoPer file, thus
       keeping the link between the 2 files. At the same time, it will
       compute the next higher value and assign it to the PerCod field.
        For each new Co record, the system will restart the PerCod
       counter.

1:1 Relation

   The 1:1 (One-To-One) relations are usually the less used in database
   applications. This type of relation is used in cases that a large amount
   of information, belonging to a single record, is to be split on 2 or more
   files, or part of the information in the record is available only in
   specific cases.

   For example, a company's mailing address is usually the same as the
   billing address. In this case, there is no need to store the billing
   address. But in the case that the billing address differs, the need to
   save this (additional) information occurs. Reserving a field in the main
   file would cause valuable space to be wasted if no billing address is
   used.

   The 1:1 relation overcomes this problem. The billing information is stored
   in a separate file, and the 1:1 relation links this data to the correct
   company. The system will create a record in the Billing addresses file
   only when necessary.

   The database, in the example, would be like this:

   In this database, to some (not necessarily all!) records of the Co file, a
   record (only one!) of the CoInv file will be associated.

   --------------------------------------------------------------------------
   Avoid creating unnecessary relations.
    The possibility of creating relations between files of the Data
   Dictionary with ease can cause the definition of unnecessary relations
   that weigh down the programming phase of the project without adding
   anything new to the final application.
   --------------------------------------------------------------------------