Go back to the guide - Prepare your data
May. 10, 2019

Handling Data Relationships

On this page

When you want to index relational data into Algolia, deciding on how to structure it can be a head-scratcher. Ultimately, it all boils down to what search experience you want to create.

Imagine you’re developing a marketplace for third-parties to sell second-hand games. There are many ways you could build your search experience:

  • you could display sellers and their full game catalogs,
  • you could display games titles and a list of people who sell them,
  • you could display classified ads, ranked by business relevance (seller’s popularity, etc.)

Depending on the experience, you need to represent data relationships differently.

The good news is, Algolia doesn’t impose a data schema. You can organize your data the way you want, as we support different types of data structures. Therefore, depending on what you want to achieve, you can rearrange your data as you see fit.

Data source

When you’re fetching from a relational database (such as MySQL), you usually have one entity per table and join tables for many-to-many mappings. In your case, it could look like:

Games

id title
1 Grand Theft Auto V
2 The Legend of Zelda: Ocarina of Time
3 Mass Effect 3

Sellers

id name popularity total_sales country
1 Alice 1000 13 France
2 Bob 900 42 USA
3 Mass Effect Fan 800 50 USA

Seller/games

id seller_id game_id
1 1 1
2 1 2
3 2 3
4 3 1

After making a join request, the serialized data might look like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

[
  {
    "id": 1,
    "name": "Alice",
    "popularity": 1000,
    "total_sales": 13,
    "country": "France",
    "games": [
      {
        "id": 1,
        "title": "Grand Theft Auto V"
      },
      {
        "id": 2,
        "title": "The Legend of Zelda: Ocarina of Time"
      }
    ]
  },
  {
    "id": 2,
    "name": "Bob",
    "popularity": 900,
    "total_sales": 42,
    "country": "USA",
    "games": [
      {
        "id": 3,
        "title": "Mass Effect 3"
      }
    ]
  },
  {
    "id": 3,
    "name": "Mass Effect Fan",
    "popularity": 800,
    "total_sales": 50,
    "country": "USA",
    "games": [
      {
        "id": 1,
        "title": "Grand Theft Auto V"
      }
    ]
  }
]

Displaying sellers and their catalog

A nested data structure would work well to create a search experience where you would display a list of sellers and their associated games.

From there, you could streamline the data by removing unnecessary attributes, and index it as follows:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

[
  {
    "name": "Alice",
    "popularity": 1000,
    "country": "France",
    "games": [
      "Grand Theft Auto V",
      "The Legend of Zelda: Ocarina of Time"
    ]
  },
  {
    "name": "Bob",
    "popularity": 900,
    "country": "USA",
    "games": [
      "Mass Effect 3"
    ]
  },
  {
    "name": "Mass Effect Fan",
    "popularity": 800,
    "country": "USA",
    "games": [
      "Grand Theft Auto V"
    ]
  }
]

Searchable attributes

This experience focuses on sellers, which means you may want to prioritize searching on seller data. For example, when a user searches for “mass effect”, you may want seller Mass Effect Fan to show up before sellers who sell Mass Effect 3, even if Mass Effect Fan doesn’t sell this game. To do so, you can create an order of priority in your searchableAttributes, by setting name first and games second.

Displaying game titles and their sellers

You can use the same type of structure to create a search experience where you would display a list of games and their associated sellers.

To achieve this, all you would need to do is to reverse the relationship: instead of nesting games per seller, you would nest sellers per games.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

[
  {
    "name": "Grand Theft Auto V",
    "sellers": [
      {
        "name": "Alice",
        "popularity": 1000,
        "country": "France"
      },
      {
        "name": "Mass Effect Fan",
        "popularity": 800,
        "country": "USA"
      }
    ]
  },
  {
    "name": "The Legend of Zelda: Ocarina of Time",
    "sellers": [
      {
        "name": "Alice",
        "popularity": 1000,
        "country": "France"
      }
    ]
  },
  {
    "name": "Mass Effect 3",
    "sellers": [
      {
        "name": "Bob",
        "popularity": 900,
        "country": "USA"
      }
    ]
  }
]

Searchable attributes

This experience focuses on games, so you may want to prioritize searching on game data. For example, when a user searches for “mass effect”, you may want game Mass Effect 3 to show up before seller Mass Effect Fan, especially if they don’t sell that game. For this, you can create an order of priority in your searchableAttributes, by setting name first and sellers.name second.

Displaying classified ads ranked by business relevance

The first two experiences focus on exhaustiveness. We make sure that no matter what the user is looking for, they get all possible options (all sellers per game, or all games per seller). Another approach to building search is to be relevance-oriented: instead of showing all possibilities, you only show the best ones.

For example, if users search for “grand theft auto”, they may not need to see all classified ads for Grand Theft Auto V. Instead, you could only show them the ones from the most popular sellers.

In this case, we recommend that you split your data and use a flat structure.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

[
  {
    "game_title": "Grand Theft Auto V",
    "seller_name": "Alice",
    "seller_popularity": 1000,
    "seller_country": "France"
  },
  {
    "game_title": "Grand Theft Auto V",
    "seller_name": "Mass Effect Fan",
    "seller_popularity": 800,
    "seller_country": "USA"
  },
  {
    "game_title": "The Legend of Zelda: Ocarina of Time",
    "seller_name": "Alice",
    "seller_popularity": 1000,
    "seller_country": "France"
  },
  {
    "game_title": "Mass Effect 3",
    "seller_name": "Bob",
    "seller_popularity": 900,
    "seller_country": "USA"
  }
]

By breaking up games by seller, you can add more granular popularity attributes to each record. This way you ensure that the game with the best seller ranks better by leveraging the customRanking attribute. Please visit our how-to guide on ranking per custom attribute to learn how.

This structure involves data duplication, so we can use Algolia’s distinct feature to de-duplicate records on game_title. Please visit our how-to guide on indexing long documents to learn how.

Searchable attributes

This experience focuses on classified ads, so you may want to prioritize searching on game data. For example, when a user searches for “mass effect”, you may want ads for game Mass Effect 3 to show up before ads by user Mass Effect Fan. For this, you can create an order of priority in your searchableAttributes, by setting game_title first and seller_name second.

Did you find this page helpful?