Solving the nested tables problem in relational databases

Question

I'm coming from a non-SQL database (MongoDB), and I'm building up a new project, but with MySQL (a relational Database).

I'm making a restaurant system, and in this system, there's a page where you can create a product and describe its ingredients.

In MongoDB, (non-SQL database), you can make this schema for your document, (I'll describe it with JSON just to give the point):

// imagine this is a document of a product stored in the products collection
{
  name: "Burger",
  price: 200,
  ingredients: [
   { name: 'tomato', qty: 2 }, // qty: ie quantity
   { name: 'cheese', qty: 1 },
   { name: 'bread', qty: 2 },
   { name: 'meat', qty: 1 },
  ]
}

In an SQL relational database, however, you'll have to create something similar to this:

At a first glance, it looks ugly, but after thinking for a bit, now I'm starting to get used to it.

Firstly, I want to ask a question regarding this approach:

• Is it the way to go for this problem?

I'm sure you have come across this scenario hundreds of times.

Second, I have another question:

Now the user have pressed the "create" button to create his product, and he sent the request to the server to process this request,

How am I going to insert these rows into the database?!

Is it by doing a for loop and sending 5 requests to the database:

1. the first request, is to create the row with the ID#1 in the product-ingredients table.
2. the second request, is to create the row with the ID#2 in the product-ingredients table.
3. the third request, is to create the row with the ID#3 in the product-ingredients table.
4. the fourth request, is to create the row with the ID#4 in the product-ingredients table.
5. the fifth and final request, is to create the row with the ID#1 in the products table.

This looks crazy, a little bit.

That's what my brain is saying.

It's why I'm particularly asking this question.

Later, there will an update product page, and the similar for loop with a bunch of requests is going to be executed on the database.

The question now is HOW.

I appreciate everyone who's going to take my brain hand into the world of relational databases. 🌹

Answer 1

In addition to what the others said, to help fill some gaps:

...sometimes say the ingredients are 30 ingredients, am I going to send 30 requests to the database just to create this product?

Like Akina said, it depends, but if none of the 30 ingredients exist yet, then yes one way is to execute a separate INSERT statement for each ingredient. But depending on how your application is architected, you could batch those INSERT statements together in the same batch so only one request is needed to be made to the database at a time. It is good practice to not make too many extra unnecessary requests to the database, when reasonably possible.

Furthermore, in some database systems, you can use a BULK INSERT method when the number of INSERT statements is a lot. That will not only INSERT all of the ingredients in one request, but also in a single statement, leveraging the most optimal way to INSERT into the table.

Ok, how much does the 30 requests going to take in practice? Like how many seconds...

Akina mentioned a few milliseconds for a single INSERT statement, but even that is an overestimate. 30 ingredients, etc, is a tiny amount of data. It shouldn't take more than a millisecond, and really probably nanoseconds for a single INSERT statement to execute. So yes, INSERTing into the table should be very fast, for the amount of data you're talking about.

if I'm inserting 30 rows, one by one. What's the guarantee that the 30 rows are going to be created successfully.

That's what transactions are for. They ensure every data manipulation statement (DML) wrapped inside of them are executed atomically - either all or nothing. If one statement errors out, they all rollback. Otherwise they all succeed together.

omg, this is too complex, and what if the rollback operation itself failed

Not possible. Once a rollback is issued (either automatically by the database system depending on the error, or manually instructed by your code) it must complete. The database system has a log that it uses to ensure atomicity and consistency (which are part of the ACID principles of a database system that is ACID compliant - most RDBMS). A rollback command is noted in the log immediately, so if anything happened such as the database server crashing, it would know where it left off when the server is back online so it can resume reconciling back to a valid consistent state of the database.

Answer 2

? Is it the way to go for this problem?

Short answer - Yes.

How am I going to insert these rows into the database?!

Insert the product first.
Then retrieve the ID automatically assigned to that newly created product row.
Then loop through the Ingredients, inserting each in turn, using the new product ID in each one.

update product page ... The question now is HOW.

It depends on the change.
Changing a single Ingredient within a product might need a single update. More "side-scale" changes might be easier done by trashing all the ingredients and inserting them anew. YMMV.

Answer 3

Is it by doing a for loop and sending 5 requests to the database

No. Backward.

1. Firstly, you create a row in product table. On the client side.
2. Then you check ingredients for it, and if some ingredient not exists yet - then you create a row in ingredients table. Each new row is saved immediately.
3. Then you link them - you create rows for product-ingredients table, one row per one ingredient for this product.
4. Finally, you save your work.

In practice - each row created on the step 2 is saved immediately and separately, you need a query per ingredient. Of course you can collect this data and save all newly created ingredients in one query, but this makes no sense in interactive mode. The rows created on the steps 1 and 3 are saved on the step 4. This needs in 3 queries. 1st query saves the product, 2nd query retrieves its udentifier, 3rd saves all relational rows.