Typical billing scheme

As a person, I was exhausted by various ASRs and I myself took part in the development of ASR regularly faced with the absence of some typical scheme that could be looked at to evaluate the ASR, as well as before creating my own ASR. There are a number of works on the network on this topic. For example, I once studied this diploma while writing a diploma. Methods of modeling and developing billing systems . A diploma is a diploma and it is a strange task to drag out schemes from it, since it does not respond to the realities.

As a result, now having quite a lot of experience with ASR, I decided to make my own scheme. But since I am still one person, then it should be shown to others and criticized. So I hope they will be interested in this topic and they will tell me what else to do and how. The scheme that I publish here has already been improved and corrected, and it is already possible to download it from github . Both as a file for Power Architect and as a ready-made DDL file for PostgreSQL. The only thing I did not have time to fill out reference books, but everything has its time. Now go to the scheme.

The first step is to look at the ER-diagram, as the most convenient means of representation of the scheme.



')
As you can see, although there are quite a lot of tables, in fact, the functionality is quite small.
And consists of the following features:

• Storage of the client’s contract and its balance sheet
• Storage of services and their cost management
• Charges for the services provided
• Discounts
• Making payments
• Money transfer from contract to contract
• Entering customer balances when transferring them from another system
• Storage of billed customer
• Redemption of invoices

It is a necessary minimum for correct carrying out charges for services and accounting of funds.
But before proceeding to the description, it is worth mentioning the agreements used in the scheme:

• All foreign keys have the format <primary key> _ <table name> . If there are several foreign keys or they point to the table itself, it is allowed either addition to the name of the form id_ <table name> _ <explanatory addition> or id_ <explanatory addition> . As an example, id_trx_from, id_trx_to for the first case and id_revoke , id_revokedby for the second case in the bill.transfer table.
  
• Fields with money are defined as numeric (18,4).
• Fields with a date have the prefix dt without fail.
• Fields with date and time are prefixed with ts.
• If there is a time interval (dtfrom, dtto or tsfrom, tsto), then the first date is always set and defaults to now (), the second date can be empty and in this case the interval is considered valid at the moment.
• In some cases, reference books use a text mnemonic key instead of a numeric primary key. Such keys are denoted as sid. Used exclusively for convenience when working with data directly through the RDBMS console.

And now the description slides .

Reference tables are:

• Contracts (bill.contract) - the minimum required to use description of the contract
• Postings (bill.trx) - Log of transactions. In fact, the amount of money has come or gone from the account.
• Used accounting side (bill.ledgertype) - indicates where the posting is going (debit, credit)
• Reporting periods (bill.period) - reports in the accounting sense of the word. Although it contains the start date and the end date, in fact it is always equal to the month
• The invoices issued to the client (bill.invoice) are the same invoices that are issued to the client during the reporting period.

Although these tables are quite enough for billing to work, but for convenience, tables of primary documents are added. The primary documents are those documents on the basis of which the entries are made to under the client's contract.

Now in the scheme there are the following primary documents:

• Balances (bill.remain) - incoming balances from another system. These documents must always be available, otherwise if you are migrating from another system, you will never know what the client balance was at the time of the migration. By the way, many ASRs suffer from this, as the developers believe that it is enough to introduce a balance. But this is not the case, as in the normal system the client’s balance is a calculated value.
• Payments (bill.payment) - incoming cash from customers.
• Transfers (bill.transfer) - transfer of funds from one account to another. I note right away that it is worth explicitly prohibiting the transfer of funds in their absence. Those. if the client’s balance is negative, the transfer should be prohibited.
• Charges (bill.charge) - charges for consumed or provided (in advance) services
• Discounts (bill.discount) - Discounts for services. Although in general there is no consensus on how to express a discount. I believe that the discount should be expressed in monetary terms to a specific charge. It simplifies working with her.
• VAT (bill.vat) - VAT from the accrual, is issued a separate document. All transactions from accruals are without VAT, while the accrual itself may include VAT. This is for example required for individuals. In this case, bill.charge has an explicit flag that the charge includes VAT. In this case, the accrual posting does not include VAT and the full amount of the accrual is made up of two postings, the posting on the original document is the accrual + posting on VAT.

Primary documents have both general fields and general rules for working with them. Let's start with the general fields:

• id_contract (id_contract_from, id_contract_to) - indicates the contract or document contract
• id_trx (id_trx_from, id_trx_to) - indicates the posting or posting of the document
• id_period - during which reporting period the document is posted.
• ts - document date
• tscreate is the date the document was created.
• amount - document amount
• id_revoke is a corrected document. Indicates the document that is being corrected by this.
• id_revokedby - corrective document. Indicates the document that has corrected the current one.

As for the reporting period and the corrected and correctional document, I’ll stop by more detail.

Reporting period.
At the end of the month, you may have two open reporting periods due to the fact that, for example, the last days fall on a weekend, and the payments to the bank have not yet been posted. Plus, this is required in cases such as when the actual document date is January 2014, and the period used is June 2015, held in early July 2015. That is, in the period June 2015, in the creation date July 2, 2015, and in the document date January 2014. Because of this, the document actually has three dates.

Corrected and corrective documents.
In fact, if you have a document in your system that is in error, you cannot delete it. It can only be canceled. Those. create a corrective document which is strictly opposite to the incorrect document. For this purpose, these two fields are used. id_revoke - filled in the correction document, and id_revokedby in the corrected one. Otherwise, for example, a part of the document is not recommended, as well as deleting documents. Instead, hide such documents, if they are in the same period. If the documents are in different periods, then you don’t need to hide them. Also note that the wiring of such fields is not, they are not adjustable.

In addition to general fields, primary documents have their own specific fields:

• Charging documents (bill.charge) - count and vatincluded . The first indicates the number of services provided, the second does not include VAT in the amount of the document.
• VAT documents (bill.vat) - id_charge and id_vatrate indicating the document of charges to which the VAT applies and what percentage of VAT was at the time of the document of charges.
• Discount documents (bill.discount) - id_charge indicating the charge applied.
• Residual documents (bill.remain) - sid_ledgertype that allows you to specify the accounting side to use when conducting a document.

Remaining fields I think are clear from a context and the selected names. This concludes the conversation about primary documents and go to the remaining reference tables.

The schema contains the following reference tables:

• Payment types (bill.paymenttype) - for dividing payments by types. Cash, cashless payment, payment agents, etc.
• Units of measurement (bill.unit) - units of measurement of services used. For example, you can take from the directory OKEI. Self reference is used for units that include others.
• Services (bill.service) - the name of the services provided under the contract.
• Prices (bill.price) - Cost of services. Time intervals have been added to account for changes in value.
• Posting type (bill.trxtype) - Indicates the type of transaction used for primary documents, as well as the default accounting side. If the default party is not specified, the selection takes place when the document is posted. For example, these are leftover documents.

As well as two auxiliary tables:

• Balance history (bill.balance) - the history of the change in the balance of the contract with reference to the postings.
• Turnover (Balance) for the reporting period (bill.saldo) - a table with aggregated data on turnover related to the period. It is often used in various analytics.

And that is all. If you do not mind to answer the survey. The results will be taken into account when writing the next post in the series :) Well, ask your questions in the comments.