Understand and Evaluate Table Relationships

When assessing the relationship between two given tables, you need to understand how many possible occurrences in one table could belong to an entity in another, and vice versa. For example, use a users table and an orders table. In this case, you want to know how many orders a given user has placed and how many possible users an order could belong to.

Understanding relationships is vital to maintaining data integrity, as it impacts the accuracy of your calculated columns and dimensions. To learn more, see relationship types and how to evaluate the tables in your Data Warehouse.

Relationship Types

There are three types of relationships that can exist between two tables:

One-to-One

In a one-to-one relationship, a record in table B belongs to only one record in table A. And a record in Table A belongs to only one record in Table B.

For example, in the relationship between people and driver’s license numbers, a person can have only one driver’s license number, and a driver’s license number belongs to only person.

One-to-Many

In a one-to-many relationship, a record in table A can potentially belong to several records in table B. Think about the relationship between orders and items - an order can contain many items, but an item belongs to a single order. In this case, the orders table is the one side and the items table is the many side.

Many-to-Many

In a many-to-many relationship, a record in table B can potentially belong to several records in table A. And vice versa, a record in table A can potentially belong to several records in Ttable B.

Think about the relationship between products and categories: a product can belong to many categories, and a category can contain many products.

Evaluating Your Tables

Given the types of relationships that exist between tables, you can learn how to evaluate the tables in your Data Warehouse. As these relationships shape how multi-table calculated columns are defined, it is important that you understand how to identify table relationships and what side - one or many - the table belongs to.

There are two methods that you can use to evaluate the relationships of a given pair of tables within your Data Warehouse. The first method employs a conceptual framework that considers how the table’s entities interact with each other. The second method uses the table’s schema.

Using the Conceptual Framework

This method uses a conceptual framework to describe how entities in the two tables can interact with each other. It is important to understand that this framework assesses what is possible, given the relationship.

For example, when thinking about users and orders consider all that is possible in the relationship. A registered user may place no orders, only one order, or multiple orders within their lifetime. If you have launched your business and no orders have been placed, it is possible that a given user can place many orders in their lifetime. The tables are built to accommodate this.

To use this method:

This type of framework can be applied to any pairing of tables in your Data Warehouse. This allows you to easily identify the type of relationship and which table is a one side and which table is a many side.

Once you have identified the terminology that describes how the two tables interact, frame the interaction in both directions by considering how one given instance of the first entity relates to the second. Here are some examples of each relationship:

One-to-One

One given person can have only one driver’s license number. One given driver’s license number belongs to only person.

This is a one-to-one relationship where each table is a one side.

One-to-Many

One given order can possibly contain many items. One given item belongs to only one order.

This is a one-to-many relationship where the orders table is the one side and the items table is the many side.

Many-to-Many

One given product can possibly belong to many categories. One given category can possibly contain many products.

This is a many-to-many relationship where each table is a many side.

Using the Table’s Schema

The second method uses the table schema. The schema defines which columns are the Primary and Foreign keys. You can use these keys to link tables together and help determine relationship types.

Once you identify the columns that link two tables together, use the column types to evaluate the table relationship. Here are some examples:

One-to-one

If the tables are linked using the primary key of both tables, then the same unique entity is being described in each table and the relationship is one-to-one.

For example, a users table may capture most user attributes (such as name) while a supplemental user_source table captures user registration sources. In each table, a row represents one user.

One-to-many

When tables are linked using a Foreign key pointing to a primary key, this setup describes a one-to-many relationship. The one side is the table containing the primary key and the many side is the table containing the foreign key.

Many-to-many

If either of the following is true, the relationship is many-to-many:

Next steps

Correctly assessing table relationships is crucial to accurately modeling your data. Now that you understand how tables are related to each other, see what you can do with the Data Warehouse Manager.