Examples in the documentation are not "real life" enough

[gsvigruha]

Usually the user requirement would require more drilling down e.g. per year: https://github.com/gsvigruha/cosyan/blame/9af2a81e807c626cda84e37b77247be0c994026c/src/main/resources/doc/rules/32_reverse_foreign_keys.md#L118

[jonathanvx]

The example of the 'sum of product truck' might not be useful to end users. The usual requirements for these sorts of aggregation usually come with 'drilling down' to the product sum and year, quarter, month.. as well as product sum and location that it was sold.

[gsvigruha]

Yeah that's a good point. I'm not sure what is a good way to do it with the current version. One way is to use the year as a foreign key in another table and define a constraint there. But even that way we can only use one field, and we would need multiple (year/location/product_type).

[gsvigruha]

I gave it some thought. I think we will need multi column indexes anyway. Once we have this it will be fairly easy to add a way to create aggregated views (which are useful on their won too) and define constraints on them, like:

create view transaction_by_year_loc (product.name, year, location, select sum(amount) as amount from transactions group by product.name, year, location);
alter view transaction_by_year_loc add constraint c_amount check(amount <= 1000);

[jonathanvx]

Technically speaking, you have two 'business specific' ways of going about this:

1. You have an intermediate summary table that aggregates data by product/day/location
2. In the product table, you can have the concept of 'last time period'. So for example, a table with product and then how many were sold in the last month, last quarter, last year. This is so that the business user would get more relevant information immediately, but still won't have trends over time.

[gsvigruha]

I think I prefer 1, it's more comprehensive. From that it's very easy to go to last time period, you just add an extra filter.