Data Model Oracle DBMS

Table Relationships

One-to-Many Relationship

    --------------------                            -----------------------------
    |   rls Lease      |           lse_s            |   rlsr Recurring Charge   |
    |==================|--------------------------->|===========================|
    |   lse_s: lease   |                            |   lse_s: lease            |
    --------------------                            |   unique_l: record #      |
                                                    -----------------------------

A relationship line with an arrow at one end indicates a one-to-many relationship between two tables. For each lease found in the rls table, there can be multiple recurring charge records in the rlsr table. The two tables are joined by the lse_s column, which is found in both tables.

One-to-One Relationship

    --------------------                            ------------------------
    |   rls Lease      |  lse_s          app_lse_s  |   rdm Demographic    |
    |==================|----------------------------|======================|
    |   lse_s: lease   |                            |   app_lse_s: lease   |
    --------------------                            ------------------------

A relationship line with no arrows at either end indicates a one-to-one relationship between two tables. For each lease found in the rls table, there is only one demographic record in the rdm table. The two tables are joined by matching the lse_s column from the rls table with the app_lse_s column from the rdm table.

Many-to-Many Relationship

    --------------------                            ------------------------------------------
    |   rls Lease      |  lse_s          app_lse_s  |   vrgc_grt Guarantor Cross-reference   |
    |==================|<-------------------------->|========================================|
    |   lse_s: lease   |                            |   app_lse_s: lease                     |
    --------------------                            |   grn_s: guarantor                     |
                                                    ------------------------------------------

A relationship line with an arrow at both ends indicates a many-to-many relationship between two tables. For each lease found in the rls table, there can be multiple guarantors in the vrgc_grt view. Also, for each guarantor in the vrgc_grt view, there can be multiple leases in the rls table. The two tables are joined by matching the lse_s column from the rls table with the app_lse_s column from the vrgc_grt view.

One-to-Many Relationship Within One Table

    ----------------------------------
    |   rcu Customer                 |
    |================================|
    |   cus_s: customer              |<------------+
    |   par_cus_s: parent customer   |  cus_s      |
    ----------------------------------             |
                    | par_cus_s                    |
                    |                              |
                    |                              |
                    +------------------------------+

A relationship line which starts and ends at the same table box and has an arrow at one end indicates a one-to-many relationship of a table with itself. A customer may belong to a parent customer and a parent customer can have many customers. An example of such a relationship is a parent corporation with several subsidiaries.

Exclusive-Or Relationship

    ---------------------                               -----------------------------
    |   ral Lessee      |             les_s             |   rhl Historical Lessee   |
    |===================|^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^ ^|===========================|
    |   les_s: lessee   |                               |   les_s: lessee           |
    ---------------------                               -----------------------------

A relationship line consisting of caret (up arrow) characters indicates an exclusive-or relationship between two tables. In this example, a specific lessee would be found on either table, but not both. Active lessees are stored in the ral Lessee table, and on termination are transferred to the rhl Historical Lessee table.

Relationship With Multiple Join Columns

    ------------------------                       -------------------------------
    |   rls Lease          |                       |   rtx Monthly Transaction   |
    |======================|                       |=============================|
    |   por_s: portfolio   |     p/c/r/o/lse_s     |   por_s: portfolio          |
    |   com_s: company     |---------------------->|   com_s: company            |
    |   reg_s: region      |                       |   reg_s: region             |
    |   off_s: office      |                       |   off_s: office             |
    |   lse_s: lease       |                       |   lse_s: lease              |
    ------------------------                       |   d_run_s: run date         |
                                                   |   t_run_s: run time         |
                                                   -------------------------------

In some relationships, more than one column is required to join two tables. In the above example, the abbreviation p/c/r/o/lse_s is used to indicate the tables are joined by columns portfolio, company, region, office, and lease. While lse_s by itself may appear to be the logical column to join the tables together, it is not recommended in this case because lse_s is not defined as an index on the rtx table. Instead, all five columns must be used for maximum performance.