What is the best way to build a Mysql table with recipe items? All ingredients in one row or one ingredient per row?

Question

I’m used to creating websites with few database records, so I don’t care about the speed of query and I have no experience with large volumes of information.

But now I am creating a website recipes, with the goal of having more than 100 thousand recipes and I have a doubt in the structure of the MYSQL table in the ingredients part.

How to assemble to make the search faster?

Think of 2 ways to assemble the ingredient table:

Forma 1: 
id|idreceita|listadeingredientes
1 |   701   |1/2 de feijão;sal a gosto;2 folhas de louro;pimenta a gosto;2,5 litros agua

Forma 2
id|idreceita|ingrediente
1 |   701   |1/2 de feijao
2 |   701   |sal a gosto
3 |   701   |2 folhas de louro
4 |   701   |pimenta a gosto
5 |   701   |2,5 listros de agua

The search would be done with:

SELECT FROM tabela_ingredientes WHERE (listadeingredientes ou ingrediente) LIKE '%louro%'

My question: it is better to have a table with 100,000 records, where the field of ingredients will be large (on average 200 characters) or a table with 800,000 records where the field ingredients will be smaller, will have on average 25 characters

Answer 1

One hundred thousand seems an almost derisory amount and doesn’t change anything in what you’re doing, so don’t worry. Having 200 characters is also derisory.

Having fewer tables always makes it faster if you know what you’re doing. Not knowing anything can be a problem.

All data modeling should be done based on existing and well-defined actual requirements. Have you done this? Only you know them and so only you can answer the question properly.

Is there a reason to keep these items in another table? Is there a combination? Individual access to the item to access revenue through the item? It does not appear to be a description without specific formatting and without much discretion. Even if you are going to do a search for the text changes little if it is all in a separate table. Being just a description, I may have misinterpreted the example given, so just create a column to store the ingredients.

To separate you have to have a justification, you have to have a reason that makes you need separate data. You have?

In the comments (read below to get the context) I put that it is possible to do this by separating the quantity from the description of the item, but this only goes if it is to do normalization complete, ie if you use a id and then have control that the description of the ingredient is always the same, because if you let the person write the name of the ingredient in each recipe will not have standardization, then you will have little or no advantage, if you have no disadvantage, which I think you will have. And this would only be useful for a much more complex control, which does not seem to be the case with AP.

The use of LIKE is inefficient in both cases, should seek a solution of FTS.

Answer 2

I see at least three steps to making the ideal solution:

1. Solve the problem;
2. Make the solution beautiful;
3. Make the solution fast;

It seems you are starting with item 3, worrying if the solution will be quick without even having a solution to the problem.

In the comments you placed a requirement of utmost importance to the problem:

"yes, because there will be a specific search for ingredients, for example, there are peppers left in the ice cream parlor, what kind of recipe can I make with chili" [sic] (source)

This indicates that from a particular ingredient you need to locate the recipes. More than that, you will have to search not only for ingredient, but for quantity as well. If the idea is to allow a search of recipes that take advantage of the available ingredients, than it would be helpful to bring a recipe that requires two peppers if I only have one?

There is also the problem of normalizing the name of the ingredient. I search for "chili", but there are recipes registered with "peppers", "peppers", "pitentoes", "pimentao", "green peppers", "red peppers", "chili" etc. This would be a serious problem in the task of bringing a result of a faithful search.

You get around this problem by creating a table to manage the ingredients:

create table ingredientes (
  ingrediente_id integer not null auto_increment,
  ingrediente_nome varchar(255) not null,
  primary key (ingrediente_id)
);

That’s in the most basic form. You can increment the table according to your other needs; for example, you can create a column that signals if a given ingredient has gluten and thereby already classify a recipe as gluten-free, can add columns with nutritional information of the ingredient and with this estimate nutritional values of the recipe (calories, sodium, etc.), can create a column that signals if a given ingredient is of animal origin and with this classify a recipe as vegan. The possibilities are many.

For recipes, you will create another table:

create table receitas (
  receita_id integer not null auto_increment,
  receita_titulo varchar(255) not null,
  primary key (receita_id)
);

That’s in the most basic form. You can increment the table according to your other needs; for example, you can create a column that informs the time of preparation of the recipe (unlikely, because perhaps it would be better to define the time of each step of the preparation mode instead), you can define a column that defines for which meals the recipe is best suited (lunch, snack, dinner, etc).

And finally, as a recipe can have more than one ingredient and one ingredient can be in more than one recipe, we have a clear relation of N:N, requiring a table of association between ingredientes and receitas. This table must have the two foreign keys that will associate the ingredient to the recipe and other columns with information from the association. For example, recipe 1, stuffed chili, has ingredient 1, green pepper. But how many? That is, the ratio table will need to define the amount of ingredient in the recipe. The ratio of recipe 1 to ingredient 1 will amount to 3. But 3 what? Units, cups, spoons? In this case it would probably be units. Thus, the ratio, in addition to defining the quantity, should define the unit of measurement. If you put as a text column we return to the initial problem: there will be no normalization in the values. One recipe can have "1 tablespoon of salt", another "1 tablespoon of salt", another "1 tablespoon of salt", etc.

I think if you’re following the answer, you’ve figured out the next step:

create table medidas (
  medida_id integer not null auto_increment,
  medida_nome_singular varchar(255) not null,
  medida_nome_plural varchar(255),
  primary key (medida_id)
);

And, in the table of relation we will have one more relation: foreign key to medidas.

create table receitas_ingredientes (
  receita_ingrediente_id integer not null auto_increment,
  receita_id integer not null,
  ingrediente_id integer not null,
  receita_ingrediente_quantidade float not null,
  medida_id integer not null,
  primary key (receita_ingrediente_id),
  foreign key (receita_id) references receitas(receita_id),
  foreign key (ingrediente_id) references ingredientes(ingrediente_id),
  foreign key (medida_id) references medidas(medida_id)
);

That’s in the most basic form. You can increment the table according to your other needs; for example, you can create a column that defines a multiplication factor for different amounts of portions. If my recipe is to use 1 pepper that yields 1 portion, if I wanted to return for two portions the quantity 2, it would be enough to define the factor as 1.0. But maybe I don’t need to double the amount of salt in this case too, so the factor could be 0.65, so the amount for two portions would be 1.3x the amount for one. This would allow your recipes to stay dynamic as your user needs.

I have a green pepper at home, what I can make for lunch?

select receitas.*
from receitas_ingredientes
join receitas using (receita_id)
where ingrediente_id = 1  # Pimentão verde
and receita_ingrediente_quantidade <= 1  # Quantidade 1
and medida_id = 1;  # Unidade de medida: unidade

Is it efficient? I don’t know, but it solves your problem. Step 1 completed. Now you can review all the requirements of your project (I believe you’ve already done this before you start structuring the database) and with this you can make your solution beautiful. You can create all the columns you need, create functions, procedures, etc. Step 2 completed. Test the system, are you fast? Great, you got a beautiful solution to be fast enough - in most cases, when steps 1 and 2 are well executed, step 3 can be ignored.

Not fast? Review steps 1 and 2. Maybe the solution you implemented was not the best. Identify the bottlenecks, see if you can implement otherwise; if so, reprint and test again. If your problem is really so specific that even doing very well steps 1 and 2 your solution still does not meet the performance requirements you can analyze how to bypass them. You can create views, materialized views, create read-only structures, cache layers, etc. It’s hard to tell which solution will be without knowing the bottlenecks. That only you can say - if it’s really necessary.

As Maniero said in his reply, a database can work with millions of records. A few thousand shouldn’t be a problem. If it is, go back to step 1. As a matter of curiosity, here are some 15 tables that go from 300,000 records, five tables that go from 1 million records (the largest goes up to 15.5 million) and so far we have had no problem with performance.

Answer 3

The way you put it doesn’t make much difference, but separating the ingredients in another table can allow searching for ingredient considering the quantity, but in the table structure would have the column nome and quantidade:

id|idreceita|quantidade|nome
1 |   701   |1/2       | feijao
2 |   701   |a gosto   | sal 
3 |   701   |2         | folhas de louro
4 |   701   |a gosto   | pimenta 
5 |   701   |2,5 l     | agua

But note that the quantity is not something so simple to handle, there are liters, milliliters, cups, spoons, kilograms, grams, units it would be interesting to convert all items to a standard, for example, all liquids use liters and all solids use grams

If you think it’s worth the trbalho will have a more exact search, because the person wants to do something with her chili, but she only has 1, if there are recipes with 2 or more does not help you