Build Sports Team API with GraphQL - Hasura - Part 2
Tags: graphql, postgres
Published: 2021-06-19 03:34:49

This post is part of a series of post and so if you haven't read the previous one, please read that and come back and we will wait :) 

## Objectives of this post

1. Introduction
2. What is one-to-one table relationship
3. Creating support staff table
4. Linking the Teams table with support staff
5. Update the relationship in Hasura
6. Fetching the data

## Introduction

In the first part, we learned how to create a team table and how to fetch data from the table using the GraphQL queries. 

[BUILD SPORTS TEAM API WITH GRAPHQL - HASURA - PART 1](https://www.eternaldev.com/blog/build-sports-team-api-with-graphql-hasura-and-postgresql-part-1/)

Before we start with adding players to the team we will first focus on adding a head coach for the team. We will learn about one-to-one table relationship.

## What is one-to-one table relationship

For our team, there can be only one Head Coach and for a Head Coach position there is only one team which he manages. This relationship is called the one-to-one relationship and it can be expressed by adding a foreign key to the table. Foreign key refers to primary key of other tables.

![Primary and Foreign Key Association](https://www.eternaldev.com/static/845910e2d5a8d9795312e66dd85d472f/705cc/sportsapihasura2_foreignkey.png)

## Creating support staff table

Let's create a support staff table which will have the details of the support staff. We can have simple information about the staff and you can expand on the data if you need.

![Staff Table](https://www.eternaldev.com/static/467c4b4270ba7ebade80d6e3f45857fa/c6ff8/sportsapihasura2_supportstaff.png)

## Linking the Teams table with support staff

Open the team table which we have created in the [part 1]((https://www.eternaldev.com/blog/build-sports-team-api-with-graphql-hasura-and-postgresql-part-1/)) and add a new column as below

Column name: head_coach_id, Type: Text, Nullable: True

![Head Coach ID Column](https://www.eternaldev.com/static/c8bcf12fa579eadc8b050be143f92b90/c1c45/sportsapihasura2_teamheadcoach.png)

Once we have the new column, we need to add a Foreign key relationship to the `SupportStaff` table so that `teams` table knows that this column references the row in the other table.

Open the `Modify` tab and then move to the `Foreign Keys` section. You can add a new foreign key linking the other table `id` column in this place

![Foreign Key link](https://www.eternaldev.com/static/04b8e9e1cbf2dc31efe12fe81d9dcdb0/f96df/sportsapihasura2_teamforeignkey.png)

## Update the relationship in Hasura

Once you have the foreign key relationship defined, we still have to create a relationship in Hasura GraphQL engine to make it easier to access the data inside the `SupportStaff` table

WHY ?

Right now once we insert the data into the SupportStaff table and then add that id in the team table and make a simple GraphQL query to get the data, It would be just the actual id of the head_coach which will be returned

```
query MyQuery {
  teams {
    team_name
    head_coach_id
  }
}

OUTPUT:
{
  "data": {
    "teams": [
      {
        "team_name": "Australia Cricket Team",
        "head_coach_id": 2
      },
      {
        "team_name": "Indian Cricket Team",
        "head_coach_id": 1
      },
      {
        "team_name": "England Cricket Team",
        "head_coach_id": 3
      }
    ]
  }
}
```

This is not so useful to the end user. When displaying the data, We might want to show the name of the coach instead of the id. So we can create a relationship in Hasura to make this simple when fetching data.

Open the `Relationships` tab in `teams` table and then you can see that Hasura will suggest a Object Relationships. Add the relationship and you can name it `head_coach` 

![Foreign Key Relationship](https://www.eternaldev.com/static/5221e32d862061089dfb608bde622c7b/9239a/sportsapihasura2_teamobjrelationship.png)

## Fetching the data

Once this is done you can now query for the head_coach name using the below GraphQL Query. Now the name which we gave in the relationship will be acting as a separate field and you can drill down into that field to find the data which is present in the other table. So in a single query you have the data from two separate tables and you can present this data easily in the UI of your app.

```
query MyQuery {
  teams {
    team_name
    head_coach_id
    head_coach {
      staff_name
    }
  }
}

OUTPUT:
{
  "data": {
    "teams": [
      {
        "team_name": "Australia Cricket Team",
        "head_coach_id": 2,
        "head_coach": {
          "staff_name": "Staff 2"
        }
      },
      {
        "team_name": "Indian Cricket Team",
        "head_coach_id": 1,
        "head_coach": {
          "staff_name": "Staff 1"
        }
      },
      {
        "team_name": "England Cricket Team",
        "head_coach_id": 3,
        "head_coach": {
          "staff_name": "Staff 3"
        }
      }
    ]
  }
}
```

## Next Steps

Next we will add more support staff to the team to learn about one-to-many relationship and then we will start adding player data to the teams.

Stay tuned by subscribing to our mailing list and joining our Discord community

[Discord](https://discord.gg/AUjrcK6eep)