What database structure to choose

Question

SQL

Hello, the question is the following.
During my medical practice life, I have met 2 internal organizations of the database structure for accounting programs.

1. Structure similar to 1C, where a separate document type corresponds to 2 or more separate tables (1 for document headers and one for each of the document bodies)

2. Structure similar to Abills, where it is less universal but (possibly) faster, in where all document headers are contained in one table (DOCS), + all document bodies are contained in another table (REESTR).

Which of these structures is more preferable for organizing an accounting program.

Answer 1

en.wikipedia.org/wiki/%D0%9D%D0%BE%D1%80%D0%BC%D0%B0%D0%BB%D1%8C%D0%BD%D0%B0%D1%8F_%D1 %84%D0%BE%D1%80%D0%BC%D0%B0 - read about normal forms and choose the structure that does not violate at least the third form.
It is better to store different entities in different tables. However, those 1C structures that I met are, in my opinion, very redundant. There is generally an EAV model - it's a complete ass.

Answer 2

First structure. It is both faster and more flexible.
You need to add an attribute to the document - it will add only to the one you need, and not to all - this is flexibility and space saving.
The data will be distributed across tables - this is the speed of sampling and insertion.

Answer 3

>>Which of these structures is more preferable for organizing an accounting program.
Accounting also implies the maintenance of some pre-aggregations. Stocks, account balances, etc.
In the case where the first structure is used, each type of document must have its own posting procedure for balances. As a result, it turns out to be quite problematic to recalculate all balances by documents or to identify which documents formed a certain current balance. In order to somehow unify the calculation of the remainder, to simplify tracking how the remainder is formed, you will have to sculpt another surrogate entity - wiring, for each of the cuts of the remainder. And this wiring will refer to different data structures, which is not very canonical, you cannot control the integrity by standard means.
For the case when there are few types of document structures (one, two, edge-three), and one structure can contain documents of different types, there is no such problem. Residuals for all measurements are easily confirmed and reproduced by lines of documents. However, there is redundancy. For the representation of some documents, it turns out to be impossible to fill in some fields, the interpretation of the field value often turns out to be dependent on the type of document saved, which is not even super good.
I have seen several systems where transactional documents are stored in three structures. 1 - Documents with external counterparties - purchase / sale documents 2) Internal documents - transfers between warehouses / zones within the organization, changes in the status of goods, write-offs of all kinds, etc. 3) Fiscal documents, that is, checks. This approach seems to me the most suitable for most of the tasks that I had to deal with.

Answer 4

And you can generally describe each column with 3 tables. data type, description of columns, and column values.
It will be mega universal, but as soon as you have to make a report, you will have to write a subquery for each column.
in this case, requests will be three-story.
This approach is in redmine for custom fields. I also saw it in some corporate applications of a different class.
See for yourself what is more important to you, flexibility without changing the structure of the database or simpler data processing in queries and reports.
Well, the logic of the code will be different.
And so different loading which the DB can hold.
And the database has restrictions on the number of columns and columns.
For myself, I would choose something in between and focus more on the logic of the code, which should be independent of the structure of the database.

Answer 5

Thanks for the answer
> For myself, I would choose something in between and focus more on the logic of the code, which should be
> Independent of the database structure.
In this case, the DBMS will be used almost like an excel book,
i.e. practically no stored procedures. well, the maximum recalculation of registers / accumulators or the output of the balance on the date, and then not always

Answer 6

Read third normal form. None of the described structures violates it.