"Order" table in the database does not have the "adjustment" amounts?

[zgap111]

I was analysing Zero VAT transactions (calcs here will exclude VAT/Tax):

So
I can see from the "Order" table : "Total Cost" / "Shipping Total Cost" / "Order Total Cost"

"Order Total Cost" = "Total Cost" + "Shipping Total Cost" + "Adjustments" (if any)

"Adjustments" is not in the "Order" table (strange... why? I expected it to be in the "Order" table, with all the other amounts)

The only way I can find an adjustment amount to an order is to cross reference with "OrderDetail" table on the "OrderSequenceNumber" field and look for "ProductReference" = ":::::"
- sometimes we have more than 1 adjustment to an order... so a simple lookup is not sufficient... have to do a kind of LOOKUP order + SUMIF with MATCH
(or can always do in Power Query add a column with calculation : "Order Total Cost" less "Total Cost" less "Shipping Total Cost" : so if =0 = no adjustment, if >0 = adjustment)

Or is there another way?
(is it in another table? etc...)

[brucet]

Adjustments can't be in the Order table because there can be more than one per order. Wherever there is a one to many relationship you need separate tables.

[zgap111]

We can have 100's lines of goods with discounts (100's of discounts per line), and we have a "Total Cost" value and "Discount Cost" ...
so "Total Adjustments" is impossible?



This problem extends to the weight... would like to have a total weight too - this is also calculated via other tables...

Is this one of the reasons why the software is so slow?

When I load up an order, it's making these extra lookups/calculations.

eg. Regions table... doesn't exist... why?... Country table = exists. To find the region id... it's difficult.
eg. Tariff and Country of Origin isn't in the "Product" table, but is looked up from the "Variable" and "UserDefinedProperties" tables

Feels like the software is constantly busy with these tasks