I need to establish relationships between tables correctly.

There are Orders, Invoices and Users tables.

For example, the Orders table consists of fields (Id, startDate, finalDate, price).

For example, the Invoices table consists of fields (Id, startDate, finalDate, totalPrice).

For example, the Users table consists of fields (Id, username, email, password).

* The fields of the tables above do not contain coherent fields.

Links (for the time being, I see connections like this)

db.users.hasMany(db.invoices); db.users.hasMany(db.orders); // db.orders.belongsTo(db.users); db.orders.belongsTo(db.invoices); // db.invoices.belongsTo(db.users); db.invoices.hasMany(db.orders);

When an order is made, OrderController does not know about the final invoices, it simply adds a new order to the user.

When an invoice request (list) is made, InvoiceController gets the last invoice from the database, checks the dates, if the old invoice creates a new one from the orders for the current month. A request is made for orders related to Users for the selected dates. And the total amount of bills is added to the totalPrice field.

If the account of the current month, then a request is made for orders related to Users for the selected dates. And the total amount of bills is updated in the totalPrice field.

And then a request is made for all accounts associated with the Users (list).

If the user wants to see one account, a request is made for orders related to Users for the selected dates and a second request to Invoices to receive account data. Formed by json and sent to the client.

Something I do not understand how best to make the relationship between the tables based on this logic.

    1 answer 1

    You must configure the entity link Invoice to Orders. Based on your business logic, I would do this for Invoice:

      HasMany(o => o.Orders).WithOptional(i => i.Invoice);

    Thus, a single account will be created for many orders. At the same time, the presence of a connection is not necessary for both entities. Logically, when requesting an invoice, the table of orders is checked, if there are non-related invoices, add links. And of course, make sure that there are navigation properties in custom entities.

    • The fact that when requesting an account, new orders are checked, the connection is understandable. And when creating an order, how is the connection to this account established? - Sergei R
    • See above. I wrote to you, the connection is not mandatory. The order can live in the database, both related and unrelated, the code of verification and selection of the appropriate orders for the condition remains on the programmer’s conscience. Thus, the requirements specified by you in the task are fulfilled. - Ieshua
    • And why then do you need connections here? I do not understand you enough. When linking one invoice to many orders, it is assumed that the Orders table will contain the invoiceId field. This means that when creating an order I need to first find out the account id create it or learn it from the Invoices table. It turns out each time creating an account, we turn to the Invoices and Orders table and to form the invoice to the Orders and Invoices table. If there are no connections, when I create an order, I only turn to the Orders table, and when I create an invoice, I use the Orders and Invoices tables. It turns out without connections of movements with base less. - Sergei R
    • First, communication is needed to make it easier to interact with entities. Different selections on the basis of relationships, etc. Secondly, relations describe the requirements of business logic, this is such a thing that should work at the database level and application code, and work perfectly, without any errors there. It is a given that must always be kept in mind. - Ieshua
    • Now, according to your understanding of EF. No Id account and order you do not need to know These are purely navigational fields, in their essence. They should be generated by the base automatically when creating a record. Second, you are completely in vain fixated on appeals to tables. At the start, where you are, think about it early. It makes sense to think when running out performance. Forget about it, most of the projects of small / medium level of EF performance are enough. - Ieshua