How to limit the number of relationships per entry?

Question

MySQL

Hello. I need to create a one-to-N relationship between tables. I'm writing a test site where you can book a table in a restaurant. I have a problem when working with tables:
restaurant
reserved
The restaurant stores the description of the restaurant, as well as the number of tables. When a user makes a reservation, an entry is created in reserved, with a link to the restaurant id, but it turns out that you can create any number of reservations.
I wanted to make a request to the database to get the maximum number of tables, as well as get the available reservation in order to calculate whether there is enough space, but then if my program is slow, it may turn out that there are no more places, and I add another reservation . I would like to shift this question to the database.

Answer 1

Read about transactions.

Answer 2

Actually a very interesting question. The logic that you are offered above will still have to be used. Those. we start a transaction, we try to stick the data, if something went wrong, we roll back the transaction. But, as far as I understand you, first of all, you don’t like that you will have to put restrictions on the number of places in the code, and roll back the transaction from the code too. This is a normal practice, but if you really want to put restrictions into the database, look towards CONSTRAINT. True, this thing is only in the latest version of MySQL (PostgreSQL has had it for a long time). With these checks, you can, for example, create a column with the number of empty seats in a restaurant and require that it be non-negative. Then at the code level, you will only need to remember to twist the free space counter by one. If the constraint is violated, the database will throw an error and the transaction will be rolled back.

Answer 3

UNIQUE Constraint .

Answer 4

How exactly does a user book a table? Most likely for a certain time, that is, for example, from 15 to 18. At the same time, for sure, there is some booking step (for example, 1 hour). Accordingly, slots appear at 15, 16, 17 hours.
This means that the table id and the time slot of the reservation are written to the table. A unique key is created (table_id, slot). The recording of all slots is done within one transaction, then no one will be able to break into the procedure for saving the reservation.