Ideology and problems of developing financial systems. Part 2
Last time I tried to talk about some of the little things related to authorization and planning access rights, which are usually forgotten when planning the structure of the financial system.
This time I wanted to talk about the problem, the elimination of which in the later stages of designing the financial system will be the most expensive and labor-intensive.
Consider an example.
Suppose there are several tables in the database of your system. One of them is a directory of legal entities. As a developer / analyst, you need to design a functionality for working with some type of documents. For example, suppose you need to develop functionality for creating and editing payment orders. Suppose 1 payment order must have the following fields:
')
1. unique number
2. date and time of creation
3. date and time of payment
4. legal entity (counterparty) to which the payment was made
5. amount
6. type of payment
Immediately, I note that you almost never have to associate a unique document number with the id of the entry in the database table. The point here is this: users tend to be wrong. The logic of users is sometimes quite peculiar, for example, from time to time it is easier for them to remove an incorrectly created document and create it in a new way (especially for new employees who are afraid of “messing up”). After such actions, you will have a lot of empty “holes” in the system between document numbers. It would seem a trifle, but any test by “interested” persons will find in this secret intent. (If you want real examples, google "Prime-TASS sued the Moscow mayor's office." Their entire evidence base - document numbers went in order, but in public access there is only a part of them). It is best, in our opinion, to assign a number to a newly created document = maximum unique number in the system for this type of document + 1.
But back to our example. Pay attention to the field number 4 - a legal entity. Since we have a directory of legal entities, it is obvious to write in this field a link to the record in this directory.
Now imagine several possible situations:
1) Payment order was created in 2009. The payment was made by Bed. In January 2010, the company was renamed to Chairs LLC. It turns out that if we open the payment order form at the end of 2010, we will see that the payment was made to the company, which in 2009 did not physically exist.
2) Suppose in March 2010 there was a merger of OOO “Bed” and CJSC “Divany”, the result was OJSC “Sofas and beds”. What can users do? And they can rename the company “Bed” to OJSC “Sofas and beds”, and they can also rename ZAO “Divany” to “Sofas and beds”. The most interesting will start at the very first report, when it turns out that all payments between different legal entities (3 actual and 4 in the database) are mixed up and only people who made the payment (who, unfortunately, came under redundancy and for several months can distinguish them) do not work in your company).
Of course, all actions can be restored by history. Only you will be doing this, not the accounting staff (well, they will not poke around in the guts of your system, in the end they still have a lot of pieces of paper that need to be cleared; and, after all, who developed the system? Your department? Well so this is your problem. Accountants in general have to pay for work, and not to deal with systems).
And all this is fundamentally wrong:
a) you take responsibility for those actions that you have no right to perform at all;
b) there are situations that, in principle, can not be disassembled;
c) this is an obvious error in the system, the occurrence of which you have not predicted.
So, I went over to what I wanted to tell you.
Let's put forward several requirements:
1. All document data must be resistant to change. This does not mean that if the user wants to change the payee in the payment order, he will not be able to do it, but this means that if he renames the payee in the directory of legal entities, the old name will be reflected in the payment order.
2. Documents must withstand any logic described in the business process. In the example described, this means that if you need to sum up the amounts of all payment orders for all legal entities that have a different name, you will have the technical capability for this.
As far as my experience allows me (hello systems of the Moscow mayor’s office), I can say that almost everything is remembered about the first paragraph, but almost no one remembers paragraph 2. Those. this is usually implemented as follows: all fields of the document (usually all those fields that fall into the printed form of the document) are stored in the database table relating to this document, in text form. In our example, this means that in addition to the reference to the company, the table also stores the name of the company itself.
This of course eliminates some problems, but not all. And in general, this is ideologically incorrect: the link is stored to the company with one name, and another is displayed in the payment order (this is if the company was renamed).
We considered two options:
1. The directory of legal entities stores all changes. Those. when updating data in the directory, we do not just write the change history, but also physically do not update the modified string - we simply mark it as inactive. This creates a new line with the “current entry” flag. In this case, a reference to the parent record is created in the newly recorded line, i.e. to the previous version of this line. Also, when creating a new version of the line, the time of its creation is fixed.
Thus, knowing the date of creation of the document (in the example of the payment order) we can always find out what data on the counterparty were relevant at that time.
Moreover, since our record versions have different id in the table, it’s a pleasure to summarize (group / classify / divide) by them.
2. The directory of legal entities consists of two tables.
The first contains only those fields that characterize this entity and is called the main one. Usually, these are immutable fields of this entity (in the example with firms, the INN and the PPC pair should most likely come up). I note that it may well be that there will be zero such fields, i.e. the only immutable field is the id in the table, and this is absolutely normal.
The second one contains all the fields of this entity, as well as a link to the record in the parent table and the time when the new record was created.
Thus, all documents referring to a given legal entity will store one link (to an entry in the main table), but the version that is relevant for this document is easily restored by the time the document was created and in the “modification time” column of the entry in the subordinate table .
As a result, the second option was chosen. And that's why:
1. A general reference to the parent point leaves all the advantages of the first option (we can still easily divide all recording options into their temporary modifications and work with each separately), but working with all versions of the parent recording does not cause any difficulties.
2. MS SQL: since in the first case we have the following modification that refers to the previous one (ie, NULL <- ver1 <- ver2 <- ver3 <- ... <- verN), then
a) in order to view the entire history of changes, we will have to go around the circuit from the i-th node to the left and to the right, which will require some costs at the database level. And this is an atomic function that just returns information about changes to the record.
b) you will not be able to use version clustering (in the second case, it is very convenient to attach an index to a link to a record in the parent table). Thus, we are very cheap (in terms of the database) and will be able to quickly receive information on all modifications of the record.
3. The second option makes it very simple at the database level to prohibit changing the data contained in the main table. Thus, in case of an unsuccessful attempt to modify them, the user will at least think about the right way to solve his problem (in extreme cases, contact the person accompanying the program).
I think that's enough for the second part.
I am pleased to answer all your questions.
This time I wanted to talk about the problem, the elimination of which in the later stages of designing the financial system will be the most expensive and labor-intensive.
Consider an example.
Suppose there are several tables in the database of your system. One of them is a directory of legal entities. As a developer / analyst, you need to design a functionality for working with some type of documents. For example, suppose you need to develop functionality for creating and editing payment orders. Suppose 1 payment order must have the following fields:
')
1. unique number
2. date and time of creation
3. date and time of payment
4. legal entity (counterparty) to which the payment was made
5. amount
6. type of payment
Immediately, I note that you almost never have to associate a unique document number with the id of the entry in the database table. The point here is this: users tend to be wrong. The logic of users is sometimes quite peculiar, for example, from time to time it is easier for them to remove an incorrectly created document and create it in a new way (especially for new employees who are afraid of “messing up”). After such actions, you will have a lot of empty “holes” in the system between document numbers. It would seem a trifle, but any test by “interested” persons will find in this secret intent. (If you want real examples, google "Prime-TASS sued the Moscow mayor's office." Their entire evidence base - document numbers went in order, but in public access there is only a part of them). It is best, in our opinion, to assign a number to a newly created document = maximum unique number in the system for this type of document + 1.
But back to our example. Pay attention to the field number 4 - a legal entity. Since we have a directory of legal entities, it is obvious to write in this field a link to the record in this directory.
Now imagine several possible situations:
1) Payment order was created in 2009. The payment was made by Bed. In January 2010, the company was renamed to Chairs LLC. It turns out that if we open the payment order form at the end of 2010, we will see that the payment was made to the company, which in 2009 did not physically exist.
2) Suppose in March 2010 there was a merger of OOO “Bed” and CJSC “Divany”, the result was OJSC “Sofas and beds”. What can users do? And they can rename the company “Bed” to OJSC “Sofas and beds”, and they can also rename ZAO “Divany” to “Sofas and beds”. The most interesting will start at the very first report, when it turns out that all payments between different legal entities (3 actual and 4 in the database) are mixed up and only people who made the payment (who, unfortunately, came under redundancy and for several months can distinguish them) do not work in your company).
Of course, all actions can be restored by history. Only you will be doing this, not the accounting staff (well, they will not poke around in the guts of your system, in the end they still have a lot of pieces of paper that need to be cleared; and, after all, who developed the system? Your department? Well so this is your problem. Accountants in general have to pay for work, and not to deal with systems).
And all this is fundamentally wrong:
a) you take responsibility for those actions that you have no right to perform at all;
b) there are situations that, in principle, can not be disassembled;
c) this is an obvious error in the system, the occurrence of which you have not predicted.
So, I went over to what I wanted to tell you.
Designing entities related to documents.
Let's put forward several requirements:
1. All document data must be resistant to change. This does not mean that if the user wants to change the payee in the payment order, he will not be able to do it, but this means that if he renames the payee in the directory of legal entities, the old name will be reflected in the payment order.
2. Documents must withstand any logic described in the business process. In the example described, this means that if you need to sum up the amounts of all payment orders for all legal entities that have a different name, you will have the technical capability for this.
As far as my experience allows me (hello systems of the Moscow mayor’s office), I can say that almost everything is remembered about the first paragraph, but almost no one remembers paragraph 2. Those. this is usually implemented as follows: all fields of the document (usually all those fields that fall into the printed form of the document) are stored in the database table relating to this document, in text form. In our example, this means that in addition to the reference to the company, the table also stores the name of the company itself.
This of course eliminates some problems, but not all. And in general, this is ideologically incorrect: the link is stored to the company with one name, and another is displayed in the payment order (this is if the company was renamed).
How we solved this problem
We considered two options:
1. The directory of legal entities stores all changes. Those. when updating data in the directory, we do not just write the change history, but also physically do not update the modified string - we simply mark it as inactive. This creates a new line with the “current entry” flag. In this case, a reference to the parent record is created in the newly recorded line, i.e. to the previous version of this line. Also, when creating a new version of the line, the time of its creation is fixed.
Thus, knowing the date of creation of the document (in the example of the payment order) we can always find out what data on the counterparty were relevant at that time.
Moreover, since our record versions have different id in the table, it’s a pleasure to summarize (group / classify / divide) by them.
2. The directory of legal entities consists of two tables.
The first contains only those fields that characterize this entity and is called the main one. Usually, these are immutable fields of this entity (in the example with firms, the INN and the PPC pair should most likely come up). I note that it may well be that there will be zero such fields, i.e. the only immutable field is the id in the table, and this is absolutely normal.
The second one contains all the fields of this entity, as well as a link to the record in the parent table and the time when the new record was created.
Thus, all documents referring to a given legal entity will store one link (to an entry in the main table), but the version that is relevant for this document is easily restored by the time the document was created and in the “modification time” column of the entry in the subordinate table .
As a result, the second option was chosen. And that's why:
1. A general reference to the parent point leaves all the advantages of the first option (we can still easily divide all recording options into their temporary modifications and work with each separately), but working with all versions of the parent recording does not cause any difficulties.
2. MS SQL: since in the first case we have the following modification that refers to the previous one (ie, NULL <- ver1 <- ver2 <- ver3 <- ... <- verN), then
a) in order to view the entire history of changes, we will have to go around the circuit from the i-th node to the left and to the right, which will require some costs at the database level. And this is an atomic function that just returns information about changes to the record.
b) you will not be able to use version clustering (in the second case, it is very convenient to attach an index to a link to a record in the parent table). Thus, we are very cheap (in terms of the database) and will be able to quickly receive information on all modifications of the record.
3. The second option makes it very simple at the database level to prohibit changing the data contained in the main table. Thus, in case of an unsuccessful attempt to modify them, the user will at least think about the right way to solve his problem (in extreme cases, contact the person accompanying the program).
I think that's enough for the second part.
I am pleased to answer all your questions.
Source: https://habr.com/ru/post/109429/
All Articles