Task: keep track of the movement of something. For example, bunkers.

  1. Bins move between different objects.

  2. It is important to be able to receive the following reports:

    A) In one line the object "outgoing" and the object "destination". In the example in the picture, the movement of bunkers between seaports.
    Sample Report

    here ports and ships are objects
    B) Report on the number of bunkers at the moment on each of the objects
    B) report on the number of objects on each type of object (our port, ship, port of the customer)

One could use such a table design: +---------------+ | Field | +---------------+ | id | | object_out_id | | object_in_id | | qty | +---------------+ +---------------+ | Field | +---------------+ | id | | object_out_id | | object_in_id | | qty | +---------------+
.. then report A) is pulled out of the database by ORM means by a simple FLow.Objects.all() request, and for the rest of the reports, it would be necessary to use not the simplest SQL queries with which ORM cannot cope.

The second option is to record one movement of the bunker in two lines in the database. +----------------+ | Field | +----------------+ | id | | transaction_id | | object_id | | qty | +----------------+

Then ORM will cope with the majority of requests, everything works quickly, BUT - report A) as in the figure you cannot pull out with a simple request ...
I want to serialize it in JSON using the Django REST Framework.
RAW requests in DRF do not support pagination.

Advise which data storage model to choose, so that there would be no problems with outputting report A in JSON and for large amounts of data there would not be too long processing for other requests?

    1 answer 1

    I would do so ...
    If the types of objects are known in advance and unchanged, then in the Object table the type_id field type_id better to do with choices . If not, then the table will do:

    Type - object types

    id
    name

    Object - objects

    id
    name
    type_id
    count - the number of objects at the moment

    Transaction - transactions

    id
    date
    object_id_1
    object_id_2
    count

    When recording (changing, deleting) transactions, increase / decrease count in related objects.
    All the queries you need are trivial.
    (table names are for example purposes only; in Django, you may have to use others)

    • Thanks for your reply! Tell me, in the proposed table "Object - objects" count - the number of objects at the moment where? I mean how many "bunkers" are located on this object at the moment? - Nikita Bel
    • Yes exactly. - Evg
    • Just one more thing - you probably have a regulation, according to which transaction management takes place. That is, transactions must have another field - status. It will help to understand - where are the bunkers that are now "loaded / unloaded." After all, in theory, bunkers from objects should already be written off after the start of the transaction, and recorded on the recipient object only after its completion. - Evg
    • It may be even more correct to use the datetime fields for this purpose: datetime_start (instead of date) and datetime_end. Then, if you select from the transaction table entries with datetime_end = None, you can get the number of bunkers that are now "between" objects. - Evg
    • to take into account the state "between" objects, we introduced transport as objects themselves. those. in the port example, these are boats. there is practically no time spent between one object and the second. - Nikita Bel