Better shape in Firebird, taking safety and lightness

Question

I’m developing a software in Delphi, using Firebird. My goal is to have a lightweight, fast, but, software that has security, regarding errors, corruption in the bank, resist as far as given to Windows crashes, etc...

Considering data from a parent table TB_CAIXA, to insert data into a child table TB_VENDAS, to prevent the sale from being recorded when the status of CASHIER for CLOSED (F), do in the TB_VENDAS a Computedby field, checking the status of the current box, and also a CHECK, comparing this and, if it is OPEN (TO), let record. Looks efficient, works.

Maybe, use 6 fields Computedby only in the TB_VENDAS is a bad idea because you will have a lot of data.

I can do the same thing using a Trigger. Ok, if it fails, any errors, let go, there goes the incorrect data for the table. The goal is not to allow incorrect recording and, keep security and lightness in the application.

I would like to have some ways to go, to better understand if it is safe in this case, or mainly more efficient, use only one Trigger to validate this?

Answer 1

In general this is about business rule of its application. As to fault tolerance This is very relative, because there is flaws that it is better for the system to stop altogether than to continue working without some guarantees.

Although Firebird can handle ACID and MVCC, in some circumstances the system may go through serious "unpredictable" problems such as a lack of disk space, corrupted disk or environment power outage. These situations can severely harm your application. Each situation can be monitored and handled, but for absolutely everything there is a cost involved, it can be financial or processing, it is up to you to define how much you want to invest and how much each investment adds value to your software. For example, you could prevent your software from opening after a power outage until an analysis in the database determined if there was damage, but your users/customers could be without a system for how long? Are your users/customers willing to use protective measures with Nobreak to reduce the occurrence of this event? providing a safe and intelligent recovery is something you can "sell"? Do your competitors offer something similar that justifies you having something similar? Depending on your (and/or your users/clients) needs you might want to review some paid solutions, such as SQL Server or Oracle, for example.

It is worth noting that the operating system has little or no impact in relation to the problems that may occur with data storage or transaction consistency.

---

Returning to the business rule, you can opt for two distinct forms with advantages and disadvantages:

Business rules in the Database

You will have the full power of the database you want to use at your disposal, so you can work with instructions SQL (DML, DDL, DCL, TCL...) to form the rule the way you want. This includes implementing triggers, procedures and/or functions to solve virtually all your needs.

Upside

• Extremely secure as ACID and MVCC ensure consistency of all transactions;

Downside

• Absolute dependence on a given SGDB (in your case, Firebird). This may not be treated as a disadvantage, but it is important to be aware that a migration is quite laborious;

• Difficulty to update the production environment with the production environment. Some operations cannot be performed if there are connected users;

• Storage and processing cost for database server can be much higher;

• Depending on the complexity may require experienced people to deal with certain problems;

---

Business rules in the application (ORM)

Using a ORM solution you can put in your application everything you should put in the database, leaving this only to store the data already processed. This gives you greater power of control over information, such as recording audit logs before processing incoming data, for example.

Upside:

• You can allow your customers to choose (limited by your ORM solution) the database you want to use, including more than one simultaneously;

• You can turn on/off rules faster and more securely;

• Updating the rules is conditional on updating the application or module and can occur while all users/clients are connected;

• Rules can be modularized, distributed and updated as required by the environment;

• It is cheaper to climb multiple instances of application in multiple virtual machines or containers;

Downside

• Depending on the solution you adopt (or have created your own) can be very limiting in terms of implementation. For example, you might want to use a DB that is not yet supported;

• You may have difficulty using advanced features from some databases, such as Full Text Search or Json fields/documents, for example;

• Problems in ORM solutions are more common than in SGDB, and I believe this is linked to the cost of thorough engineering and testing. Note that for every update of an SGDB there is much more in ORM solutions;

---

Surely there are many more advantages and disadvantages in each of the methods. You can also merge the two solutions, such as creating certain triggers for certain jobs in the database and making the rules more complex and dynamic for the application.

Be very careful to allow the system to continue operating even after it has detected that there is a problem, especially in the data persistence layer.