Is the database architecture and approach to solving the problem chosen correctly?

Question

Database design

Hello!

There is an application in which the entity "Application" is present. This entity, when it enters the database, has the status Created. Also possible statuses are Received (If the Application has not yet expired), Active (The validity period has come and has not yet expired) and Closed (If the validity period has expired). All Applications are stored in one table “zayavka”. Once a minute, a selection is made from the database (read from the "zayavky" table) of Applications in the statuses Created, Received, Valid and checking the validity period to transfer to the next status. If the Application has expired, then the Application receives the Closed status and, in fact, is no longer used, it lies in the database in case someone wants to look at it. The application has links in both directions to other tables - such as activity logs, users, etc.

It turns out that the "zayavka" table accumulates applications in the Closed status and every minute in order to select Applications in the statuses requiring processing, you have to shovel the entire table - and when the table is inflated - it may turn out that a minute will not be enough for the selection. This architecture does not seem optimal to me, and I think it makes sense to move Orders in the Closed status to another table, thus reducing the polling time for the "zayavka" table. In general, what would be the right thing to do in this case and at what number of records in the table we should expect problems.

Thanks for reading!

Answer 1

The structure is quite viable, if used correctly.
In order not to "Shovel the entire table", in which after a while there will be many "Closed" requests, you need to add an index on the "Status" field.
Thus, when executing a select query, if you apply a filter on this column, extra values ​​will be cut off (reading will first take place by index, and then rows from the table that match the selection criteria).