Storing and querying PostgreSQL database entities with multiple related entities?

Question

Designing a PostgreSQL database that will be queried by a Node API using Sequelize. Currently, I have a table called recipes that has columns called ingredients and instructions. Those columns are stored for a given as an array of strings like {Tomatoes, Onions}.

That method of storage worked fine for simply fetching and rendering a recipe on the client side. But it wasn't working well for fuzzy search querying because, using Sequelize all I could do was ingredients: { [Op.contains] : [query] }. So if a user typed tomatoes there was no way to write a "fuzzy" search query that would return a recipe with an ingredient Tomatoes.

And then I read this in the PostgreSQL documentation:

Arrays are not sets; searching for specific array elements can be a sign of database misdesign. Consider using a separate table with a row for each item that would be an array element. This will be easier to search, and is likely to scale better for a large number of elements.

Now I'm considering storing ingredients and instructions as separate tables, but I have a couple of questions.

1) As a recipe can have multiple ingredients related to it, should I just use a foreign key for each ingredient and the Sequelize hasMany relationship? That seems correct to me, except that I'm now potentially duplicating common ingredients each time a new recipe is created that uses that ingredient.

2) What would be the best way to write a fuzzy search query so that a user could search the main columns of the recipes table (e.g. title, description) and additionally apply their query to the instructions and ingredients tables?

Essentially I'd like to end up with a fuzzy search query applied to the three tables that looks something like this...

        const recipes = await req.context.models.Recipe.findAll({
        where: {
          [Op.or]: [
            { title: { [Op.iLike]: '%' + query + '%' } },
            { description: { [Op.iLike]: '%' + query + '%' } },
            { ingredients: { ingredient: { [Op.iLike]: '%' + query + '%' } } },
            { instructions: { instruction: { [Op.iLike]: '%' + query + '%' } } }
          ]
        }
      });

Thanks!

Answer 1

I have done this, i happen to use graphql in my node layer with sequelize, and i have filter objects that do this type of thing. You'll just need some include statements in your Recipie.findAll.. after your initial where clause where you evaluate whether you are searching title or description or both type thing. i sent my search params in with prefix's i could strip off that told me what sequelize op's i would want to use on them and just ran my args through a utility method to create my where clause, but i know there are many ways to skin that cat. i just did not want to clutter up my resolvers with tonnes of hardcoded ops and conditional clauses was all.... your include might look something like this

    include: [{
              model: models.Ingredient,
              as: 'Ingredients',
              through: { some join table specifying keys where necessary since this 
                         is many to many }
              where: {some conditional code around your search param},
            }, {
              model: models.Instruction,
              as: 'Instructions',
              where: {some conditional code around your search param},
            }],

There is good documentation around multiple includes, or nested includes in the sequelize docs, but from what i see above you have a fairly good understanding of what you need to do. To uncomplicate things a bit, i'd start with just searching on your fields from recipie (title, description) before you add the includes and get that working, then it will be a little clearer how you want to form your where clauses.

alternativley.. you can skip the includes and write associations in your models and call them with getters and pass the where clauses to those... i do that as well and again well documented stuff now.. Sequelize has really upped their game

Recipie.associate = function (models) {
    models.Recipie.hasMany(models.Ingredient, { as: 'Ingredients', through: "recipie_ingredient" foreignKey: 'recipie_id'});
  };

now you have a getter for Ingredients, and if you declare belongsToMany targetting back at Recipie in the Ingredient model then you'll have a getter there as well, and you can pass your search string to that via where clause and get all recipies that have a given ingredient or ingredient list type thing.... Clear as mud?