Table Relationships

Chapter 9 - Working with Multiple Tables
Mastering Crystal Reports 9
by Cate McCoy and Gord Maric
Sybex 2003

Building appropriate links between two tables not only requires knowledge of the data, it also requires knowledge of what is actually happening to the data during the linking process and what kind of result is desired. The relationship achieved between tables based on linking a field will be one of four types: one-to-one, one-to-many, many-to-many, or no relationship.

One-to-One When linking Table A with Table B, for each record in Table A, there is exactly one matching record in Table B. In Figures 9.10 and 9.11, the Resorts table and the ResortFinanceCompany table both contain a ResortCode field. For each unique record in the table identified by the ResortCode, there is exactly one match in the other table. The business rule that this is enforcing is that each resort has exactly one finance company; you would combine the tables to create a report that contained the finance company name and the resort name.

Figure 9.10. Resorts

Figure 9.11. Finance companies

One-to-Many When linking Table A with Table B, for each record in Table A, there are multiple matches in Table B. This is the most common type of table relationship. In Figures 9.12 and 9.13, you can see that for each RegionCode listed in the Regions table, there are multiple entries in the Resorts table. The business rule in effect in this situation is that one region can contain many resorts.

Figure 9.12. Regions

Figure 9.13. Region resorts

Many-to-Many The concept is that in linking Table A with Table B, multiple entries in Table A match multiple entries in Table B. In reality, many-to-many tables cannot be involved in a Crystal linking activity nor are they a good idea for a database table due to poor performance issues. When a database designer creates a database schema, many-to-many relationships are generally split into multiple one-to-many relationships.

No Relationship There is no business rule that states that a table has to be related to another table. Sometimes, a table is simply used to store information that is referenced in lookups or by applications. A standalone table not involved in a linking relationship is said to be a reference table.

Use of content on this site is expressly subject to the restrictions set forth in the Membership Agreement

Conello © 2000-2003     Feedback