We all know and use expression fields in tables to display additional information from other tables (for example a customer name in an order containing only the customer number). This works fine for a limited number of rows but as the list grows expressions hurt performance. This idea is to solve this performance hit by making set-based expressions without having to resort to views.
Disclaimer: I haven't thought this through 100% but I think it could work. ;)
Suppose you have a table “order” and a table “customer”. The orders contain a customer number but not the customer name. Now to add the customer name in the order list I imagine I could define table “customer” as an addtional entity under table “order”. In this entity I choose customer number as the reference column. In the next step I then add colums from table “customer” to table “order” where these columns don't physically exist in table “order”. Just like existing expression fields they could be marked in blue or some other color. In this particular example I’d add column “customer.customer_name” to table “order” as an expression.
Under water the GUI adds table “customer” to the query of “order” using a left join. It might use alias t2 for “customer”. Then t3, t4 etc for additional defined entities. So in this case this would be select t1.*, t2.customer_name from order t1 left join customer t2 on t2.customer_id = t1.customer_id for the GUI.
There probably should be a check on these defined entities to prevent duplication of rows from the main table within the grid.
The reason for this idea is that we often use views for longer lists of data instead of tables with expression fields, for performance reasons. But this has obvious downsides too such as additional required functionality to make the data editable.
Additional:
After writing this I realized the idea could basically be seen as an expansion of lookup tables. Currently a lookup has a 1 on 1 column relationship: a column in a table is displayed with the value of a column in another table. This could be expanded to displaying additional columns of the lookup table. So in this example the order-customer reference would allow to add additional columns from lookup table “customer” to the data model of “order” in the form of expressions.
