Data Relationships: One-to-Many Relationship

[intapiuser]

For general information on types of data relationships, potential complications and how to resolve them, see the following articles: 

• Data Relationships: Introduction to Many-to-Many
• Data Relationships: Check Type of relationship
• Data Relationships: One-to-Many Relationship (You are here)
• Data Relationships: The Invisible Many to Many
• Data Relationships: Many-to-Many Relationship Resolutions 

One-to-Many relationship – One table holds unique values for every row, but the other table holds duplicate values for any or all of the corresponding values in the first table. A One-to-Many relationship occurs when one side has individual, unique values for all records on the id field, while the other side holds duplicate values for the identifying field.

For instance, in the following scenario a relationship exists between the 'Categories' table and the 'Products' table.

The CategoryID is a unique identifier for the different categories in this table, and each record will hold additional information for each category, i.e. CategoryName, Description, Picture and so on. We'll distinguish between the different categories by their ID.

On the other side of the relationship, in our Products table, we'd like to know which category each product belongs to, i.e. juice, milk and beer will belong to the beverages category, while bananas, kiwi and , mango will belong to the fruits category. In addition to the unique Product id for each product In the Products table, we'll hold the CategoryID. This will create multiple values for the CategoryID in the Products table because many products can belong to the same category.

This means that the CategoryID in the Products table will not be unique, making this relationship a One-to-Many. Placing the CategoryID in the rows or series element of a widget and a measure from the Products table, we'll be able to aggregate values from the products table, with the unique CategoryID's from the Category table.

In the following scenario, a possible aggregation would be to count the number of Products in each Category, or to sum the QuantityPerUnit for each Category.

 Image 1. One to Many Schema